How to Copy Conditional Formatting with Relative Cell References in Excel

Conditional Formatting is an excellent tool in Excel to customize cells’ formatting based on user-defined condition(s). By using this amazing feature, we can highlight cells, and set different colors on the cells that satisfy our applied conditions. But sometimes we may need to copy an existing formatting containing relative cell references and paste them into another part of the worksheet. In this article, we will discuss how to copy conditional formatting with relative cell references in excel. So let’s get started.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

2 Useful Methods to Copy Conditional Formatting with Relative Cell References in Excel

In this section, we will demonstrate 2 effective methods to copy conditional formatting in excel with relative cell references. To illustrate this, let’s take an example where we have marks of 4 students on Mid Term and Final Term for Biology and Physics. (See the figure below)

Here, we can see that in Biology, the final term marks which are less than the Mid Term marks have been highlighted with the yellow background color. The conditional formatting that we used for cells C6:C9 are shown below:

Here, the conditional formula that we have used is =C6<B6 which is applied to $C$6:$C$9. Now we want to copy the conditional formula and paste it to F6:F9 so that the condition becomes = F6<E6. We can accomplish that by using two methods. Let’s start with our first method.

1. Utilizing Format Painter to Copy Conditional Format with Relative Cells in Excel

This is the easiest approach to copying conditional format with relative references in excel. Here, we will use a special tool known as Format Painter. Follow the steps below.

Steps:

• First, select any cell that contains the conditional formatting that you want to copy. Here I have selected C6. you can select any cells from C6 to C9 you want.
• Now, on the Home tab, click on Format painter under the Clipboard group.

• Then, you will see a paintbrush icon on the screen like this below.

• Now, take the icon to F6, left-click on the mouse and drag it to F9. You should see that the F6:F9 cells have been conditionally formatted.

• For convenience, we are providing the complete process in a short video below.

• If we want to check the formula of the conditional formatting of a cell, we can check that by first clicking on the cell then going to the Conditional Formatting option and choosing Manage Rules…

• Here, I have selected F7 and the conditional formatting formula for this cell is as we expected (F6<E6).

2. Copying Conditional Formatting with Relative Cells in Excel Using Paste Special Feature

Now, there is another alternative way to Format Painter. Alternatively, we can use the Paste Special Feature of Excel to copy conditional formatting even with relative cell references. To know more, follow the steps below.

Steps:

• First, Select the cells that contain the conditional formatting. Here, I have selected C6:C9.

• After that, press Ctrl+C to copy.
• Now select F6:F9 and right-click on the mouse. You will see a menu. From that menu, select Paste Special.

• Now, you will select the Paste Special From there, select Formats, and click OK.

• Finally, you will see that the cells of F6:F9 have been conditionally formatted.

• If you want to check the formula of conditional formatting, you can do that in a similar way that we have shown in the first method.

How to Copy Conditional Formatting to Another Sheet in Excel

In excel, we can copy the conditional formatting from one sheet and paste it into another sheet. To do that, we can use the Format Painter Option. To know more, follow the steps below.

Steps:

• First, go to the sheet from where you will copy the conditional formatting. Then, click on any cell that contains the conditional formatting. Here, I am selecting a formatted cell (C7) from the 1. Format Painter sheet.
• Then, click on the Format Painter tool from the ribbon under the Clipboard group.

• Now, go to another sheet where you want to copy the format. I am going to 3. Copy in Another Sheet.

• Now, left-click on the top cell where you want to paste the format and drag it down to the bottom cell. Here I have left-clicked on C6 and dragged it down to The result is below.

• Like the previous examples, we can check the formula of this conditional formatting to verify that we have properly copied the formatting.

Things to Remember

• Be careful to write the conditional formula with proper relative reference. Otherwise, after copying the format, the results may be inaccurate.

Conclusion

That is the end of this article. Hopefully, now you have a clear idea of how to copy conditional formatting with relative cell references in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.