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 January’s flipbook issue

Starting this month, all Association magazines — the Journal of Accountancy, The Tax Adviser, and FM magazine (coming in February) — are completely digital. Read more about the change and get tips on how to access the new flipbook digital issues.


Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.