Formatting Issues

How to Keep Conditional Formatting Applied to an Entire Column in Excel Online

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to apply a conditional formatting rule to an entire column (e.g., Column I) to make specific text bold and red, but the applied range keeps changing or fragmenting.

Product: Excel Online
Device & OS: not provided
Scenario: Filtering, editing, copying, pasting, and adding rows within a table that has conditional formatting applied.
Observed behavior: The conditional formatting applied range fragments and changes automatically when modifying the spreadsheet, instead of staying locked to the entire column.

Before you start

Check your 'Conditional Formatting Rules Manager' to see your current ranges; if you have copied or pasted cells frequently, you will likely see multiple duplicated rules with fragmented cell ranges.

Solution 1Recommended

Use a VBA Script in the Desktop Application

Since Excel Online lacks a native feature to lock formatting ranges, switching to the desktop app and using VBA is the most reliable method.

Excel for the web does not provide a reliable way to prevent copying cells or inserting columns from altering conditional-formatting ranges. Formatting is copied alongside the cells, which splits the rules.

To permanently enforce formatting on an entire column, you must use the desktop application to run a VBA script that automatically reapplies the rule when changes occur.

Open Desktop Excel

In Excel Online, click on the 'Editing' dropdown menu at the top of the ribbon and select 'Open in Desktop App'.

Open the VBA Editor

Once in the desktop version, press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Apply a Worksheet Change Event

In the left project pane, double-click the specific worksheet you are working on. Paste a VBA macro designed to clear and reapply your specific conditional format (e.g., highlighting 'Updated' in bold/red) to Column I whenever the sheet is modified.

Save as a Macro-Enabled Workbook

Save your file as an Excel Macro-Enabled Workbook (.xlsm) to ensure the script continues to run.

Solution 2

Manually Reset the Applies to Range

If you must remain in Excel for the Web, you will need to periodically clean up and reset your conditional formatting rules.

Free Microsoft Office alternative

Switch to WPS Office for Advanced Desktop Formatting

Since Excel Online limits your ability to lock conditional formatting ranges and lacks VBA macro support, consider using a robust desktop alternative. WPS Office offers a powerful, fully-featured spreadsheet application that handles VBA and advanced formatting seamlessly.

1. Download and Install WPS Office: Visit the official WPS website, download the free suite, and install it on your computer.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook.
3. Manage Formatting and VBA: Use the built-in Conditional Formatting manager or access the VBA editor (ALT + F11) to secure your column formatting without the web limitations.

Completely free and lightweight desktop suiteFully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xls)Supports VBA macros to easily automate and lock formatting rulesFamiliar user interface ensuring a zero-learning-curve migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting range break when I copy and paste data?

When you copy and paste cells, Excel automatically copies the cell formatting along with the data. This duplicates the conditional formatting rules and applies them to the newly pasted cells, fragmenting the original unified range.

Can I lock a conditional formatting range in Excel for the Web?

No, Excel for the Web does not have a native, reliable way to prevent conditional formatting ranges from changing when rows are added, copied, or merged. You must use the desktop version with a VBA script to force the formatting to stay locked.

How do I fix duplicated conditional formatting rules?

To fix duplicated rules, go to Home > Conditional Formatting > Manage Rules. Delete the duplicate rules that apply to smaller fragmented ranges, and then update the 'Applies to' field of your main rule to encompass the entire column (e.g., =$A:$A).

Document & File Issues