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

Conditional Formatting in Excel enables you to quickly format a cell (or range of cells) based on the value or the text in it. It can help you to identify your desired data that follow the conditions required by you. Sometimes it is necessary to use the same conditional formatting across multiple fields. We will learn the easiest ways to copy the conditional formatting to other cells in Excel.


Download Practice Workbook


4 Easiest Ways to Copy Conditional Formatting in Excel

The practice workbook contains 7 sheets. In the first practice worksheet, there are marks obtained by students on their Chemistry and Biology exams. The Chemistry column has been formatted to show the top five marks obtained by highlighted cells and a red font is used to identify marks that are 80 and above.

Dataset for Copying Conditional Formatting

We will learn how to copy the same formatting from the Chemistry column to the Biology column. We will also learn how to copy Conditional Formats containing a text (in our workbook, the word is “Great“) in the second sheet. Finally, we will see how we can copy formatting between different worksheets (in this workbook, we will copy the Chemistry column formatting to the Math column in the fifth sheet).


1. Copy Conditional Formatting with Numbers

We practiced this in the Conditional Formatting Sheet of the Practice Workbook.

i. Using Paste-Special

a. Using Ribbon Feature

In this instance, we will only use the Ribbon of the Home tab to do basic functions like Copy-Paste.

📌 Steps:

  • Select any formatted cell (To know how to use Conditional Format) that you want to copy the formatting of. Now, from the Ribbon select Copy from the Clipboard group.

Copy Conditional Formatting using ribbon

You can also copy by using the keyboard shortcut Crtl+C.

  • Select the cells you want to format. From the Ribbon click the down indicator of Paste and select the Formatting icon.

  • The selected cells will be formatted with conditions.


b. Using Context Menu

  • Select a cell containing formatting and from the right-click menu select Copy from the Context Menu.

  • Now select the cells and right-click and click the arrow beside Paste Special and select the Formatting icon.

Copy Conditional Formatting from the context menu

  • Look at the dataset.

Conditional formatting has been copied to newly applied cells. There is an alternative option of the Context Menu is shown in the below section.

  • Or you can select Paste Special only.

  • This will open a new dialogue box of Paste Special. Here select Format and then OK.

  • We have done it.


ii. Using Format Painter

This process is quite similar to the previous but it is easier and more multifunctional. Format Painter is a tool to copy formatting from one cell to another cell. You can also copy conditional formatting using this tool.

📌 Steps:

  • Select the cell whose formatting you want to copy. From the Ribbon select Format Painter of Clipboard group.

  • Now select the cells which you want to format.

Format painter to Copy Conditional Formatting

  • Look! How easily done!


Similar Readings


2. Copy Conditional Formatting with Text

All the previous operations were done using numbers. Let’s see how to copy conditional formatting in cells containing text, Let’s make a new worksheet with the 1st worksheet data named Conditional Formatting Text. We added two new columns named Remark beside Chemistry and Biology. Here the teacher added “Great” remarks to students who got 80+ numbers. We will first add a condition to format “Great” remarks and then copy it to the next Remark column.

📌 Steps:

  • First, we apply the following formula to get Great for marks greater than 80.
=IF(C5>80,"Great","") 

  • Select the column without the header (in our case, we selected underneath the 1st Remark column). Then from the Ribbon select Conditional Formatting and from the drop down menu select New Rule.

  • A dialogue box New Formatting Rule will appear. From there select Use a formula to determine which cells to format.
  • In the Format the values where this formula is true box white the formula
=IF(D5="Great",True,False) 
  • This will check for cells that contain “Great“. If the argument returns true it will format the cell and if false then no change will happen.
  • After that, click the Format button.

  • In the Format Cells window, you can find many formatting options. Pick the one that suits you. You can also learn about it here. Now click OK.

  • Now that everything is fixed and changes are viewed click OK.

Condition copied in Conditional Formatting

  • The first Remark column has been conditioned and cells containing remarks “Great” have been identified with green cells.

  • Now select a cell from this Remark column and click Format Painter. Then copy it to the cells of the second Remark column.
  • The conditional formatting has been copied.

Copy Conditional Formatting for text data


3. Copy Conditional Formatting to Different Sheets

Format Painter tool can copy the conditional formatting to different worksheets and workbooks. In this section, we will see how this tool can copy the conditional formatting to a different worksheet.

For that, we added the third worksheet Copy Formatting to New Sheet in our workbook. We will copy the formatting of the Chemistry column of the Conditional Formatting worksheet and paste it to the Math column of the Copy Formatting to New Sheet worksheet. Let’s see how.

📌 Steps:

  • Let’s see this worksheet first.

  • From the Conditional Formatting worksheet, select any cell under the Chemistry column. Then click the Format Painter option.

  • Now head on to Copy Formatting to New Sheet Worksheet and select the cells underneath the Math column.

Copy Conditional Formatting to another sheet

  • This cell is also formatted!


4. Copy Conditional Formatting with Relative Cell References

In this section, we will show how to copy conditional formatting with relative cell references.

📌 Steps:

  • First, we apply conditional formatting with a cell reference for highlighting cells of the Chemistry column based on Cell F5. Cells greater than F5 will highlight.
  • We put the following formula in this conditional formatting.
=C5>=$F$5 

Relative cell reference of Copy Conditional Formatting

  • Now, copy conditional formatting using Format Painter in the Biology column.

  • Now, click on the Manage Rules option from the Conditional Formatting field.

  • The Conditional Formatting Rules Manager window appears.

Copy Conditional Formatting with relative cells

We can see the relative cell reference has been copied by copying conditional formatting in Excel.


Conclusion

Hope you find this article helpful to copy the conditional formatting to other cells in Excel. Please take time to rate this article. If you have any questions regarding this article you can ask me in the comment section. You can also give us suggestions on how to improve this article. For more articles please our website ExcelDemy.


Further Readings

Shahin Mahmud

Shahin Mahmud

Hello I am Shahin Mahmud. I love working with Excel. I am a Civil Engineer holding a Bachelor’s degree from Bangladesh University of Engineering and Technology. I am interested in anything related to computers and phones. I also like to help people facing computer related problems and write articles about them.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo