Farihat
To solve multiple types of problems, there are numerous techniques you can use via Excel tools. One of these magical pieces of equipment is the Pivot Table. In many tutorials and manuals, you might find that these advise you to avoid keeping blanks while generating a Pivot Table from a dataset. It is due to inconsistent output from the blank cells or rows. Now, you may have a dataset with blank rows. After you have created a Pivot Table based on this dataset, you will see that will impact your newly created pivot table. You can try three approaches to update your Pivot Table in order to remove blank rows in Excel. You will find how can you directly delete any row, or remove multiple rows and refresh afterward, also to filter your Pivot Table.
Following is an overview of a superstore record of purchases where we removed blank rows from Excel Pivot Table.
📁 Download Excel File
Download the practice workbook below.
How to Create a Pivot Table in Excel
The Pivot Table is an important tool of Microsoft Excel. To generate a new form of the table using different column numbers, or to operate any mathematical condition, such as summation, average, etc., we can use the Pivot Table efficiently. First, we need to create a Pivot Table from a specific dataset. Unlike other tools, this process needs detailed instructions.
Now, this paragraph will help you to create a pivot table in Excel step by step. From the following data set, we will consider creating Pivot Table using the “Customer Name” and their respective “Billing Amount” columns.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the dataset of range B5:E14.
- Now, select the Pivot Table option from the Tables group, under the Insert tab as shown in the figure.
- Here, you will get the Pivot Table window.
- Next, check the box for the Existing Worksheet option.
- Then, select any cell (say B16) of the same sheet (Pivot Table Create) to declare the location.
- After that, hit the OK button.
- Now, on cell B16, you will get an extension window for Pivot Table Fields.
- Here, drag the “Customer Name” and the “Billing Amount” fields to the Rows and the Values areas respectively.
- Next, the Pivot Table is available as per your requirement.
Finally, edit the Column Header from “Row Labels” to “Customer Name” as the dataset.
Learn to Remove Blank Rows in Excel Pivot Table with These 3 Approaches
This article is going to introduce three approaches in Excel to remove blank columns from Pivot Table. Those methods will help you to understand the uses elaborately. The following discussion will cover the uses of the Refresh, Go To Special, Filter options. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
Approach 1: Delete Blank Rows from the Source to Remove them from Pivot Table
From the discussion of the previous section, we know that the Pivot Table is connected with its source dataset. There are various cases when you need to modify your dataset by removing a row or a record. These records usually are no longer required in your data set. Now, this method will discuss how can you delete such a row with ease. Also, you can update your Pivot Table along with the change in the dataset.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select a row that has unrequired data. Let us assume the row number is 10.
- Now, click on the Delete icon under the Cells group, from the Home tab.
- Here, notice the Sum of Billing Amount of customer “Matthew Hobbert” as 116. It adds up the total billing amount to 315.
- Next, Right-Click on the “Customer Name” column header.
- Then, choose the Refresh option from the Context Menu.
Therefore, you can notice the Sum of Billing Amount, has changed for “Matthew Hobbert” from 116 to 76, and so the Grand Total changes from 315 to 275.
Approach 2: Use Go To Special to Remove Blank Rows of Pivot Table
For individual record removal, it is a strenuous process to refresh the Pivot Table all the time. For those cases, we can try the Go To Special option. This option will enable you to select all the blank rows within a click from your dataset. If the selection gets easy in this method, which helps the next refresh the Pivot Table easier and more efficiently.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell range B6:E14.
- Then, click on the Go To Special option from the Find & Select drop-down.
- Now, from the Go To Special window, tick the option for Blanks.
- Next, hit the OK button.
- After that, you will find the blank rows selected.
- Now, click on the Delete icon, from the Cells group, under the Home tab.
- Next, Right-Click on the “Customer Name” column header.
- Then, choose the Refresh option from the Context Menu.
- Moreover, you will get your Pivot Table clear of blank records or rows.
Approach 3: Use Filter Option to Discard Blank Rows of Pivot Table
Using the Filter option is the easiest way to remove blank rows for the Pivot Table in Excel. In this method, you don’t need to change or delete or modify your dataset. As a result, this method becomes easier than the previous two methods. From the pivot table itself, you can just Filter the column and cross out the blank rows. Unlike other methods, you can access your blank rows anytime you want in this method only.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, click on the drop-down icon beside the “Customer Name” column header.
- Now, as the picture shows the elaborated view of the drop-down, untick the “(blank)” option at the box.
- Next, hit the OK button.
- Finally, you will see Filter has successfully removed the blank rows in Excel Pivot Table.
How to Replace Blank Cells or Rows in Pivot Table
When we prepare the Pivot Table from a dataset, we noticed that it keeps blank cells just as they were. It gets difficult for us to analyze in that case. Now, we can replace the blanks with any of the character(s). This method will involve the use of the Pivot Table Options menu. We can just input the character (for example N/A) to replace where the data isn’t available or blank.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, we can see row 7 with a blank cell from the dataset and keep a blank cell for the Pivot Table as well.
- Now, click on the column header “Customer Name” and Right-Click on it.
- Here, select the Pivot Table Options from the Context Menu.
- After that, the PivotTable Options window pops up.
- Then, under the Layout & Format tab, write “N/A” beside the For empty cells show option from the Format tab.
- Now, hit the OK button.
Therefore, we can see that the blank cell of “Robin Clarris” is no longer blank, but “N/A”.
How to Highlight Blank Cells or Rows in Pivot Table
Amidst a dataset, we may get cells or rows with no data, i.e. blank cells or rows. The article has discussed creating a Pivot Table, removing blank rows, and replacing blank cells or rows. In this section, we will share how to highlight blank cells or rows of Pivot Table. We will use the Conditional Formatting option to highlight all the blank cells within the selected range.
⬇️⬇️ STEPS ⬇️⬇️
- First, we can see the following dataset, where the record of “Robin Clarris” has a blank cell. As a result, in the Pivot Table, we can see the “Sum of Billing Amount” blank for “Robbin Clarris”.
- Now, select the cell range B14:C17 from the Pivot Table.
- Next, click on the Conditional Formatting drop-down from the Home tab.
- Then, choose the New Rule option.
- Here, you can see the New Formatting Rule window.
- After that, choose the Format only cells that contain option from the Rule type menu.
- Then, select the Blanks option from the Format only cells with drop-down.
- Now, click on the Format button.
- Next, choose a color to fill from the Fill tab.
- Then, hit the OK button.
- Now, from the New Formatting Rule window, hit the OK button to proceed.
Therefore, you can see the blank cell previously available on the Pivot Table is formatted with your desired color.
📄 Important Notes
🖊️ You need to make sure that, you refresh the Pivot Table for any change in the dataset.
🖊️ The first method is specifically for deleting an entire record or row. You can use manual row deletion for multiple cases, but it can be difficult for a huge dataset.
📝 Takeaways from This Article
📌 Primarily, you can delete an unnecessary record by deleting the entire row. Refreshing the Pivot Table can remove unnecessary data.
📌 Also, we can use the Go To Special option to select blank rows. After deleting them, you can try the Refresh option for the Pivot Table to successfully remove blank rows.
📌Finally, you can apply the Filter tool for the Pivot Table to remove the blank rows in the most simple way.
Conclusion
To remove blank columns for Pivot Table, three methods are available in Excel. You can use the Refresh, Go To Special, and Filter options to directly remove the blank rows. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
