Written by Allen Wyatt (last updated May 6, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Turning Off Display of Zeros for All Worksheets.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can ...
Discover MoreThe Formula Bar is a regularly used feature in the Excel interface. You can, however, modify whether Excel displays the ...
Discover MoreThe mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-05-06 10:07:27
J. Woolley
For more on this subject, see my April 2022 comment here: https://excelribbon.tips.net/T013437
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 © 2025 Sharon Parq Associates, Inc.
Comments