3 Approaches to Remove Blank Rows in Excel Pivot Table

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.

Overview of the process to remove blank rows in 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.

Choose a cell range for creating Pivot Table

  • 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.

Customize the Pivot Table Window

  • 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.

Customize Pivot Table by selecting fields

Finally, edit the Column Header from β€œRow Labels” to β€œCustomer Name” as the dataset.

Edit column header


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.

Dataset to remove blank rows in Excel Pivot Table

approach

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.

Delete unrequired record

  • Next, Right-Click on the β€œCustomer Name” column header.
  • Then, choose the Refresh option from the Context Menu.

Refresh your Pivot Table to remove blank rows in Excel

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.

Final output after removing blank rows

approach

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.

Find Go To Special option in Excel

  • Now, from the Go To Special window, tick the option for Blanks.
  • Next, hit the OK button.

Edit Go To Special dialogue box

  • After that, you will find the blank rows selected.
  • Now, click on the Delete icon, from the Cells group, under the Home tab.

Delete the blank rows under the selection

  • Next, Right-Click on the β€œCustomer Name” column header.
  • Then, choose the Refresh option from the Context Menu.

Refresh your Pivot Table

  • Moreover, you will get your Pivot Table clear of blank records or rows.

Pivot Table after removing blank rows

approach

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.

Access filter drop-down for the row values

  • Now, as the picture shows the elaborated view of the drop-down, untick the β€œ(blank)” option at the box.
  • Next, hit the OK button.

Filter out the blank rows to remove blank cells

  • Finally, you will see FilterΒ has successfully removed the blank rows in Excel Pivot Table.

Manually filter to remove 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.

Replace Blank cell from Pivot Table

  • Now, click on the column header β€œCustomer Name” and Right-Click on it.
  • Here, select the Pivot Table Options from the Context Menu.

Choose the Pivot Table Option 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.

Pivot Table Options modify for blank cells

Therefore, we can see that the blank cell of β€œRobin Clarris” is no longer blank, but β€œN/A”.

Final output of successful replacement of blank cells


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”.

Dataset for highlighting blank cells or rows

  • 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.

Select 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.

Edit New Formatting Rule window

  • Next, choose a color to fill from the Fill tab.
  • Then, hit the OK button.

Choose a fill color to show for highlighting blank cells

  • Now, from the New Formatting Rule window, hit the OK button to proceed.

Confirm the format

Therefore, you can see the blank cell previously available on the Pivot Table is formatted with your desired color.

Blank cells or rows has been highlighted in pivot table


πŸ“„Β  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.

(Visited 17 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo