How to Copy Conditional Formatting to Another Cell in Excel (2024)

Conditional Formatting in Excel enables you to quickly format a cell (or range of cells) based on the value or the text in it.

For example, below I have an example where I have student’s scores and I have used conditional formatting to highlight all the scores that are above 80.

If you’re interested in learning all the amazing things you can do with conditional formatting, check out this tutorial where I show some useful conditional formatting examples.

Once you have set the conditional formatting rules for a cell or range of cells, you can easily copy it to other cells on the same sheet or other sheets.

In this tutorial, I will show you how to copy conditional formatting from one cell to another cell in Excel. I will cover multiple ways to do this – such as simple copy-paste, copy and paste conditional formatting only, and using the format painter.

So let’s get started!

Copy Conditional Formatting Using Paste Special

Just like you can copy and paste cells in the same sheet or even across sheets or workbooks, you can also copy and paste the conditional formatting from one cell to another.

Note that you can not just copy and paste the cell. You have to make sure that you copy a cell but only paste the conditional formatting rules in that cell (and not everything else such as the value or the formula).

And to make sure you only copy and paste the conditional formatting, you need to use Paste Special.

Suppose you have a dataset as shown below where I have conditional formatting applied to column B (the Math score) so that all the cells that have the value more than 80 get highlighted.

Now, what if I want to apply the same conditional formatting rule to the second column (for Physics score) os that all the cells above 80 are highlighted in green.

This can easily be done!

Below are the steps to copy conditional formatting from one cell to another:

1. Select cell B2
2. Right-click and copy it (or use the keyboard shortcut Control + C)
3. Select the entire range where you want to copy the conditional formatting (C2:C11 in this example)
4. Right-click anywhere in the selection
5. Click on the Paste Special option. This will open the Paste Special dialog box.
6. In the Paste Special dialog box, select Formats
7. Click on OK

The above steps would copy the conditional formatting from column B and apply it to the selected cells in column C.

One thing to remember when using Paste Special to copy conditional formatting is that it will copy all the formatting. So if there is any border in the cells or if the text has been made bold, etc., that would also be copied.

Note: The same steps shown above would also work when copying and pasting conditional formatting to cells in another sheet or even another workbook.

Copy Conditional Formatting Using Format Painter

Format painter is a tool that allows you to copy the format from a cell (or range of cells) and paste it.

And since conditional formatting is also a part of the formatting, you can also use format painter to copy and then paste it.

Suppose you have a dataset as shown below where I have conditional formatting applied to the Math score column so that all the cells that have the value more than 80 get highlighted.

Below are the steps to use format painter to copy conditional formatting from the Math score column and apply it to the Physics score column:

1. Select the cell (or range of cells) from which you want to copy the conditional formatting
2. Click the Home tab
3. In the Clipboard group, click on the Format Painter icon
4. Select all the cells where you want the copied conditional formatting to be applied

Pro tip: In case you want to copy the conditional formatting and paste it on multiple cells or ranges (that you can not select at one go), click on the Format painter icon twice. That will keep the format painter active and you can paste the formatting multiple times (unless you hit the Escape key).

Once you have the format painter activated, you can use it on the same sheet, on some other sheet in the same workbook, and even on the other workbook.

Again, just like with Paste Special, Format painter also copies all the formatting (including the conditional formatting).

Also read: Conditional Formatting Not Working in Excel

Issue when Copying Conditional Formatting

In most cases, you will have no problems copying and pasting conditional formatting from one cell to another.

But you may face issues if you have used a custom formula to determine which cells to format.

This option allows you to create your own formula and formatting is applied in the formula returns TRUE for a cell and not applied when the formula returns FALSE.

If you have used a formula in conditional formatting that uses absolute or mixed references, then it may not work as expected when copied.

For example, in the below example, I have used the formula =$B2>=80 to highlight all cells in column B that have a value higher than 80.

But when I copy this conditional formatting to column C, it still references the B column and I get the wrong result (as shown below).

So, if you copy conditional formatting from one cell to another and don’t get the expected result, it’s best to check the formula used and adjust the references.

For example, in this case, I can change the formula to =B2>=80 and it should work fine.

If you’re wondering where the formula goes, click on the Home tab and then on Conditional Formatting.

In the options that appear, click on ‘New Rule’. In the New Formatting Rule dialog box, click on the option – Use a formula to determine which cells to format.

This will show you the field where you can enter the formula for the selected range. If this formula returns TRUE for the cell, it will get formatted and if it returns FALSE, it will not.

So these are two simple ways you can use to copy conditional formatting from one cell to another in Excel – using Paste Special and Format Painter.

And in case you see issues with it, check the custom formula used in it.

Hope you found this tutorial useful!

Other Excel tutorials you may find useful:

• Highlight Rows Based on a Cell Value in Excel (Conditional Formatting)
• Highlight EVERY Other ROW in Excel (using Conditional Formatting)
• How to Remove Table Formatting in Excel
• Search and Highlight Data Using Conditional Formatting
• How to Apply Conditional Formatting in a Pivot Table in Excel
• Highlight the Active Row and Column in a Data Range in Excel
• Apply Conditional Formatting Based on Another Column in Excel