Formatting Issues

Fix Excel Conditional Formatting Highlighting Every Cell in Teams

Nimra Malik Oct 12, 2026 870 views

Question details

The user needs to resolve an issue where a conditional formatting rule, such as highlighting dates less than TODAY(), incorrectly highlights every cell when viewed in Excel for Teams or the Web.

How to Fix Excel Conditional Formatting Highlighting Every Cell in Teams

Product: Microsoft Excel (Teams/Web)
Device & OS: not provided
Scenario: Viewing or editing an Excel workbook with conditional formatting rules inside Microsoft Teams or Excel for the web.
Observed behavior: The conditional formatting rule behaves differently than in the desktop app, incorrectly applying the highlight format to every cell in the selected range.

Before you start

Ensure that the cells you are trying to format actually contain valid date values and not text strings, as incorrect data types are a common cause of formatting errors in the web version.

Solution 1Recommended

Modify the Conditional Formatting Formula to Exclude Blanks

Web versions of Excel often evaluate blank cells as zeros, which represent a date in the year 1900. Modifying your formula to ignore blanks prevents the entire column from being highlighted.

When using a formula like =A1<TODAY(), Excel for the web might treat empty cells in your range as '0'. Since 0 is less than today's numerical date value, the empty cells get highlighted. Wrapping the condition in an AND function fixes this.

Open Rules Manager

Select the affected data range, go to the Home tab, click on Conditional Formatting, and select Manage Rules.

Edit the Rule

Select your existing rule and click Edit Rule. If it uses =A1<TODAY(), change the formula to =AND(A1<>"", A1<TODAY()).

Check Applies To Range

Verify that the 'Applies to' field exactly matches your data range (e.g., =$A$2:$A$100) instead of an entire column (=$A:$A).

Apply and Save

Click OK to apply the new rule, then save your workbook and refresh your Microsoft Teams tab to see the changes.

Modify the Conditional Formatting Formula to Exclude Blanks

Formula Update Complete: By explicitly telling Excel to ignore blank cells, your formatting will now only apply to actual date values that are older than today.

Solution 2

Check Data Types and Web Compatibility

Sometimes data exported or entered in the web app is stored as text rather than actual dates, breaking conditional logic.

Seamless Conditional Formatting

Use WPS Spreadsheet for Reliable Conditional Formatting

Avoid web-compatibility glitches and syncing errors by using WPS Spreadsheet. It offers a robust, fast desktop experience with full Microsoft Excel format compatibility, ensuring your conditional formatting rules like =TODAY() work flawlessly every time without highlighting blank cells incorrectly.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook.
2. Select the Data Range: Highlight the cells or column you want to apply the date-based formatting to.
3. Create a New Rule: Go to the Home tab, click on Conditional Formatting, and select New Rule.
4. Input the Formula: Choose 'Use a formula to determine which cells to format' and enter =AND(A1<>"", A1<TODAY()).
5. Apply Formatting: Click Format to pick your desired highlight color, then click OK to apply the reliable formatting.

100% compatible with Microsoft Excel (.xlsx) formatsReliable conditional formatting without web-rendering glitchesFree, lightweight, and fast desktop applicationIntuitive user interface for managing complex data rules

microsoft office alternative - wps office

Frequently Asked Questions

Why does =TODAY() conditional formatting highlight blank cells?

Excel often evaluates blank cells as the number zero, which translates to the date January 0, 1900. Since this is less than today's date, the rule triggers and highlights it. You can fix this by using the AND function to exclude blanks: =AND(A1<>"", A1<TODAY()).

Does Excel for the web support all conditional formatting rules?

Most basic conditional formatting rules are supported in Excel for the web. However, complex formulas, cross-sheet references, or specific relative reference structures might render incorrectly or behave differently compared to the desktop app.

How do I stop conditional formatting from applying to the whole column?

Open the Conditional Formatting Rules Manager and check the 'Applies to' field. Ensure it references only your specific data range (e.g., =$A$2:$A$100) rather than the entire column (e.g., =$A:$A).

Can I use conditional formatting for dates older than 30 days?

Yes, you can set up a conditional formatting rule using a formula to check for older dates. Select 'Use a formula to determine which cells to format' and enter =A1<(TODAY()-30) to highlight dates older than 30 days.

Document & File Issues