In Microsoft Excel, a pivot table helps to calculate and summarize data with precision. But sometimes you may face problems with blank rows in the pivot table. This occurs when you have blanks in source data. In this article, we are going to show how to remove blank rows from a pivot table in Excel.
Remove Blank Rows in Excel Pivot Table: 4 Methods
At the time of calculating or summarizing data, you may face problems with blank rows. To solve these problems, we have to remove those blank rows first. Today, I am going to explain 4 methods to remove blank rows in the Excel pivot table. Consider a dataset of a pivot table with blank cells.
1. Use Pivot Table Option to Remove Blank Rows
With the help of the pivot table option, you can remove blank rows very easily. In the following method, I am going to explain the process of removing blank rows using a pivot table.
Step 1:
- In the pivot table chart, place your cursor and right-click on the mouse to show pivot table options.
- Select the “PivotTable Options”.
Step 2:
- A new window will appear. Choose “Layout & Format”.
- Fill up with “0” in the “For empty cells show” option. This will input 0 for every blank cell in the pivot table.
- Press OK.
- Thus, you will see all the blank cells will be filled up with “0”.
- Now, all the blanks are filled with data. Thus, we can remove blanks in the pivot table.
2. Apply Conditional Formatting to Remove Blank Rows in Excel Pivot Table
In the following dataset, you can see a row containing a blank. In this method, I am going to show you how to remove these blank rows by applying conditional formatting.
Step 1:
- Select any row from where you want to remove blank cells. You can also select a range or group of cells.
- From the Home ribbon, select “Conditional Formatting” and go to “New Rule”.
Step 2:
- A new window appears named New Formatting Rule.
- Click on “Format only cells that contain”.
- Change the option to “equal to” and write “(blank)” in the next cell.
- Then press “Format”.
Step 3:
- “Format Cells” dialogue box will appear.
- Choose “Number” and then change the category to “Custom”.
- Type “;;;” in the type box. This will format all the zero or blank cells as blank.
- Press OK to continue.
Step 4:
- Let’s fill the blanks with colors. Go to “Fill” and choose a color to fill.
- Click OK.
- As you can see the blank is filled with color.
- This is the way to remove blank rows by applying conditional formatting. Using the similar way, you can remove the rest of the blank cells too.
3. Utilize Filter Feature to Remove Blank Rows in Excel Pivot Table
Filtering is one of the best options in Excel to filter data. With the filer feature, you can remove blank rows in the pivot table.
Steps :
- In the pivot table row click on the arrow.
- Remove the tick mark (✓) sign from the blank row option.
- Press OK.
- The blank row will be removed from the corresponding column
- Thus, by filtering, we can remove blank rows in the pivot table.
4. Apply Find & Replace Option to Remove Blank Rows in Excel Pivot Table
In the following method, we are explaining the removal of blank rows with the help of the “Find and Replace” option.
Steps :
- Select the worksheet.
- Press Ctrl + H to display the “Find and Replace” dialogue box.
- In the Find and Replace window, fill the “Replace with” option with “Other”.
- Click “Replace All”.
- A new window will appear confirming the replacement of the blank with ‘Other”.
- Press OK.
- Click “Close” to see the results.
- As you can see. The blank cell is removed with the word “Other”.
Thus you can remove blank rows with the “Find and Replace” option.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Things to Remember
- In the pivot table, you cannot edit data manually or fill in blanks manually. So, to fill blanks you can apply methods to fill the blank rows or cells.
- Conditional formatting only formats the cells that have the text value (blank) in them. So, to remove all the blank cells using conditional formatting you need to put (blank) word in every blank cell.
Conclusion
In this article, I have tried my best to cover all the methods to remove blank rows in the Excel pivot table. Hope this article will help you with your problems. If you find any problem solving your issues regarding this, feel free to comment us in the comment section. Thanks!
