How to Copy Conditional Formatting with Relative Cell References in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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 Format Painter to work faster. 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.

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

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

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

  • 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.

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

  • 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…

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

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

Read More: How to Copy Conditional Formatting to Another Cell in Excel


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 Paste Special command 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.

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

  • 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.

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

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

Copying Conditional Formatting in Excel Using Paste Special Feature

  • 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.

Read More: How to Copy Conditional Formatting Color to Another Cell in Excel


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.

How to Copy Conditional Formatting to Another Sheet in Excel

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

How to Copy Conditional Formatting to Another Sheet in Excel

  • 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.

How to Copy Conditional Formatting to Another Sheet in Excel

  • 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.

Download Practice Workbook

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


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.


Related Articles

<< Go Back to Copy Conditional Formatting | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo