Pivot tables are used in Microsoft Excel as tools for data analysis, summation and exploration. Using a pivot table allows you to sort though a large amount of data so that you can obtain summations and averages of the data that are analyzed, for example. In order to not leave blanks in the pivot table, you can remove and replace the blank entries with other data by setting up the values of empty cells in the "PivotTable Options...."
Step 1
Open your XLS file and locate the pivot table report worksheet which you have derived from your source data.
Step 2
Right-click one of the entries in the pivot table and choose "PivotTable Options..." from among the drop-down list. (see reference 2)
Step 3
Check the "For empty cells show:" check-box in the Format section of the Layout & Format tab.
Step 4
Type the content that you want to to appear in blank cells in the "For empty cells show:" text field and click "OK" to apply to the changes. For example, type "N/A" without the quotation marks to replace all the blank cells in the table with "N/A" without the quotation marks.
Step 5
Check the entries in the pivot table. All the blanks have been removed and replaced with the new content.
Tip
If you want to remove only some of the empty entries in the pivot table you can create a pivot table based on a selected range of the source data table so that you can manipulate the data in the specified pivot table.