Home Work How to Copy Microsoft Excel’s Conditional Formatting

How to Copy Microsoft Excel’s Conditional Formatting

by Kevin

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.

Format Painter on the Home tab

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.

Using the Painter to copy the formatting rule

You’ve now cloned the conditional formatting rule as well as the formatting. You may check this by going to Home > Conditional Formatting > Manage Rules and accessing the Conditional Formatting Rules Manager.

Confirm the rule in the Manager

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.

Copy the formatted cells

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.”

Paste Special Formatting option

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.

Confirm the rule in the Manager

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.”

Manage Rules in the Conditional Formatting drop-down list

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.

Select a sheet

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

Duplicate the 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.

Changed cell range for the duplicated rule

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!

You may also like

Navhow is dedicated to teaching people all over the world how to do anything.

 

Subscribe

The Best How-To Newsletter Anywhere

©2022 Navhow, All Right Reserved