How to Copy Conditional Formatting to Another Workbook in Excel

Get FREE Advanced Excel Exercises with Solutions!

Conditional Formatting is one of the most used Excel tools. Conditional Formatting allows us to format cells according to our criteria. In this article, we will see several ways to Excel Copy Conditional Formatting to Another Workbook. We have a sample dataset containing Name, Gender, Occupation, and Salary.

Copy Conditional Formatting


In our sample data Gender and Salary columns are conditional formatted. Here, Females are highlighted and Salary above $30000 is highlighted. We will see how to copy this formatting to another workbook, where we have another dataset That looks like the following image.

We will see 3 easy methods to copy this Conditional Formatting in another workbook.


1. Using Format Painter to Copy Conditional Formatting to Another Excel Workbook

Here, we will see use of Format Painter in Excel.

Steps:

  • First, select the entire data set or the specific column or rows or cells where lies the formatting that you want to copy. Then, click the Format Painter.

Copy Conditional Formatting using format painter

  • After that, go to the Workbook where you want to apply this Conditional Formatting, and all you need to do is drag down to select a range. The formation will be copied.

  • Now, our dataset will look like the following image.

As you can see, the formatting is exactly what we wanted.

Read More: How to Copy Conditional Formatting to Another Sheet


2. Copying Conditional Formatting to Another Workbook by Paste Special Tool

In our second method, we will discuss the use of the Paste Special option to copy Conditional Formatting in Excel.

Steps:

  • First, select the specific range or cell where our conditional formatting lies. Then Press CTRL+C or copy using the right click of the mouse.

Copy Conditional Formatting paste special

  • Now, go to the worksheet or workbook where our new dataset is and select the entire range in the dataset, and right-click the mouse button.

Copy Conditional Formatting paste formats

  • Form here, click on Paste Special as shown in the above image and a dialog box will pop up.

  • Just select Formats as shown in the above image and click OK.

All the cells are formatted accordingly.

Read More: How to Remove Conditional Formatting but Keep the Format in Excel


3. Applying Excel VBA to Copy Conditional Formatting to Another Workbook

At the end of this article, we will see the use of VBA code to copy conditional formatting from one workbook to another. Keep in mind to open both the workbook while applying this method.

Steps:

  • First, right-click on the sheet and go to View Code.

Copy Conditional Formatting vba

  • After that, copy and paste the VBA code below.

VBA code:

Sub mycopycode()

Workbooks("Copy Conditional Formatting").Worksheets("Sample Data").Activate
Range("C5:C11", "E5:E11").Select
Selection.Copy

Workbooks("Book1").Worksheets("Sheet3").Activate
Range("E4:E10", "G4:G10").Select
Range("E4:E10", "G4:G10").PasteSpecial xlPasteFormats

End Sub

Copy Conditional Formatting macros

  • After that, press the F5 or play button to run the code.

That’s it. Our VBA has copied the format to the new workbook.

Read More: VBA Conditional Formatting Based on Another Cell Value in Excel


Things to Remember

We have to keep a couple of things in mind while doing these methods.

  1. We have to check the Formula in conditional Formatting, whether it is Relative reference or Absolute reference. In case of referencing you may need to change the formula according to your cell after applying Paste Special of Format Painter.
  2. Always keep open the workbooks while copying from one workbook to another.

Download Practice Workbook


Conclusion

These are 3 different methods to Copy Conditional Formatting to Another Workbook in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback


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.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo