Written by Allen Wyatt (last updated November 18, 2017)
This tip applies to Excel 2007, 2010, 2013, and 2016
King knows how to turn off the display of zeros in Excel for individual worksheets. He wants to turn it off by default so that every worksheet he opens, zeros are not displayed. If he wants zeros, he knows he can turn on the display of zeros.
One way to get this to happen is to set up your own default workbook. Follow these steps:
If you are unsure of where the XLStart folder is located (step 6), use the Find feature of Windows to locate the folder. With the template in that folder, any time you create a new workbook, the settings within the workbook (including whether zero values are displayed or not) should be set according to however they were in the template.
Of course, this approach doesn't help with existing workbooks or with workbooks that you may receive from others. In that case, you may want to adopt the use of a couple of small macros that control the display of zero values.
Sub Display0() ActiveWindow.DisplayZeros = True End Sub
Sub Hide0() ActiveWindow.DisplayZeros = False End Sub
The first macro (Display0) turns on the display of zero values, while the second (Hide0) turns off the display. These could easily be assigned to toolbar buttons or shortcut keys so you don't have to wade through the Options dialog box to turn the display on and off.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12456) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Turning Off Display of Zeros for All Worksheets.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it ...
Discover MoreThe Formula Bar is a regularly used feature in the Excel interface. You can, however, modify whether Excel displays the ...
Discover MoreExcel allows you to configure your system, even to the point of adding macros to your ribbon area. What are you to do, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-11-21 09:55:07
Nick from London
When I had the old tool bars not only did I have a macro toggling the display of Zero but the Tool Bar button swapped.
Most of my old custom buttons can be found on the ribbon but not this one.
It would be nice to replicate the old toolbar button but my Ribbon knowledge is not up to it anyone got a link, so an old dog can learn new tricks.
Nick
2017-11-18 05:43:26
Michael (Micky) Avidan
@To whom it may concern
I prefer (and did so) to use a SINGLE button placed on the QAT which Toggles the state of the Zero Display.
---------------------------
Sub Toggle_Zeros_Display()
ActiveWindow.DisplayZeros = Not (ActiveWindow.DisplayZeros)
End Sub
--------------------------
Michael (Micky) Avidan
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments