Formatting Issues

How to Fix Today's Date Conditional Formatting When Copying Excel Sheets

Khadija Khan Oct 10, 2026 870 views

Question details

The user needs to fix a conditional formatting rule that highlights the current day, as it stops working correctly after duplicating a planner template sheet.

How to Fix Today's Date Conditional Formatting When Copying Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Duplicating a planner template sheet that relies on dynamic date highlights.
Observed behavior: The conditional formatting rule for today's date fails to apply properly to the newly copied sheet.

Before you start

Before editing the formatting rules, ensure both your original sheet and copied sheet share the exact same row and column layout, as mismatched cell ranges often cause reference errors during duplication.

Solution 1Recommended

Edit and Update the Conditional Formatting Rule via Manage Rules

The most reliable method is to review the original rule and adjust the 'Applies to' range or recreate it on the copied sheet to ensure cell references are updated.

When copying sheets in Excel, conditional formatting ranges can sometimes remain linked to the original sheet instead of dynamically updating to the new one.

Open the Manage Rules manager

Navigate to your original sheet, select the cell with the working highlight, and go to Home > Conditional Formatting > Manage Rules.

Review the original formula

Select the current-day rule from the list and click Edit Rule. Note the formula used (such as =TODAY()) and the exact range in the 'Applies to' field.

Update the new sheet

Switch to your newly copied sheet, open the Manage Rules dialog again, and either recreate the rule or edit the existing rule's 'Applies to' references so they correctly point to the cells in the current sheet.

Edit and Update the Conditional Formatting Rule via Manage Rules

Check Absolute References: If the original formula contains absolute references that include the original sheet name (e.g., 'Sheet1'!$A$1), be sure to remove the sheet name or change it to the new sheet's name.

Solution 2

Use the Format Painter to Transfer the Rule

Quickly copy working formatting rules from the original sheet to the new one using the Format Painter tool.

Efficient Spreadsheet Management

Easily Manage Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet provides an intuitive interface for managing conditional formatting rules. You can easily duplicate sheets while retaining all complex cell formats and dynamic date highlights without requiring manual corrections.

1. Open your template: Launch WPS Spreadsheet and open your existing planner or calendar file.
2. Duplicate the sheet safely: Right-click the sheet tab at the bottom, select 'Move or Copy', and check 'Create a copy' to safely duplicate your planner layout.
3. Manage formatting rules: Navigate to Home > Conditional Formatting > Manage Rules to verify or adjust any date-based highlights instantly.

100% compatible with Microsoft Excel (.xlsx) formats and conditional formatting rulesIntuitive rule manager to easily edit and deploy date-based cell highlightsSeamlessly duplicate sheets while automatically preserving formulas and formatsLightweight application that runs smoothly on Windows, Mac, Linux, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

Why does the conditional formatting disappear when I copy an Excel sheet?

When copying sheets, conditional formatting ranges can sometimes become broken or remain rigidly linked to the original sheet's data instead of dynamically updating to reflect the new sheet. This breaks the rule and stops the highlighting from working.

How do I easily conditionally format a cell based on today's date?

Select the cells you want to format, go to the Home tab, click Conditional Formatting, choose Highlight Cells Rules, select 'A Date Occurring', and pick 'Today' from the dropdown menu.

Can I apply conditional formatting to an entire workbook at once?

Conditional formatting rules are generally sheet-specific in spreadsheet applications. While you can apply the same rule across multiple sheets by grouping them or using the Format Painter, it is highly recommended to manage complex rules on a per-sheet basis to avoid cell reference errors.

Document & File Issues