How to hide zero values
Q. I use formulas to create the totals in my Adjusted Trial Balances and other spreadsheets. There are always so many zero values, and they are very distracting. I know that there are different formats that I can choose for the zeros, but how can I make Excel not show anything at all for a zero value?
A. In certain scenarios, showing zero values can be very distracting. As you specified, in an adjusted trial balance, the debit or credit with the zero balance generally is not shown. But if you are using a formula and a zero value is calculated, a zero will be shown. There are other scenarios as well where it would be best to hide zero values. Fortunately, this can easily be done in Excel. You can hide zero values in Excel with Excel 2007 and later versions on Windows and Excel 2011 and later versions on Mac using a simple Excel feature. However, there are also ways of hiding zero values by using formulas, but this is more complicated than using the feature.
In order to hide zero values in your Excel spreadsheet, click File on the Ribbon. Click Options, then Advanced. Scroll down about three-fourths of the way until you reach the section called Display options for this worksheet:. Underneath that, uncheck Show a zero in cells that have zero value. See the screenshot below. Only the current worksheet will stop showing zero values.
You can access a video demonstration below and accompanying workbook of hiding zero values in Excel.
— By Kelly L. Williams, CPA, Ph.D.
