Excel Conditional Formatting on All Sheets

How-to basics · Updated November 18, 2026

Excel does not have a single built-in command that applies one conditional-formatting rule to every worksheet at once. Create the rule on each sheet, or copy a correctly formatted template range to matching ranges on the other sheets; for repeated workbooks, a VBA macro can automate the copying.

What “all sheets” means in Excel

Conditional formatting rules belong to worksheet cells. A rule created for a range on one sheet does not automatically appear on other sheets, even if those sheets have the same layout or are grouped. The rule has a range it applies to, a condition it evaluates, and a format to display when the condition is met. Excel stores and manages those rules separately for each worksheet.

This distinction matters when a workbook has monthly tabs, department tabs, or regional reports. If you add a red fill to overdue dates on January’s sheet, February’s matching cells do not inherit the rule. You must create or copy it there too. Grouping sheet tabs can make many worksheet edits at once, but it is not a dependable way to create one shared conditional-formatting rule across sheets.

There are two practical goals people may mean by “all sheets”: making the same rule work on the same cell range in several sheets, or making it work wherever similar data appears, even when the ranges differ. The first is straightforward with copying. The second requires a consistent layout, separate rules with adjusted ranges, or automation designed for the workbook.

Apply the rule to each sheet manually

For a small number of worksheets, creating the rule separately is the clearest method. It avoids copying unrelated formatting and makes it easy to check that each rule applies to the intended cells.

  1. Open the first worksheet and select the cells the rule should affect.
  2. Choose Home > Conditional Formatting, then select a built-in rule or choose New Rule.
  3. Set the condition and the format, then select OK.
  4. Repeat on each other worksheet, selecting its corresponding range and creating the same rule.

For example, suppose each sheet has a status column and you want cells containing “Late” to stand out. Select the status range on each sheet, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter Late, and select a format. This rule checks each selected cell for that text. If your goal is to highlight an entire row based on its status, use a formula-based rule instead; the distinction is explained below.

Manual setup is usually best when there are only a few tabs or when sheets are not identical. It also lets you tailor the range on each sheet—for instance, one team may have 40 records and another 400—rather than applying formatting to a needlessly large area.

Copy an existing rule to matching ranges

When sheets share the same layout, copy the formatted range from a template sheet and paste its formats into the equivalent range on another sheet. In desktop Excel for Microsoft 365 on Windows, select the source cells, copy them, select the destination cells, then choose Home > Paste > Paste Special and select Formats. This transfers formatting, including conditional formatting, to the destination cells.

Paste Special Formats transfers more than conditional formatting. It can also copy direct font, fill, border, and number formatting from the source. If the destination already has intentional styling, check it after pasting. The Format Painter can also transfer formatting, but it is less convenient for large ranges or repeated destinations. To avoid carrying unwanted styles, create the rule manually or use a macro designed around your workbook.

Before copying, verify that the source and destination ranges have matching shapes and purposes. If the source rule applies to A2:F100, for example, copy that range to the same address on each comparable sheet. Excel adjusts relative references in formulas as it copies the rule, just as it adjusts references when a worksheet formula is copied. Absolute references remain fixed, so review formulas that use dollar signs.

After copying, open Home > Conditional Formatting > Manage Rules on the destination sheet. Confirm that the rule is listed and that its Applies to range is correct. A rule can exist but appear ineffective if its range does not include the cells you expected.

Use formula rules for rows and more specific tests

A formula-based rule is useful when the condition is in one column but the formatting should cover a whole row. Assume the table has task details in A2:F100 and each task’s status is in column F. Select A2:F100, choose Home > Conditional Formatting > New Rule, select the option to use a formula to determine which cells to format, and enter =$F2="Late". Choose a format and confirm.

The dollar sign before F locks the status column, while the row number is allowed to change. Excel therefore checks F2 for the first row in the selected range, F3 for the next row, and so on. Every cell across a row receives the format when that row’s status is Late. When applying this rule to another sheet, select the equivalent range there and create or copy the rule with the correct starting row.

Conditional-formatting formulas must return a result Excel can evaluate as true or false. They use ordinary Excel formula syntax, but direct references to cells on another worksheet are restricted in conditional-formatting formulas. If a condition depends on a different sheet, consider a named range or a formula that uses INDIRECT, and test it carefully. For rules that only evaluate data on the current sheet, ordinary relative and absolute references are simpler and more robust.

Check the rule scope in Manage Rules

Excel’s Rules Manager is the best place to diagnose which cells a rule affects. Choose Home > Conditional Formatting > Manage Rules. The Show formatting rules for menu lets you view rules for the current selection or the worksheet. Select the worksheet view when you want to inspect all rules stored on that sheet.

Read the Applies to field carefully. It might show a range such as =$A$2:$F$100. If it points to only one column, the rule will not format the rest of the row. If it includes header rows or blank areas, those cells may receive formatting too. You can edit a rule or its range in the manager, but changes made there still apply to that worksheet’s rule—not to every sheet in the workbook.

Rules can overlap. For example, one rule may make a cell red when a value is overdue, while another makes it green when the value is complete. In the Rules Manager, review their order and any available Stop If True setting. The order and rule logic can affect which format is visible, so copying rules without checking the destination may reproduce an unintended result.

Automate copying with VBA for repeated layouts

If a workbook has many identically structured sheets, VBA can copy formats from a template range to the matching range on every other sheet. The example below assumes the first worksheet is the template and the relevant range is A2:F100. It copies all formatting in that range, not only conditional formatting, so it can overwrite destination font, fill, border, and number formats as well.

Sub CopyFormattingToAllSheets()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range

    ' Use the first worksheet as the formatting template.
    Set sourceSheet = ThisWorkbook.Worksheets(1)
    Set sourceRange = sourceSheet.Range("A2:F100")

    For Each targetSheet In ThisWorkbook.Worksheets
        If targetSheet.Name <> sourceSheet.Name Then
            sourceRange.Copy
            targetSheet.Range(sourceRange.Address).PasteSpecial Paste:=xlPasteFormats
        End If
    Next targetSheet

    Application.CutCopyMode = False
End Sub

Use this only when those destination ranges are intended to match the template. Save a separate copy of the workbook before running any macro; macros cannot be undone with Ctrl+Z. To run VBA, use desktop Excel, enable macros only when you trust the workbook and its code, and save a macro-enabled workbook if you need to retain the macro. Excel for the web does not run VBA macros.

Common problems and how to fix them

  • The rule appears on one tab only: That is normal. Create it on the other sheets or copy the formatted range to them.
  • The format appears on the wrong rows: Check the rule formula’s row reference and its Applies to range. A formula written for a range starting on row 2 may not work as intended if pasted into a range starting on row 5.
  • Only one column changes color: The Applies to range may cover only that column. For row highlighting, select the full row range and use a formula that locks the test column.
  • Copying adds unwanted styling: Paste Special Formats copies ordinary cell formats too. Reapply the destination’s intended styles, or create the conditional-formatting rule directly.
  • Rules seem duplicated or conflict: Inspect Manage Rules on the affected worksheet, remove obsolete rules, and confirm rule order and ranges.
  • A copied formula checks the wrong cell: Review which references are relative and which have dollar signs. Relative references shift during copying; absolute references do not.

It is also useful to distinguish conditional formatting from regular cell formatting. A colored fill applied from the Fill Color button is static: Excel does not change it when a value changes. A conditional-formatting fill is displayed only while its rule’s condition is met. If you want cells to update automatically as data changes, confirm that the color comes from a conditional-formatting rule.

Windows, Mac, and Excel for the web

The main approach is similar in Excel for Microsoft 365 on Windows and Mac: select cells, open the Conditional Formatting controls on the Home tab, and create or manage rules. Ribbon labels and dialog layouts can differ slightly between platforms and updates, but the key principle remains the same: rules are managed per worksheet. Copying and using Paste Special Formats is also available in desktop Excel, though the exact interface can vary.

Excel for the web includes conditional-formatting tools, but its available controls may differ from desktop Excel. It cannot run VBA. If you need a macro, advanced rule management, or a workflow that depends on desktop-only controls, open the workbook in the desktop app. In every version, check the rule’s range on each worksheet rather than assuming a change on one tab has propagated to the others.

Frequently Asked Questions

Can I apply conditional formatting to all Excel sheets at once?
No single built-in command applies one conditional-formatting rule across every worksheet. Create the rule on each sheet, copy a formatted range to matching ranges, or automate suitable repeated layouts with VBA.

Does selecting or grouping all sheet tabs copy conditional formatting?
Do not rely on grouped tabs to create a shared rule. Conditional-formatting rules are stored for individual worksheets. Use a deliberate copy-and-paste method or set up the rule on each sheet and verify it in Manage Rules.

Will Paste Special Formats copy conditional formatting?
Yes, copying a range and pasting its formats can transfer conditional formatting. It also transfers other formatting, such as fills, fonts, borders, and number formats, so check the destination afterward.

Can one conditional-formatting rule refer directly to another sheet?
Direct references to another worksheet are restricted in conditional-formatting formulas. A named range or INDIRECT may help for some designs, but a rule based on data on the same sheet is generally easier to maintain.

Can Excel for the web apply the rule to every worksheet?
It offers conditional-formatting tools, but it does not provide a universal all-worksheets rule command, and it cannot run VBA. Apply or copy rules sheet by sheet, or use desktop Excel for a VBA-based workflow.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.