How to Copy Conditional Formatting in Microsoft Excel – How-To Geek

Join 425,000 subscribers and get a daily digest of news, geek trivia, and our feature articles.
By submitting your email, you agree to the Terms of Use and Privacy Policy.
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more…
Microsoft Excel logo on a green background
If you use conditional formatting in Microsoft Excel to automatically format cells that match criteria, you may want to apply the same rule to another part of your sheet or workbook. Instead of creating a new rule, just copy it.
You might have a conditional formatting rule based on date that you want to use for other dates. Or, you may have a rule for finding duplicates and need to use it on another sheet. We’ll show you three ways to copy conditional formatting in Excel and use it elsewhere in your spreadsheet or in another sheet in the same workbook.

Copy Formatting Using Format Painter
Copy Formatting Using Paste Special
Copy Formatting Using the Conditional Formatting Rules Manager

Format Painter is a helpful Office tool that lets you copy formatting to other parts of your document. With it, you can copy a conditional formatting rule to other cells.
RELATED: Copy Excel Formatting the Easy Way with Format Painter
Select the cells containing the conditional formatting rule. Go to the Home tab and click the Format Painter button in the Clipboard section of the ribbon.
Format Painter on the Home tab
You’ll see your cursor change to a plus sign with a paint brush. Select the cells you want to apply the same rule to, making sure to drag through adjacent cells.
Using the Painter to copy the formatting rule
Now you’ve copied the conditional formatting rule, not just the formatting. You can confirm this by viewing the Conditional Formatting Rules Manager using Home > Conditional Formatting > Manage Rules.
Confirm the rule in the Manager
The Paste Special options in Excel can do more than help you add or multiply values. You can use the formatting paste action to apply conditional formatting too.
RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel
Select the cells containing the conditional formatting rule. Then copy them using one of these methods:
Copy the formatted cells
Select the cells that you want to apply the rule to by dragging through them. Then use the Paste Special action for formatting with one of the following.
Paste Special Formatting option
You’ll then see the formatting apply to your selected cells. Again, you can confirm that the rule copied and not just the formatting by viewing the Conditional Formatting Rules Manager.
Confirm the rule in the Manager
The Conditional Formatting Rules Manager helps you keep track of rules you’ve set up in your sheet or workbook. It can also help you copy formatting by making a duplicate rule and then editing it slightly to fit other cells.
RELATED: How to Manage Conditional Formatting Rules in Microsoft Excel
Open the tool by going to the Home tab and clicking the Conditional Formatting drop-down arrow. Select “Manage Rules.”
Manage Rules in the Conditional Formatting drop-down list
When the Conditional Formatting Rules Manager opens, select “This Worksheet” in the drop-down box at the top. If the rule you want to duplicate is on a different sheet, you can select it from the drop-down list instead.
Select a sheet
Then, select the rule you want to copy at the bottom and click “Duplicate Rule.”
Duplicate the rule
An exact copy of the rule will appear. You can then change the cell range in the Applies To box on the right for the new cells or select the sheet and the cells with your cursor to populate the box. You can also make other adjustments to the format if you like.
Changed cell range for the duplicated rule
Click “Apply” to apply the rule to the new cells and “Close” to exit the Rules Manager window.
Conditional formatting gives you a great way to make certain data in your sheet stand out. So if you decide to apply the same rule to other cells or sheets, remember to save some time and just copy it!
Facebook
Instagram
Twitter
LinkedIn
RSS Feed
The Best Free Tech Newsletter Anywhere
By submitting your email, you agree to the Terms of Use and Privacy Policy.

source