Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Turning Off Display of Zeros for All Worksheets.
by Allen Wyatt
(last updated February 2, 2013)
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12455) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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!
A large number of graphics in a worksheet can slow down Excel. One way to compensate is to turn on picture placeholders, an ...Discover More
The Formula bar is used to display the formula that appears in a cell. You may want to modify how the Formula bar is ...Discover More
Want to spice up your worksheets with your own custom colors? Here's how to define them easily.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.