How to Copy Conditional Formatting in Excel (3 Easy Ways)

The number of convenience stores everywhere is a true indicator of how much we revel in convenience. Excel agrees with the lot of us and harbors many such conveniences with multiple methods.

Sticking to that theme, today’s tutorial welcomes you to learn about copying Conditional Formatting in Excel. The highlights of this how-to are using Paste Special, the Format Painter, and the Conditional Formatting Rules Manager to copy Conditional Formatting, also touching on a small issue you may face with this Excel feature.

So without further ado, lets dive in.

Copy Conditional Formatting in Excel

Why would you want to copy Conditional Formatting?

You have sales data split branch-wise and have highlighted the figures less than $100 a day. You want to copy the same Conditional Formatting of this data for a newly added branch. Or you could have highlighted alternate rows and want the same highlighting for an added column.

Basically when you copy Conditional Formatting, you’re copying a Conditional Formatting Rule. The condition or criterion specified in that rule allows the new range to take on the format according to its own values. What will be copied from the copy area is not the static format, but the criteria of the Conditional Formatting Rule which will then format the paste area accordingly.

Simple copy-pasting will result in ditto pasting of the value, Conditional Formatting Rule, and the format of the cell(s). See this data below with Conditional Formatting applied to one column:

Why would you want to copy Conditional Formatting

If we copy the numbers from column C to columns D, E, and F, the latter columns will be overwritten:

Why would you want to copy Conditional Formatting

Therefore, you will have to employ one of the methods in this tutorial to copy the format. It's not only highlighting that you can copy; any Conditional Formatting item can be copied. Highlighting, Data Bars, Color Scales, and Icon Sets; all these items will be copied the same way. Highlighted data like this:

Why would you want to copy Conditional Formatting

And Data Bars like this:

Why would you want to copy Conditional Formatting

Their formats will be copied no differently! Allow us to demonstrate.

Let’s get copying!

Method #1 – Using Paste Special

The prime instinct of copying anything is to copy-paste it but not everything is a Ctrl + C, Ctrl + V situation. Those situations, including Conditional Formatting, require pasting a little specially. The Paste Special options in Excel include pasting the format from copied cells.

The steps ahead use an example to copy Data Bars from one column to another. The sales figures for the year 2025 have been visually enhanced with Conditional Formatting’s Data Bars. The aim is to copy the format from the sales for 2025 to 2026. Use the following steps to copy the Conditional Formatting with Paste Special.

  • Select the cells that you want to copy the format from.
  • Using Paste Special to copy conditional formatting
  • Copy the selected cells by pressing the Ctrl + C
  • Using Paste Special to copy conditional formatting
  • Select the range where the Conditional Formatting is to be pasted.
  • Right-click the selection and point to Paste Special from Paste Options in the opened context menu.
  • Under Other Paste Options, click on the Formatting button.
Using Paste Special to copy conditional formatting

Alternatively use the keyboard shortcut Alt + e + s + t + Enter after selecting the paste area. The Alt + e + s keys open the Paste Special dialog box, t will select the Formats radio button and the Enter key will select the OK command.

Whether you opt for the right-click context menu or the keyboard shortcut, the copied cells will render their format to the selected range for pasting:

Using Paste Special to copy conditional formatting

Note in the sample shot above that the Data Bars have been pasted according to the cell values in the paste range. This also holds true for the other types of Conditional Formatting; if a value in one list is highlighted, the copied format won’t highlight the paste range according to the copied range’s values but according to the paste range’s values.

Method #2 – Using Format Painter

 The Format Painter has one job; painting the format from one point to another. Sounds like we can work with that. The Format Painter can pick up on Conditional Formatting and paint/paste the format to a range of choice. Find out how to copy Conditional Formatting using the Format Painter with these steps:

  • Select the range with the preferred format.
  • Click on the Format Painter button (double-click for using the Format Painter for multiple points) in the Home tab’s Clipboard
  • Using Format Painter to copy conditional formatting
  • The cursor will be changed to a cross with a paintbrush icon.
  • To apply the format, select the area that you want to paint the copied format to with the new cursor.
Using Format Painter to copy conditional formatting

The Format Painter will paint the format to the new selection:

Using Format Painter to copy conditional formatting

Method #3 – Using the Conditional Formatting Rules Manager

 The third option for copying a Conditional Formatting Rule is to duplicate the rule in Conditional Formatting’s Rules Manager. The Duplicate Rule option in the Rules Manager will exactly copy a rule and you can then edit the range it is applied to. Let’s show you how to copy Conditional Formatting using the Rules Manager with our case example:

  • To open the Rules Manager, use the Conditional Formatting button in the Home tab’s Style group and select Manage Rules from the menu.
  • The Show formatting rules for bar shows Current Selection. Change it to This Worksheet to view the rules for the current worksheet.
  • Using Rules Manager to copy conditional formatting
  • Select the rule that you want to copy and click on the Duplicate Rule button from the bar at the top.
  • This will create an identical Conditional Formatting Rule.
  • Using Rules Manager to copy conditional formatting
  • Click on the Applies to field to edit the reference.
  • In our example case, we will change the reference from C3:C14 to D3:D14. You can do this manually or you can select the new range on the worksheet after clicking on the Applies to field.
  • When done, click on the OK button of the Rules Manager.

The duplicated rule with the altered cell reference copies the Conditional Formatting to the sales figures in column D:

Using Rules Manager to copy conditional formatting

Issues when Copying Conditional Formatting

Conditional Formatting is a pretty smart feature that shouldn’t usually cause any bother. The possibility of bother comes with using custom formulas in the Rules. The problem that you may see with custom formulas is static formatting.

That means when you will try to copy the formatting from, let’s say, column A to column B, it will be the same in both columns implying that Conditional Formatting is only accounting for the values in column A and not column B. This is not Conditional Formatting malfunctioning and there’s a small reason behind the appearance of this issue.

Let’s see what this issue looks like with a new example.

In our example, we have the monthly sales for the quarter also categorized by the sales representatives. Employees who managed sales at or above $1,600 will receive a bonus. For the first month, we already applied a Conditional Formatting Rule to highlight the sales at or exceeding $1,600 and things are alright at this point.

Issues when Copying Conditional Formatting

But when we tried copying the conditional format, here’s what happened:

Issues when Copying Conditional Formatting

In one glance, we know that's not right. You can see that the sales figures in February, March, and April are highlighted in a linear pattern extending from column A. The sales greater than and equal to $1,600 have not been highlighted and the focus of the copied format is only visual.

As to why that’s happening, let’s check the particulars of the Conditional Formatting Rule. Select a cell from column A’s sales and go to Home tab > Conditional Formatting icon > Manage Rules. In the launched Rules Manager, select the relevant rule and hit the Edit Rule button.

There’s the culprit:

Issues when Copying Conditional Formatting

The formula entered for this rule is as follows:

=$C6>=1600

Column C is locked as an absolute reference while the row is free. This type of cell reference is fed into the formula when you want to highlight the full row of the cell that meets the condition of the formula.

If we convert the absolute reference into a relative one, the new formula would be as follows:

=C6>=1600

This keeps the row and column, both relative. Close the dialog box and the Rules Manager by selecting the OK buttons.

Now the correct figures have been highlighted from the edited Conditional Formatting Rule:

Issues when Copying Conditional Formatting

A few ways of copying Conditional Formatting, a small troubleshooter on an issue with copying Conditional Formatting, bagged it! More items for your Excel baggage? Coming up!

About Mehwish Javaid

Mehwish, an ACCA-qualified professional, transitioned from an audit trainee to an Excel specialist. With a foundation in financial auditing, her 4+ years of Excel expertise, showcased as a Content Specialist at ExcelTrick, bridges her auditing background with advanced spreadsheet skills. Read more...