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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Paste some information into a worksheet and Excel helpfully displays some options related to the paste operation. If you ...
Discover MoreExcel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...
Discover MoreSome formulas require the use of circular references in order to determine a result. If you want to make sure that ...
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 © 2021 Sharon Parq Associates, Inc.
Comments