Repair Empty Cells and Error Values in Pivot Desk

Once you make use of calculations in an Excel Pivot Desk chances are you’ll come throughout #DIV/0! error values. Allow us to go forward and try how one can Repair Error Values in Pivot Desk.

Repair Error Values in Pivot Desk

Once you get Error Worth #DIV/0! in an Excel Pivot Desk, it normally means that you’re attempting to divide a quantity by zero.

Such Error Vales don’t look good in a Pivot Desk they usually can increase pointless questions at any time when you are attempting to current vital info to others.

Fortunately, it’s attainable to repair such error values in an Excel Pivot Desk by making Excel change such Error Values by a Customized outlined worth or Textual content.

1. Repair #DIV/0! Error in Pivot Desk

From our expertise, #DIV/0! errors in Pivot Desk happen as a consequence of an incorrect system or calculation errors within the Supply Knowledge File being utilized by the Pivot Desk.

Therefore, it’s actually vital to ensure that there isn’t any incorrect system or calculation error within the Supply Knowledge File.

If you’re fully glad with the formulation and calculations in Supply Knowledge File, you’ll be able to observe the steps beneath to repair #DIV/0! error in Pivot Desk (whether it is nonetheless there).

Proper-click on the Pivot Desk and click on on PivotTable Choices within the drop-down menu.

On PivotTable choices display, test the little field subsequent to For error worth present: and enter NA (Not Relevant) or some other textual content that you simply wish to present up within the Pivot Desk rather than the Error Worth.

Click on on the OK button to save lots of this setting within the workbook.

Now, at any time when the Pivot Desk comes throughout #DIV/0! error within the supply knowledge file, you will notice NA (Not Relevant) within the Pivot Desk, as a substitute of seeing an Error Worth.

Essential: A disadvantage of this answer is that it will possibly masks future errors in supply knowledge file. Therefore, at all times ensure that formulation and calculations in supply knowledge file are right and free from all errors.

2. Repair Empty Cells in Pivot Desk

At any time when the supply knowledge file for a Pivot Desk comprises blanks (which normally occurs), you might even see empty or no values in sure cells of your Pivot Desk.

Similar to different errors, empty values in a Pivot Desk don’t look good they usually also can result in waste of time as a consequence of questions on them throughout your presentation.

Proper-click in your Pivot Desk and click on on PivotTable Choices within the drop-down menu.

On PivotTable choices display, test the little field subsequent to For empty cells present: and enter “O” or “NA” within the field.

Click on on OK to save lots of this setting.

Now, all of the empty values in your Pivot Desk might be reported as “0” which makes extra sense than seeing blanks or no values in a Pivot Desk.

3. Repair “Clean” Worth in Pivot Desk

As a substitute of seeing empty cells, you might even see the phrases “clean” being reported in a Pivot Desk. As talked about above, this error is going on as a consequence of presence of empty cells in your Supply Knowledge File.

You’ll be able to both repair this error through the use of Customized Values (strategies as mentioned above) or conceal “clean” in Pivot Desk by following the steps beneath.

1. Determine the placement of “clean” values in your Pivot Desk. In our case, the phrase “clean” is showing in Row 8 and likewise in Column C of the Pivot Desk.

2. To cover “clean” values in Pivot Desk, click on on the Down-arrow situated subsequent to “Row Labels”. Within the drop-down, uncheck the little field situated subsequent to clean and click on on the OK button.

This may conceal all of the “clean” values in your Pivot Desk.

• Cover and Unhide Values in Pivot Desk
• Change Pivot Desk Knowledge Supply and Vary