Copying Conditional Formatting

In Excel, conditional formatting is considered part of the regular formatting of a cell. If you want to copy conditional formatting from one cell to another, you can do so by simply copying the cell and pasting it (or its format) to another cell. If you want to copy a conditional format to a range of cells (and only the conditional format), the easiest way to do so is by following these general steps:

1. Select the cell that contains the conditional format you want copied.
2. Display the Home tab of the ribbon.
3. In the Styles section, click Conditional Formatting. Excel displays various options related to conditional formatting.
4. Click Manage Rules. Excel displays the Conditional Formatting Rules Manager. The format you want copied should already be filled in. (See Figure 1.)



Figure 1. The Conditional Formatting Rules Manger.

5. Select the conditional formatting rule you want copied.
6. Click the Duplicate Rule button. The selected conditional formatting rule now appears twice in the Rules Manager.
7. Within the duplicated rule, select whatever appears in the Applies To box.
8. Press the Delete key. The Applies To box should now be empty and the insertion point still within it. (See Figure 2.)



Figure 2. Ready for a new range.

9. Using the mouse, click and drag to select the range of cells to which the conditional format should be applied.
10. Click the OK button to dismiss the Rules Manager.

That's it. Excel does the rest and copies the conditional formatting, as you desired. Technically, the conditional formatting rule wasn't copied, but moved. When you click OK (step 10), the duplicated step is moved to the target range you specified (step 9) and removed from the original cell selected (step 1).

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6253) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Copying Conditional Formatting.