• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Fix Empty Cells and Error Values in Pivot Table

How to Fix Empty Cells and Error Values in Pivot Table

By: Waseem Patwegar

When you make use of calculations in an Excel Pivot Table you may come across #DIV/0! error values. Let us go ahead and take a look at how to Fix Error Values in Pivot Table.

Fix Empty Cells and Error Values in Pivot Table

Fix Error Values in Pivot Table

When you get Error Value #DIV/0! in an Excel Pivot Table, it usually means that you are trying to divide a number by zero.

Such Error Vales do not look good in a Pivot Table and they can raise unnecessary questions whenever you are trying to present important information to others.

Luckily, it is possible to fix such error values in an Excel Pivot Table by making Excel replace such Error Values by a Custom defined value or Text.

1. Fix #DIV/0! Error in Pivot Table

From our experience, #DIV/0! errors in Pivot Table occur due to an incorrect formula or calculation errors in the Source Data File being used by the Pivot Table.

Hence, it is really important to make sure that there is no incorrect formula or calculation error in the Source Data File.

Pivot Table With #DIV/0! Errors

If you are completely satisfied with the formulas and calculations in Source Data File, you can follow the steps below to fix #DIV/0! error in Pivot Table (if it is still there).

Right-click on the Pivot Table and click on PivotTable Options in the drop-down menu.

Open Pivot Table Options

On PivotTable options screen, check the little box next to For error value show: and enter NA (Not Applicable) or any other text that you want to show up in the Pivot Table in place of the Error Value.

Use Custom Error Values in Pivot Table

Click on the OK button to save this setting in the workbook.

Now, whenever the Pivot Table comes across #DIV/0! error in the source data file, you will see NA (Not Applicable) in the Pivot Table, instead of seeing an Error Value.

Important: A drawback of this solution is that it can mask future errors in source data file. Hence, always make sure that formulas and calculations in source data file are correct and free from all errors.

2. Fix Empty Cells in Pivot Table

Whenever the source data file for a Pivot Table contains blanks (which usually happens), you may see empty or no values in certain cells of your Pivot Table.

Pivot Table With Empty Value

Just like other errors, empty values in a Pivot Table do not look good and they can also lead to waste of time due to questions about them during your presentation.

Right-click on your Pivot Table and click on PivotTable Options in the drop-down menu.

Open Pivot Table Options

On PivotTable options screen, check the little box next to For empty cells show: and enter “O” or “NA” in the box.

Custom Format Empty Cells in Pivot Table

Click on OK to save this setting.

Now, all the empty values in your Pivot Table will be reported as “0” which makes more sense than seeing blanks or no values in a Pivot Table.

3. Fix “Blank” Value in Pivot Table

Instead of seeing empty cells, you may see the words “blank” being reported in a Pivot Table. As mentioned above, this error is occurring due to presence of empty cells in your Source Data File.

You can either fix this error by using Custom Values (methods as discussed above) or hide “blank” in Pivot Table by following the steps below.

1. Identify the location of “blank” values in your Pivot Table. In our case, the word “blank” is appearing in Row 8 and also in Column C of the Pivot Table.

Blank in Pivot Table Row and Column

2. To hide “blank” values in Pivot Table, click on the Down-arrow located next to “Row Labels”. In the drop-down, uncheck the little box located next to blank and click on the OK button.

Hide Blank in Pivot Table Rows

This will hide all the “blank” values in your Pivot Table.

Related
  • How to Hide and Unhide Values in Pivot Table
  • How to Change Pivot Table Data Source and Range

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Recent Posts

  • Fix: iPhone Camera Black Screen Problem
  • How to Clear Your Activity History In Windows 11
  • How to Find WiFi Password in Windows 11
  • How to Enable Touch Keyboard in Windows 11/10
  • How to Send Group Email In Gmail App On iPhone




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved