Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Turning Off Display of Zeros for All Worksheets.
Written by Allen Wyatt (last updated May 6, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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 (12456) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Turning Off Display of Zeros for All Worksheets.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Gridlines are very helpful in seeing where cells are located on the screen. You are not limited to black gridlines; ...Discover More
Do you want page breaks displayed on the screen? Excel allows you to specify whether it should show those page breaks or not.Discover More
Some people prefer to have what is displayed on the screen in Excel be the precision at which the program works. If you ...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 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.