How to Automatically Exclude Blanks in a Pivot Table in Excel

Blanks are inevitable in an Excel table, particularly if you're working with a large database of information. It could be that you allowed an extra box for a second line of an address, and not everyone needs two lines for an address. If you're using a pivot table to work with complex data, you can stop the pivot table counting blank cells to keep your data as clean as possible.

When you're working with data in Excel, a pivot table gives you the ease of management you need. If your pivot table has blank fields, you can remove a blank from a pivot table or change content using the pivot table tools built into Excel. Click on PivotTable, then choose PivotTable Tools on the ribbon.

Whether you want to use the tools to have the pivot table hide blank rows or remove the word "blank" from them, you click on either Analyze or Options and then select Options in the PivotTable group. Then select the Layout & Format tab and Format to get to the choices you need to make.

In some cases, the pivot table feature inserts the word "blank" in every empty cell. This can understandably be problematic when you're trying to clean up your data. This process is different from the process you use to have the pivot table hide blank rows.

To stop a pivot table counting blank cells and filling them with the word "blank," follow these steps:

1. Click inside the pivot table and choose Control + A to select all the data on the page.
2. Select Home > Styles > Conditional Formatting and New Rule.
3. In the box that opens, select Format only cells that contain.
4. In the drop-down boxes under Format only cells with, select Cell value, Equal to and type (blank) in the third box.
5. Select the Number tab. Choose Custom and insert three semicolons under Type.
6. Click OK.

This removes the word "blank" from the pivot table, replacing it with a truly blank field.

In some cases, you may want to stop your pivot table counting blank cells altogether to clean up your data. To keep blanks from displaying in a row:

1. Select a row field.
2. On the Analyze tab, click Field Settings.
3. Choose Layout under Layout & Print and clear the check box reading Insert a blank line after each item label.

If you want to eliminate blank cells in Items, do the following:

1. Select the item you want.
2. Under the Design tab, look for Layout; click Blank Rows and select the check box on either Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label.

Cleaning up blanks in a pivot table can become annoying over time. Setting up your pivot table to hide blank rows automatically saves time. To do this, you set up a template. Templates keep you from having to reformat your pivot tables every time you work with new data.

To create a template:

1. In the file you want to save as a template, choose File and Save as.
2. In the box that pops up, look for File Format and choose Excel Template (.xltx).
3. Give the template a name.

To refresh the data you've input into a template:

1. Open the workbook with new data.
2. Copy all the new data.
3. Open the template file.
4. Find the range location that matches the new data you're pasting.
5. Paste in the new data you've copied from the other worksheet.
6. Click Refresh data.

All data is refreshed to incorporate the new information you input.