Introduction

If you use conditional formatting in Microsoft Excel to format cells that meet criteria, you may wish to apply the same rule to another portion of your sheet or file. Instead of establishing a new rule, just duplicate it.

You may have a date-based conditional formatting rule that you wish to utilize for other dates. Alternatively, you may have a rule for spotting duplicates that you need to apply to another sheet. We’ll show you three different methods to copy conditional formatting in Excel and paste it onto another spreadsheet or page in the same file.

Format Painter Copy Formatting

Format Painter is a useful Office tool that allows you to copy formatting from one portion of your document to another. You may use it to copy a conditional formatting rule to other cells.

Choose the cells that contain the conditional formatting rule.

Navigate to the Home tab and then to the Clipboard area of the ribbon, where you will find the Format Painter button.

Your cursor will change to a plus symbol with a paint brush. Select the cells to which you wish to apply the same rule, ensuring that you drag across neighboring cells.

Paste Special Copy Formatting

Excel’s Paste Special options may do more than just let you add and multiply numbers. Conditional formatting may also be applied using the formatting paste function.

Choose the cells that contain the conditional formatting rule. Then use one of the following ways to duplicate them:

• Select “Copy” from the right-click menu.
• On the Home tab, click the Copy button in the Clipboard portion of the ribbon.
• Use the Windows keyboard shortcut Ctrl+C or the Mac keyboard equivalent Command+C.

Drag across the cells to choose the ones to which you wish to apply the rule. Then use the Paste Special action to format using one of the options below.

• Right-click, choose Paste Special > Other Paste Special Options, and then select “Formatting.”
• In the Clipboard area of the Home tab, click the Paste drop-down arrow. Select “Formatting” from the list of Other Paste Special Options.
• On the Home tab, choose Paste Special from the Paste drop-down arrow. In the dialog box, check the Formats box and click “OK.”

The formatting will subsequently be applied to the cells you’ve chosen. By accessing the Conditional Formatting Rules Manager, you can validate that the rule was copied and not simply the formatting.

Using the Conditional Formatting Rules Manager for Copy Formatting

The Conditional Formatting Rules Manager allows you to keep track of the rules you’ve created in your sheet or workbook. It may also assist you in copying formatting by creating a duplicate rule and then slightly altering it to suit additional cells.

Navigate to the Home tab and choose the Conditional Formatting drop-down arrow. Choose “Manage Rules.”

When the Conditional Formatting Rules Manager appears, pick “This Worksheet” from the top drop-down box. If the rule to be duplicated is on a separate sheet, you may choose it from the drop-down list instead.

Then, at the bottom, choose the rule you wish to duplicate and click “Duplicate Rule.”

The regulation will be reproduced exactly. Then, in the Applies To box on the right, modify the cell range for the new cells, or pick the sheet and the cells with your pointer to fill the box. You may also make additional changes to the format if you like.

To apply the rule to the new cells, click “Apply,” and then “Close” to leave the Rules Manager window.

Conditional formatting is an excellent approach to highlight certain data in your spreadsheet. So, if you want to apply the same rule to additional cells or sheets, remember to save time by just copying it!