How to Hide Zero Values and Error Values in Excel Spreadsheets

In spreadsheets where we used formulas extensively some of the cells may contain zero values as well as error values. Zero values may appear as a formula result at the same time error value may be caused by errors in formulas. Error value like #DIV/0!, #NAME? and #VALUE are common. By hiding these values you can improve the readability of the sheet. So let us see how to hide Zero Values and Error Values in Excel easily. I am going to the tips straightaway. If you face any problem please feel free to let me know.

Display or hide zero values in Excel Spreadsheets

You can hide zero values in selected cells or entire worksheet. To hide zero values in worksheet do follow the below tips.

Go to Excel Options from Microsoft Office Button and choose the category “Advanced”

Scroll to the bottom and under display options for this worksheet remove tick mark against Show a zero in cells that have zero value

Hide Zero Values and Error Values in Excel

You can hide zero values in any of the sheets of your choice from there. But if you want to hide zero values in only selected cells in Excel you need to apply custom number formats as follows.

Select the cells that contain the zero values by first clicking on any cells with zero values and then followed by ctrl+click on other cells with value zero.

Press Ctrl+1 to go to Format Cells. There in the Category list, click Custom. In the Type box, type 0;-0;;@

Hide Error Values in Excel Spread Sheet

You can easily hide error values like #DIV/0!, #NAME? and #VALUE! in cells by applying conditional formatting. To do this:

First select the cells that contain the error value that you wish to hide

Click Conditional Formatting from the Home tab, Styles group. Click New Rules. There under Select a Rule Type choose Format only cells that contain.

Hide Zero Values and Error Values in Excel

Now under Edit the Rule Description set the Format only cells with Error. Now click Format and set the font color to white. You have done!

Speak Your Mind