Copy Conditional Formatting – 5 Handy Ways to Do It

Why do people want to copy conditional formatting in Excel?

The main reason – I think – is that nobody wants to retype conditions. Yes, Conditional formatting conditions may take time to develop and tweak. If they are in some workbook or sheet you certainly don’t need to retype  them all again.

Here are 5 handy ways to copy conditional formatting in your Excel sheets…

It is important that you know…Excel treats conditional formatting as standard format, so with a simple copy and paste you will inherit the conditional formatting criteria. This is not always the case since you may have content in the target cells or perhaps you don’t know where the conditional formats are.

The following tips will show you how to do it quickly and easily for those various scenarios…

1) Copy and paste

Do this when the destination cells are empty or you want to replace the content.

Yes. Only copy the cell that contains the conditional formatting and choose the destination cell or range and paste.

2) Copy and paste special

Do this when the destination cells are not empty.

Select the range>Copy>Select destination cells>Edit>Paste Special>Formats>Ok

On Excel 2007

Select the range>Copy>Select destination cells>Home>Clipboard>Paste>Paste special>Formats>Ok

The next tip is quicker than this one…

3) Use the Format Painter to Copy Conditional Formatting

I love this way of copying Excel conditional formatting. It is handy and you are accustomed to doing it with plain formatting.

Just select the cell or range with the conditional formatting you want to copy, and then pick the format painter (if you don’t know the location of the tool you are living under a rock!) and then click on the target cells. The conditional formatting will be inherited.

Tip: double click the painter so you can apply format to multiple target cells or ranges

As I said to you before, Excel treats conditional format the same way as plain format.

The following way of copying conditional formatting is not so know but still very powerful…

4) Extend list formats and formulas to Copy Conditional Formatting

It means that the format of the table will be automatically replicated downward as you add more entries to the list.

The graphic below illustrates the point…

The cell A7 doesn’t have any conditional format. If you have the extend list formats and formula option activated; then, the cell A7 will acquire the adjacent table formatting (color if cell < 30, in this example).

This option is activated by default. If not…

Activate this option this way:

On Excel 2003 go to Tools>Options>Edit>extend list formats and formulas

On Excel 2007 go to Office button>Excel Options>Advanced>Editing options>Extend data range formats and formulas

Then, the formatting is copied automatically as you enter data down in your lists.

5) Select the adjacent range and apply conditional formatting (Excel 2003)

You can select contiguous cells to the range that contains the conditional formatting, and select the conditional formatting dialog and click ok.

The format will be copied to the adjacent cells.

Conclusion:

Now that you know more than one way to copy conditional formatting, you won’t be out of ideas to do it. You will do it with confidence and speed.

Important reminders

  • Sometimes the destination range does not contain the same values that trigger the conditional formatting format. This does not mean that the conditional formatting has not been copied. If the cell remains non-formatted, change the value and see that it works
  • You can copy and paste conditional formatting between sheets and workbooks the same easy way
  • If you don’t know where the cells with conditional format are, use Go To command this way: F5>Special>Select>Conditional formats>Ok

That’s all for now….

Please use these tips to develop and hone your Excel skills, advance your business and your career, and to add value to the world.