How to hide zero values

By Kelly L. Williams, CPA, Ph.D.

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.


Where to find March’s flipbook issue

The Journal of Accountancy is now completely digital. 





Get Clients Ready for Tax Season

This comprehensive report looks at the changes to the child tax credit, earned income tax credit, and child and dependent care credit caused by the expiration of provisions in the American Rescue Plan Act; the ability e-file more returns in the Form 1040 series; automobile mileage deductions; the alternative minimum tax; gift tax exemptions; strategies for accelerating or postponing income and deductions; and retirement and estate planning.