Let’s take a look at the following table. There are four columns, first and last name, address and zip code. In the two last columns, you can notice that some of the cells are empty.
When you create a PivotTable from this data, Excel will automatically fill the empty cells with the word (blank).
We are going to use the classic pivot table layout for this example.
Remove (blank) cells in PivotTable
In order to remove (blank) cells and change them to empty cells, follow these steps.
- Insert PivotTable.
- Check all the PivotTable Fields.
- Change the PivotTable to the classic layout (or keep the standard one if you prefer).
- Click inside the PivotTable and Press Alt + A to select all PivotTable data.
- Choose Home >> Styles >> Conditional Formatting >> New Rule.
- Choose Format only cells that contain.
- In Format only cells with, choose Cell Value, and equal to, then type (blank).
- Click the Format…
- Choose the Number tab and Custom
- Inside the Type field, insert three semicolons.
- Click OK.
This rule replaced all strings consisting (blanks) to empty spaces.
If you add missing values to the table, nothing will change inside the PivotTable. If you want to apply changes, you have to click it with the right mouse button and choose Refresh.
Now, as you can see, Excel updated the PivotTable values.