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:
Figure 1. The Conditional Formatting Rules Manger.
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Setting up conditional formatting can be challenging under some circumstances, but once set it can work great. Unless, of ...
Discover MoreConditional formatting is a great tool you can use to customzie your worksheets. When you want to test whether a value in ...
Discover MoreYou can use conditional formatting to add shading to various cells in your worksheet. This tip shows how you can shade ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-09-25 12:23:42
Charlie
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
mjenkins
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
Gary
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!
Gary
2016-07-15 10:32:08
mjenkins
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
Kevin
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
RKeev
Just use the format painter; its far easier than any other way.
2016-07-08 11:13:58
Brad
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
mike
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
Vivian
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
Jennifer
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
Angelo
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments