Step-by-Step Guide: Replicating Conditional Formatting Rules in Excel

Quick Links

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

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

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.

Copy Formatting Using Paste Special

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:

• Right-click and select “Copy.”
• Click the Copy button in the Clipboard section of the ribbon on the Home tab.
• Use the keyboard shortcut Ctrl+C on Windows or Command+C on Mac.

FX PRO (Gold Robot + Silver Robot(Basic Package))

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.

• Right-click and move to Paste Special > Other Paste Special Options and pick “Formatting.”
• Click the Paste drop-down arrow in the Clipboard section on the Home tab. Move down to Other Paste Special Options and pick “Formatting.”
• Click the Paste drop-down arrow on the Home tab and pick Paste Special. Mark the option for Formats in the dialog box and click “OK.”

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.

Copy Formatting Using 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.”

Click here

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!