How to Make Excel Hide Zero Values by Default
Question details
The user wants to configure Excel so that zero values are hidden automatically across all new workbooks and worksheets, avoiding the need to manually change the display settings for every new file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating new Excel workbooks or inserting new worksheets where cells containing the value zero should automatically appear blank.
- Observed behavior
- By default, Excel displays zeros in cells, and the option to hide zero values is configured strictly on a per-worksheet basis.
Before modifying startup folders, ensure that hidden files and folders are visible in your file explorer, as the AppData directory required for this configuration is hidden by default.
Configure Default Templates in the XLSTART Folder
By creating customized Book.xltx and Sheet.xltx templates, you can force Excel to apply your preferred zero-value display settings to all newly created files.
Excel relies on the XLSTART folder to determine default behaviors and formatting for new documents. When you place properly configured template files in this directory, Excel will automatically use them whenever you open a new workbook or add a new worksheet.
Open Microsoft Excel and create a completely blank workbook with your preferred default number of sheets.
Navigate to File > Options > Advanced. Scroll down to the 'Display options for this worksheet' section, select a worksheet from the dropdown, and uncheck 'Show a zero in cells that have zero value'. Repeat this step for every worksheet currently in the workbook.
Go to File > Save As. In the file path bar, type %appdata%\Microsoft\Excel\XLSTART and press Enter. Choose 'Excel Template (*.xltx)' from the format dropdown, name the file exactly 'Book.xltx', and click Save.
In the same Book.xltx file, delete all worksheets except for one. Save this single-sheet document as 'Sheet.xltx' in the exact same XLSTART folder. Excel will now use these templates to hide zero values automatically.
Easily Manage Zero Values with WPS Office
If you want a straightforward way to manage data visibility without tampering with system startup folders, WPS Spreadsheet offers intuitive formatting settings to hide zero values instantly.
- 1. Open Your Spreadsheet: Launch WPS Office and open your current spreadsheet file.
- 2. Access Spreadsheet Options: Click the 'Menu' button in the top-left corner and select 'Options' from the drop-down list.
- 3. Navigate to the View Tab: In the Options dialog box that appears, click on the 'View' tab.
- 4. Disable Zero Values: Locate the 'Window options' section, uncheck the 'Zero values' box, and click 'OK' to hide all zeros in the current worksheet instantly.
Frequently Asked Questions
Will creating these templates affect my existing Excel files?
No, saving custom Book.xltx and Sheet.xltx templates in the XLSTART directory only affects newly created workbooks and newly inserted worksheets. Your existing files will retain their original formatting.
Can I hide zero values without changing default templates?
Yes. If you only want to hide zeros in specific cells rather than the whole worksheet, select the cells, press Ctrl+1 to open Format Cells, choose 'Custom', and enter the format code: 0;-0;;@ to hide zero values.
Why can't I find the XLSTART folder using %appdata%?
If the %appdata% shortcut does not work, you can navigate there manually by opening File Explorer and going to C:\Users\[YourUsername]\AppData\Roaming\Microsoft\Excel\XLSTART. Make sure you have enabled 'Show hidden files, folders, and drives' in your Windows Folder Options.
How do I revert to Excel's original default settings?
To restore Excel's original behavior where zero values are shown by default, simply navigate to the %appdata%\Microsoft\Excel\XLSTART folder and delete or rename the Book.xltx and Sheet.xltx files you created.
