Formatting Issues

Fix Conditional Formatting Disappearing When Copying in Excel

Huda Qurayshi Oct 9, 2026 872 views

Question details

The user needs to stop conditional formatting from disappearing from the original cell when copying it to other areas of the workbook.

How to Fix Conditional Formatting Disappearing When Copying in Excel

Product: Microsoft Excel 2021
Device & OS: not provided
Scenario: Attempting to copy conditional formatting across a billing workbook using Format Painter, Paste Special, or standard copy and paste functions.
Observed behavior: The conditional formatting rules are unexpectedly deleted or removed from the source cell immediately after completing the copy or Format Painter operation.

Before you start

Create a backup copy of your billing workbook before testing rule changes to ensure you do not permanently lose any existing formats or critical data.

Solution 1Recommended

Review and Fix Conditional Formatting Rules and Ranges

Conflicting rules, fragmented ranges, or relative references often cause formatting to drop when copied.

When you copy and paste conditional formatting, Excel automatically updates the 'Applies to' range. If the formula inside the rule contains relative references instead of absolute references, or if multiple rules conflict with each other, it can cause the formatting to fail or disappear on the original cell.

Open the Rules Manager

Select the original cell that contains the conditional formatting. Go to the Home tab, click on Conditional Formatting, and select Manage Rules.

View All Worksheet Rules

In the Conditional Formatting Rules Manager dialog box, change the 'Show formatting rules for' dropdown menu to 'This Worksheet' to see all active rules.

Check the Applies To Range

Review the 'Applies to' column for any fragmented ranges (e.g., =Sheet1!$A$1,Sheet1!$C$4 instead of =Sheet1!$A$1:$F$10). Consolidate the ranges if necessary.

Verify Formula References

If you are using a formula to determine which cells to format, edit the rule and ensure you are using absolute references (using the $ sign, like $A$1) if the formatting should remain locked to specific criteria.

Review and Fix Conditional Formatting Rules and Ranges

Stop If True: If multiple rules apply to the same cell, check the 'Stop If True' box on higher-priority rules to prevent lower-priority rules from interfering.

Solution 2

Test for Workbook Corruption in a New File

Determine if the issue is a bug within the specific Excel file or an application-wide error.

Solution 3

Update Microsoft Office 2021

Install the latest Office updates to patch known bugs affecting copy and paste functionality.

Free Microsoft Office alternative

Try WPS Office for Stable Spreadsheet Formatting

If Excel 2021 glitches continue to cause your conditional formatting to disappear, switching to WPS Office provides a reliable and seamless solution. WPS Spreadsheet handles complex rules and formatting effortlessly without removing your original data settings.

1. Download and Install: Download WPS Office for free from the official website and run the quick installation.
2. Open Your Excel File: Launch WPS Spreadsheet and open your existing billing workbook.
3. Apply Formatting Securely: Use the Format Painter to duplicate your conditional rules securely without losing them on the source cell.

100% compatible with Microsoft Excel (.xlsx) formats and conditional formatting rulesFree, lightweight, and fast to launch without complex licensing issuesFamiliar user interface ensuring a zero-learning-curve transitionStable Format Painter and Paste Special features that preserve original formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does Format Painter remove my conditional formatting from the source cell?

This usually happens due to a workbook glitch, rule conflicts, or when the formatting formula uses relative references that become invalid once the 'Applies to' range expands. In rare cases, corrupted cached data in the workbook forces the original format to drop.

How do I lock a conditional formatting rule when copying?

To lock a conditional formatting rule, edit the rule's formula and use absolute cell references by adding dollar signs (e.g., $A$1). This ensures the rule continues looking at the exact same criteria even when applied to new cells.

Can Excel macros interfere with conditional formatting?

Yes. If a workbook contains VBA macros—especially those triggered by worksheet change events—they might be programmed to clear cell formats automatically upon editing or pasting. Disabling macros via the Trust Center can help rule this out.

Document & File Issues