Copy Conditional Formatting in Excel

by

When working in Excel, it is normal practice to copy conditional formatting from one cell to another.

It saves us time because we don’t have to create the same conditional formatting rule over and over again. It also reduces conditional formatting errors.

In this article, I am going to show you some easy methods to copy conditional formatting in Excel.

Method 1 – Copy Conditional Formatting Using Conditional Formatting Rules Manager

The conditional formatting rules manager dialog box is the best way to copy conditional formatting in Excel.

We can also use the copy format for copying conditional formatting in Excel.

However, when we copy format, we are copying all of the formats (number formats, borders, font colors, and so on) of the copied cell.

So, if we just want to copy the conditional formatting, the conditional formatting rules manager is the best choice.

Two tables show the attendance percentages of two student groups. I used conditional formatting to highlight the attendance percentages that are less than 75% in Group A’s attendance % (Column B).

Data set with conditional formatting applied to one column

Now I want to apply the same conditional formatting to the attendance percentages of Group B students (Column E).

I can copy the conditional formatting that has already been applied to the cells in column B to the cells in column E rather than setting the same conditional formatting rule again for column E.

I can do this by following the steps given below.

  1. Go to the Home tab.
Click the home tab
  1. Click the Conditional Formatting icon (Which is in the Styles group).
Click on the conditional formatting icon in the styles group
  1. Select “Manage Rules…” from the expanded list of Conditional Formatting.
Click on the manage rules option

Then, Excel will open the “Conditional Formatting Rules Manager” dialog box.

Conditional Formatting Rules Manager dialog box
  1. Expand the “Show formatting rules for” dropdown list and select “This Worksheet”.
Expand the Show formatting rules for dropdown list
  1. Select the rule that we want to copy and click the “Duplicate Rule” button.
Click on the duplicate rule icon
  1. Change the cell reference of the duplicated rule to the cell range that we want to apply the conditional formatting. So, I am changing the cell reference of the duplicated rule to cells E3 to E7.
Change the applies to range
  1. Finally, click the “OK” button of the “Conditional Formatting Rules Manager” dialog box.
Click OK button

Now, I have copied the conditional formatting of cells B3 to B7 into cells E3 to E7.

Conditional formatting has been copied to column E
Also read: How to Copy Formatting In Excel?

Method 2 – Copy Conditional Formatting Using Format Painter

We can use the Excel format painter to copy the conditional formatting in Excel.

This is, in my opinion, the easiest method to copy conditional formatting in Excel.

Below two tables show the attendance % of two student groups. I have applied conditional formatting to the attendance % (Column B) of Group A to highlight the attendance percentages that are less than 75%.

Data set with conditional formatting applied to one column

Now, I want to apply the same conditional formatting to the Group B students’ attendance percentages (Column E).

Instead of creating the same conditional formatting rule again for column E, I can copy the already applied conditional formatting in column B cells to column E cells.

I can do this by following the below steps.

  1. Select the cells that contain conditional formatting. So, I am selecting cells B3 to B7.
Select the column from which you want to copy the conditional formatting
  1. Go to the Home tab.
Click on the home tab
  1. Click the “Format Painter” icon (Which is in the Clipboard group).
Click on the format printer icon

If you want to copy the conditional formatting to multiple different cells, double-click the “Format Painter” icon.

  1. Select the first left cell of the range where we want to apply the conditional formatting. In this case, I am selecting cell E3.
Select the first cell in the column where you want to copy condition formatting

As soon as I select cell E3, conditional formatting, including all other formatting, is applied to cells E3 to E7.

I can copy just cell B3’s conditional formatting without copying every cell from B3 to B7 (as it’s the same conditional formatting rule that is applied to all the cells).

Then, to apply the conditional formatting to the destination cells, I need to select all cells E3 through E7.

I decided not to use this method because it leads to the removal of the bottom table border of cell E7.

Format printer copies all formatting

So, keep in mind that the format painter copies all of the formatting from the source cells, not just the conditional formatting.

Also read: How to Copy and Paste in Excel Without Changing the Format?

Method 3 – Copy Conditional Formatting Using Paste Special Option

We can also use the “Format Paste” option to copy conditional formatting in Excel.

The following tables show the attendance percentages for two student groups. I used conditional formatting on Group A’s attendance % (Column B) to highlight percentages that were less than 75%.

Data set with conditional formatting applied to one column

Now I’d like to apply the same conditional formatting to the attendance percentages of Group B students (Column E).

Instead of writing a conditional formatting rule again for column E data, I can copy the conditional formatting that was already applied in column B cells to column E cells.

I can do this by following the steps below.

  1. Select the cells that contain conditional formatting. So, I am selecting cells B3 to B7.
Select the column from which you want to copy the conditional formatting
  1. Go to the Home tab and click the “Copy” icon (which is in the Clipboard group) to copy the selected cells.
Click on the copy icon in the ribbon

I can also use the “Ctrl + C” keyboard shortcut to copy the selected cells.

  1. Select the first left cell of the range where we want to apply the conditional formatting. In this case, I am selecting cell E3.
Select first cell in column where you want to copy condition formatting
  1. Expand the “Paste” options of the Home tab.
Expand the paste options in the ribbon
  1. Go to the “Other Paste Options” section and click the “Format” icon.
Click on the paste format icon

Now, I have applied conditional formatting of Group A’s attendance % to Group B’s attendance %.

Conditional formatting has been copied to the destination range

Keyboard Shortcuts to Copy and Paste Conditional Formatting

I can use below Excel keyboard shortcuts to paste the formats of the copied cells.

  • ALT + H + V + R
ALT H V R shortcut
  • Menu Key  (Key between ALT and Ctrl) + S + R
Menu Key (Key between ALT and Ctrl) + S + R
  • ALT + E + S + T + Enter
ALT + E + S + T + Enter
  • (Ctrl + ALT + V) + T + Enter

(We have to press Ctrl, ALT, and V together. Then press T. After that, press the “Enter” key)

Control ALT V T Enter
Also read: How to Remove Conditional Formatting in Excel?

In this article, I have covered three different methods to copy conditional formatting in Excel.

While the best way to do this is by using the Conditional Formatting rules manager, I often use the keyboard shortcuts and the Paste Special method as it gets the work done in most cases.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment