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.

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.

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.

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

• Look! How easily done!

Similar Readings

• How to Do Conditional Formatting with Multiple Criteria
• Excel Conditional Formatting on Multiple Columns
• Conditional Formatting Based On Another Cell in Excel (6 Methods)
• Excel Conditional Formatting with Formula If Cell Contains Text

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.

• 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

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

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

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.

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

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

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

• Conditional Formatting with Formula for Multiple Conditions in Excel
• Excel Conditional Formatting Formula with IF
• How to Highlight Lowest Value in Excel (11 Ways)
• Change a Row Color Based on a Text Value in a Cell in Excel