Formatting Issues

How to Hide Zero Values While Highlighting Weekend Columns in Excel

Amos Gikunda Oct 9, 2026 871 views

Question details

The user needs to hide zero values generated by formulas in Excel cells when a gray background is applied to weekend columns via conditional formatting.

How to Hide Zero Values While Highlighting Weekend Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Applying a conditional formatting rule to highlight weekend columns with a background color while preventing the formula results of zero from being visible.
Observed behavior: When applying conditional formatting to shade weekend columns gray, zero values become visible because changing the font color to white contrasts against the gray background.

Before you start

Ensure that your conditional formatting rule for highlighting the weekend columns is already created and applied to the correct data range.

Solution 1Recommended

Apply a Custom Number Format to Hide Zeros

The most reliable way to hide zero values against any background color is by embedding a specific custom number format within your conditional formatting rule.

Changing the font color to white does not work well if the cell background changes dynamically. Instead, using the custom format `;;;` instructs Excel to display a blank cell for any positive number, negative number, zero, or text, while preserving the actual background color.

Open Conditional Formatting Manager

Select the range of cells where the weekend columns are located. Go to the Home tab, click on 'Conditional Formatting', and select 'Manage Rules'.

Edit the Highlighting Rule

Select the rule that applies the gray background to your weekend columns and click 'Edit Rule'.

Format the Number Settings

Click the 'Format' button in the rule dialog, then navigate to the 'Number' tab.

Apply the Custom Format

Choose 'Custom' from the category list. In the 'Type' input box, enter `;;;` (three consecutive semicolons) and click OK to apply the changes.

Apply a Custom Number Format to Hide Zeros

Format Preserved: Your weekend shading will remain visible, but the zero values will now be completely hidden without affecting the underlying data or formulas.

Solution 2

Hide All Zero Values in the Worksheet

If you prefer to hide zero values across the entire worksheet regardless of conditional formatting, you can disable the global zero-value display setting.

Seamless Spreadsheet Formatting

Hide Zero Values and Format Cells Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting and custom number formats, allowing you to highlight weekend columns and effortlessly hide zero values just like Excel.

1. Select Data: Open your workbook in WPS Spreadsheet and highlight the target range of cells.
2. Manage Formatting Rules: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'Manage Rules'.
3. Edit Rule Options: Select your weekend highlighting rule, click 'Edit', and open the 'Format' dialog.
4. Apply Custom Format: Under the 'Number' tab, select 'Custom', enter `;;;` in the Type box, and click OK to hide the zeros.

Fully compatible with Microsoft Excel (.xlsx) formulas and conditional formatting rules.Advanced custom number formatting to easily manage complex data visualizations.Free, lightweight, and user-friendly interface for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does changing the font color to white not work for hiding zeros?

If your conditional formatting applies a gray background to weekend columns, a white font color will still contrast against the gray, making the zero values highly visible. Custom number formatting avoids this by rendering the text completely transparent.

Will the `;;;` format delete my underlying formulas?

No, the `;;;` custom format only changes how the data is visually displayed on the screen. The underlying zero values and formulas remain completely intact and will calculate normally.

Can I use an IF formula to hide the zeros instead?

Yes, you can modify your formula to output a blank string instead of a zero (e.g., `=IF(A1+B1=0, "", A1+B1)`). However, using the custom number format method is often preferred because it leaves the original formula simpler and preserves numerical values for downstream calculations.

Document & File Issues