Wasim Akram
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. Learn more from this link.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Remove Blank Rows in Excel Pivot Table
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.
Read More: How to Remove Blank Rows in Excel (7 Methods)
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 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 too.
- 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 chose 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.
Read More: How to Remove Conditional Formatting in Excel (3 Examples)
Similar Readings
- How to Remove #DIV/0! Error in Excel (5 Methods)
- How to Remove Panes in Excel (4 Methods)
- Data clean-up techniques in Excel: Fixing trailing minus signs
- How to Remove Comments in Excel (7 Quick Methods)
- Remove Strikethrough in Excel (3 Ways)
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.
Read More: How to Delete Empty Rows and Columns in Excel VBA (4 Methods)
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 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.
Read More: How to Find and Delete Rows in Excel (5 Ways)
Things to Remember
- In the pivot table, you can not edit data manually or fill 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 knock us in the comment section. Thanks!
