OmniDocHow to Copy Conditional Formatting to Other Cells in Excel
Copy Excel conditional formatting to other cells with Format Painter, Paste Special, or by extending the rule's range, so you don't have to recreate it.
If you have built a conditional formatting rule in Excel, such as highlighting overdue dates or flagging totals above a target, you probably want the same rule on other columns or sheets. Rebuilding it by hand takes time and invites mistakes, especially when the rule uses a formula. This guide shows you how to copy Excel conditional formatting to other cells in three ways, and how to make sure the copied rule still compares the right values.
The short version is that you can copy the formatting with the Format Painter tool, paste only the formats, or extend the range of the existing rule. Each method suits a different situation, and the table below helps you choose. The steps and checks that follow apply to a standard Excel workbook.
How do I copy Excel conditional formatting to other cells? The short answer
Use Format Painter for a quick copy to a few areas, use Paste Special with formats only when you want to copy from a cell you have already copied, and extend the rule's range when the new cells belong to the same block of data.
| Your situation | What to do | Why |
|---|---|---|
| You want to copy a rule to a few scattered cells | Use the Format Painter tool | It is fast and copies the rule along with the cell's other formatting |
| You need to paste the rule into a block that already has its own formatting | Copy the source cell, then use Paste Special and choose formats only | It keeps the content of the target cells and copies only the formats |
| The new cells continue the same column or table | Edit the rule's range to include them | You keep one rule, so future changes apply everywhere at once |
| The rule must work on another worksheet | Use Format Painter or Paste Special on that sheet | Both methods work across sheets; check the formulas afterward |
Before you start
- Find the cell that shows the rule working. Format Painter copies from whichever cell you select, so pick a cell that currently displays the highlight you want to reuse.
- Note the target cells' current formatting. Both copy methods overwrite the fill, font, borders and number format of the cells they land on. If those cells already have formatting you want to keep, copy the rule another way or record the settings first.
- Decide whether the rule uses relative or absolute references. A formula such as =C2>100 shifts as it moves, while =$C2>100 keeps the column fixed. Knowing which one you have tells you what to check later.
- Open the workbook you plan to edit. Copying a rule to another workbook is not covered here, so keep the source and target in the same file.
- Know your Excel version if the rule refers to another sheet. Older versions of Excel do not allow conditional formatting formulas that point to other worksheets, so a copied rule may fail on an older computer.
Copying conditional formatting with Format Painter, step by step
Format Painter is the quickest way to copy a rule to cells you can reach by clicking or dragging. It copies the source cell's formatting, including any conditional formatting that applies to it.
- Click the cell that has the rule. Format Painter takes its settings from the selected cell, so the cell must show the formatting you want to copy.
- Select the Format Painter tool. It sits on the Home tab of the Excel ribbon, next to the clipboard group. Your pointer will change to show a brush.
- Click or drag over the target cells. A single click pastes the formatting into one cell. Dragging across a range applies it to every cell in the range.
- Double-click the tool to paste more than once. The tool stays active so you can paint several areas in a row. Press Esc when you are done.
- Scroll through the pasted area to confirm the highlights appear. Format Painter only copies rules that apply to the source cell, so a rule that covers a whole column may need the range extension method below to reach every row.
Extending the rule's range instead
If the new cells continue the same table or column, extending the range of the existing rule keeps everything in one place. Changes to the rule then apply to all the cells automatically.
- Click any cell inside the range that has the rule.
- Open the conditional formatting rules manager for the worksheet. It lists every rule on the sheet along with the range each one covers.
- Select the rule and edit the range it applies to. For example, change A2:A20 to A2:A50 so the rule covers 30 more rows.
- Save the change and check the new rows. Type a value that meets the condition into one of the added cells to confirm the highlight appears.
Pasting only the formats
Paste Special lets you copy a rule without touching the values in the target cells. This works well when the target cells already hold data you need to keep.
- Select the source cell and copy it. Press Ctrl+C on Windows or Command+C on a Mac.
- Select the target cells. Click the first cell or select the full range.
- Open Paste Special and choose the option for formats only. Excel pastes the cell's formatting, including conditional formatting, and leaves the values alone.
Check the result
After copying, test the rule on real data and read the formulas, because most problems come from references that moved when the rule was copied.
| Check | How to see it | If it's wrong |
|---|---|---|
| The highlight appears on the new cells | Type a value that meets the condition into a target cell | Run Format Painter again, or extend the rule's range to include the cell |
| The formula compares the right cells | Open the rules manager and read the formula against the first cell in the range | Add $ before the column letter, for example $C2, so the reference stays fixed |
| The target cells kept their values | Compare the values with a copy of the sheet from before the change | Press Ctrl+Z to undo, then use Paste Special with formats only |
| No duplicate rules overlap | Count the rules listed for the same range in the rules manager | Delete the extra copy so the same condition is not applied twice |
🎯 Which approach fits your sheet?
- A few scattered cells or a short list: Format Painter gets the job done in seconds.
- A table or column that keeps growing: Extend the range of one rule so new rows pick up the highlight without another copy.
- A block with data you must not disturb: Use Paste Special with formats only.
❓ FAQ
1. Does Format Painter copy the rule or just the color?
It copies the rule along with the cell's other formatting. The target cells then follow the same condition, and the highlight changes when their values change.
2. Can I copy conditional formatting to another worksheet?
Yes, Format Painter and Paste Special both work across sheets. If the rule's formula refers to another worksheet, check that your version of Excel supports it.
3. Why does my copied rule compare the wrong cells?
Relative references shift when a rule moves, so a formula written for row 2 may point to row 10 in the copy. Add a $ before the column or row you want fixed, then test a few cells to confirm.
4. How do I apply a rule to a whole column?
Extend the rule's range so it covers the full column, or select the column and use Paste Special with formats only. Extending the range is easier to maintain because one rule controls the whole column.
📝 Summary
To copy Excel conditional formatting, use Format Painter for quick copies, Paste Special with formats only to protect existing data, or extend the rule's range to cover new cells. Whichever method you choose, check the references and test a few values so the copied rule compares the cells you intended.
