Copy Conditional Formatting in Excel

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 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 formatting to other cells.

Practice Workbook

About Practice Workbook

The practice workbook contains 3 sheets. In the first practice worksheet, there are marks obtained by students on their Chemistry and Biology exams. The Chemistry Marks 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.

About Workbook

We will learn how to copy the same formatting from the Chemistry Marks column to the Biology Marks 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 Marks column formatting to the Math Marks column in the third sheet).

Easiest Ways to Copy Conditional Formatting in Excel

1. Copying Conditional Formatting with Numbers

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

i. Using Paste – Special

a. Using Ribbon

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

  • 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 or CTRL+C.

 Copy Cell with Formatting in Ribbon

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

 Paste Spacial in Ribbon

  • The selected cells will be formatted with conditions.

 Copy Format

b. Using Right-Click Menu

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

Copy Cell with Formatting with Right Click

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

Paste Spacial Arrow with Right Clic

  • Or you can select Paste Special only.

Copy Format with Right Click

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

Paste Special Dialogue Box

  • We have done it.

Copy Format with Right Click

ii. Using Format Painter

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

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

Format Painter Selection

  • Now select the cells which you want to format.

Format Painter in Action

  • Look! How easily done!

Copy Format - done


Similar Readings:


2. Copying 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 Score and Biology Score. 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.

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

Conditional Formatting with text

  • 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. Now click Format.

Formula Creation

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

Select Format Color

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

Confirmation

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

Conditioned

  • Now select a cell from this Remark column and click Format Painter. Then copy it to the cells of the second Remark column.

Format Painter Text

  • The conditional formatting has been copied.

Copy Formatting Text Done

3. Copying 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 be used to copy 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 Marks column of the Conditional Formatting worksheet and paste it to the Math Score column of the Copy Formatting to New Sheet worksheet. Let’s see how.

Let’s see the third worksheet first

Math Score No formatting

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

 Copying Formatting from Another Sheet

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

Formetting in New Sheet

  • This cell is also formatted!

Done Formatting in New Sheet

Conclusion

Hope you find this article helpful. 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. Your help will make us even better, Thank you.


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

ExcelDemy
Logo