How to Remove Blanks in Pivot Table in Excel

In this tutorial, you will learn how to remove blanks in pivot table in Excel.

In order to create the desired report, a pivot table is a statistics tool that condenses and reorganises specific columns and rows of data in a spreadsheet or database table. The utility simply “pivots” or rotates the data to examine it from various angles rather than altering the spreadsheet or database itself.

Once you are ready, we can get started by using real-life scenarios to help you understand how to remove blanks in pivot table in Excel.

Remove Blanks in Pivot Table

Before we begin we will need a group of data to be used to remove blanks in pivot table in Excel.

Step 1

Make sure your group of data is displayed in a clean and tidy manner in the first sheet.

Step 2

To create a pivot table we will select the entire data group, select ‘Insert’, and select ‘Pivot Table’.

Step 3

Once the pivot table is created, you will see there are some blanked cells in the table.

Step 4

To remove the blank and replace with a 0, you can simple select ‘Pivot Table Analyse’, then select ‘Options’, then insert ‘0’ in the ‘Empty cells as:’.

Step 5

Once you are done your pivot table will look like this.