Join 425,000 subscribers and get a daily digest of news, geek trivia, and our feature articles.
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…
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.
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.
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.
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.
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:
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.
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.
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.”
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.
Then, select the rule you want to copy at the bottom and click “Duplicate 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.
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!
The Best Free Tech Newsletter Anywhere