Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Copying Conditional Formatting.

Copying Conditional Formatting

Written by Allen Wyatt (last updated September 9, 2022)
This tip applies to Excel 2007 and 2010


In Excel, conditional formatting is considered part of the regular formatting of a cell. If you want to copy conditional formatting from one cell to another, you can do so by simply copying the cell and pasting it (or its format) to another cell. If you want to copy a conditional format to a range of cells (and only the conditional format), the easiest way to do so is by following these general steps:

  1. Select the cell that contains the conditional format you want copied.
  2. Display the Home tab of the ribbon.
  3. In the Styles section, click Conditional Formatting. Excel displays various options related to conditional formatting.
  4. Click Manage Rules. Excel displays the Conditional Formatting Rules Manager. The format you want copied should already be filled in. (See Figure 1.)
  5. Figure 1. The Conditional Formatting Rules Manger.

  6. Select whatever appears in the Applies To box.
  7. Using the mouse, click and drag to select the range of cells to which the conditional format should be applied.

That's it. Excel does the rest and copies the conditional formatting, as you desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6253) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Copying Conditional Formatting.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Pulling Cell Names into VBA

Excel allows you to define names that can refer either to ranges of cells or to constant information, such as formulas. ...

Discover More

Hanging Indents in Wrapped Text

If you use hanging indents for some of your paragraphs, you may wonder why they don't look right when they wrap on the ...

Discover More

Making a Cell's Contents Italics within a Macro

You can use macros to process information in your worksheets. You may want to use that macro to apply the italic ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Sorting Conditional Formats Properly

Conditional formatting can be a great tool to get your data looking just the way you need. However, when you sort data ...

Discover More

Working with Multiple Conditions

When you apply conditional formatting, you are not limited to using a single condition. Indeed, you can set up multiple ...

Discover More

Noting Inactivity within a Timeframe

There are many times when you are creating a worksheet that you need to analyze dates within that worksheet. Once such ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 7?

2022-09-11 07:39:13


@Dave, I started when Excel first came out (on one of the first Macintosh OS's ... long before Windows existed) ...

@J. Woolley, thanks for the tip. I'll have a look at that !

2022-09-11 00:54:37

Dave Bonin


You’ve been using Excel for 36 years?!?!

Impressive. I’m only at 28 years.

2022-09-10 11:06:47

J. Woolley

My Excel Toolbox has macros to backup and restore a worksheet's conditional formatting (CF) using named ranges, which auto-adjust to worksheet changes.
See my comment dated 2021-08-14 10:11:28 at the bottom of the following Tip's comment section:
First make sure the current CF is correct by use of the Conditional Formatting Rules Manager (Alt+H+L+R), then use CFBackup. If you later find the CF has become corrupted, using CFRestore will usually fix the problem.
My Excel Toolbox also includes the following dynamic array function:
This function returns Applies To Range, Type, and Stop If True for each CF.

2022-09-10 06:56:53


Dave, thanks for the feedback. I'm aware, and also use custom formats where possible. There are some uses of conditional formatting however which if one wants to "mimic" them one would have to resort to macros ... which is what I'm doing in my add-ins.

Unfortunately, sometimes we have to work with files we get from other people and can't just change the design of the files without impacting many other aspects of certain business processes ...

When I have some time, I'm hoping to develop some VBA code in my add-ins to try and automatically scan the set of conditional format rules for "identical" conditions and then clean them up in the background. Something I feel Microsoft should build into Excel by default (but in the 36 years I've been using Excel I often have come across functionalities that were missing in Microsoft's release and were then added on by third party VBA developers ... so no surprise there ...

2022-09-09 12:14:22

Dave Bonin


That's one of the reasons I try to avoid using conditional formats whenever possible.

The most common use I have for what could be a conditional format is to color negative values red. In that case, I'll instead use a custom number format such as:

This does what a conditional format could have done but without actually using a conditional format.

Also note that zero values show up as a properly-justified "0   " without the extraneous trailing ".00" which only adds clutter. I often also use a format like:

This shades zero values to a light gray, further reducing visual clutter, yet still providing confirmation that a constant or formula result of zero is present.

There are a lot of other things you can do with custom number formats if you dig into them.

2022-09-09 04:31:06


Is there also a way to keep Conditional Formats "clean"? My experience is that when inserting rows or copying cells the list of "rules" in the Conditional Formats dialog box tends to grow, and it is not uncommon to have literally hundreds of conditional format rules where just half a dozen would be what I started with ...

Manually cleaning this out every so often is a real burden ...

From what I can see, Excel only "refrains" from creating a new rule when inserting a row (for example) if the rule that exists includes the entire column in the "Applies to" entry ...

2017-09-25 12:23:42


Thanks for the tip. In order to make it work. I had to hold down the control key. Otherwise it moved the formatting instead of copying.

2016-07-15 16:13:59

David Gray

You can also copy conditional formatting through the Paste Special option. However, be aware that some of the most effective and powerful formulas employ absolute column or row references, which do not react well to being copied.

2016-07-15 11:16:52


Gary, Conditional Formatting is not Excel's most stable algorithm. I prefer to keep things as simple as possible for Excel when dealing with Conditional Formatting. Sometimes when you copy to a whole row or range you will end up with multiple copies of the same Conditional Formatting applied to the row or range and then Excel gets confused and really slows down. The gentleman who shared method this with me suggested keeping it simple and treating Excel with respect! :)

2016-07-15 10:45:41


mjenkins, I don't quite understand. Why choose only the first cell in the row you're copying to? I get the same result, apparently, whether I choose one cell or the whole row. Please explain?

Thank you!


2016-07-15 10:32:08


I frequently need to insert rows into a wide spreadsheet and I like to make sure that the conditional formatting is copied into the cells. Here is how to accomplish this easily.
To Insert a Row:
Select row
Ctrl-C to copy row
Move 1 row down & select only the first cell in the row (1 cell only)
Right click with the mouse & choose Insert Copied Cells (this makes sure the conditional formatting is copied)
You may check the Conditional Formatting reference range to be sure it is all OK in the new row (Highlight the cell you want to check. On Home Tab click on Conditional Formatting in the Styles area. Click Manage Rules to see the formatting and ranges you want to verify.)
In your new row you can delete or overtype the text with the new entry's data.
I know that Excel is great at copying the conditional formatting, and I don't really need to check up on how well it performs this task, but if you are a double-checker, this works.

2016-07-15 09:17:40


For the step 6 enquirers.
In the "Applies to" box put a , (comma) after the currently entered cell detail, then you can drag you mouse over the area you wish to add.

2016-07-15 07:53:21


Just use the format painter; its far easier than any other way.

2016-07-08 11:13:58


This tip is incomplete. Please explain what to do to finish the job.

At the end of step 6, the Conditional Formatting Rules Manager is still open. Selecting OK or Apply MOVES the rule to the new range. It does not COPY the rule.

2016-03-14 14:18:14


In the conditional format formula, 'Applies to' ...
If your want to reference conditional format across columns use =A$1 (as in A1, B1, C1, D1); if you want to reference across rows use =$A1 (as in A1, A2, A3, A4).
Then copy it to your other cells.
Verify that the results are ok in the other cells.

2016-01-20 17:34:39


Jennifer Bower, I can't figure out your last post on changing $A$1 to just A1. Can you explain it further?

2016-01-13 13:28:59


Never mind! I found it on another site. Angelo, if you see this ... you need to remove the absolute references in your formula. So right now it probably reads: $A$1. If you edit this to just be A1 and then include the range of cells it applies to, it should change the reference cell. Hope this helps!

2016-01-13 13:15:30

Jennifer Brower

I reiterate what Angelo said. I cannot get the reference cell to change from the original cell. So all of the cells in the range that have the conditional format applied reference back to A1 instead of referencing their own cell A2, A3, A4, etc. I know it can be done because I've worked in spreadsheets that do this, but I can't figure out how to do it myself. Please help!

2015-11-12 11:06:43


I've to copy the conditional formatting present in B1 in B2. The conditional formatting contain a reference to A1. Copying the cell from B1 to B2 I get the same conditional format but the reference to A1 does not change to A2!

How can I resolve this problem ?

2014-10-08 12:20:33

Fred Thompson

It's not possible to execute step 6 when the conditional formatting dialog is still open. This tip is incomplete.

2012-06-18 09:46:52

TJ Burek

Conditional formatting is very useful in Excel, however, there is a downside to it. We've found that in very long spreadsheets (i.e. 30,000 rows) or in large spreadsheets with foreign text containing accent marks or special characters), condition formatting can make the spreadsheet sluggish when inserting lines.

We've also found that double or triple thickness borders on very long spreadsheets (i.e. 12,000 rows) or in large spreadsheets containing foreign text (with accent marks or special characters) will make filtering/ unfiltering painfully slow. Changing all borders to single thickness will make filtering/unfiltering instantaneous.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.