Apply Conditional Formatting – Multiple Sheets in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on August 1, 2023

This tutorial demonstrates how to apply conditional formatting to multiple sheets in Excel and Google Sheets.

condformatting multiple format

 

In the latest versions of Excel, you can no longer select multiple sheets and then apply a conditional formatting rule to all the sheets at once. You need to do one sheet first, then copy the conditional formatting between sheets. Or, you can write a macro to repeat the conditional formatting on each sheet.

Apply Conditional Formatting to Multiple Sheets

Copy-Paste

  1. Select the sheet with the conditional formatting applied and right-click the cell with conditional formatting rule. Then click Copy (or use the keyboard shortcut CTRL + C).

 

condformatting copysheet one

 

  1. Next, click on the destination sheet and select the destination cell or cells. Right-click and choose Paste Special > Paste Format.

 

cond formatting paste sheet two

 

Format Painter

You can also copy the conditional formatting rule from one sheet to another with the Format Painter feature.

  1. Select the cell in the source sheet that has a conditional formatting rule and then, in the Ribbon, select Home > Clipboard > Format Painter.

 

condformating format painter select cell

 

  1. Click on the destination sheet and click on the cells or cell where you wish to apply the format.

 

condformatting format painter

 

Create a Macro to Apply Conditional Formatting Rule

While creating a new conditional formatting rule in Excel, you can record a macro to mimic the steps you take.

  1. In the Ribbon, select View > Macros > Record Macro.

 

cond formatting ribbon record macro

 

OR, in the Ribbon, select Developer > Code > Record Macro.

 

cond formatting ribbon dev tab

 

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.

  1. In the Record Macro dialog box, (1) type in a name for your macro and (2) make sure you select Personal Macro Workbook from the drop-down list. Then, (3) click OK to start recording.

 

condformatting name macro

 

  1. Once you have clicked OK, you can follow the steps to create the conditional formatting rule you require, and then click the stop button at the bottom of the screen to stop recording the macro.

 

VBAProject StopButton

 

  1. To run the macro on a different sheet, switch to that sheet and select the cell or cells you wish to apply the conditional formatting to.
  1. In the Ribbon, select View > Macros > View Macros.

 

VBAProject ViewMacros

 

OR Developer > Visual Basic > Macros

  1. Click on the Macro in the Macro name list and then select Run.

 

condformatting run

 

  1. You can run the macro on as many sheets as you require.

Note: You can view the code by selecting the macro in the Macro dialog box (shown above) and selecting Edit. This takes you to the Visual Basic Editor and enables you to view and/or edit your VBA code.

Apply Conditional Formatting to Multiple Sheets in Google Sheets

You can apply conditional formatting to multiple sheets in Google Sheets in the same way as you do in Excel.

Copy Conditional Formatting From One Cell

Copy-Paste

If you already have a cell or column with a Conditional Formatting rule set up, you can use Copy-Paste to copy the rule to another sheet.

  1. Right-click on a cell that has the conditional formatting rule applied to it and click Copy (or use the keyboard shortcut CTRL + C).

 

cond formatting gs copy

 

  1. Select the sheet you require and then select the cell or cells in that sheet where you wish to apply the conditional formatting rule. Select Paste special > Format only (or use the keyboard shortcut CTRL + ALT + V).

 

condformatting gs paste special

 

The conditional formatting rule will then be applied to the entire column you have selected.

Paint Format

If you already have a cell or column with a conditional formatting rule set up, you can use Paint Format to copy the rule to another column.

  1. Select the cell with the relevant conditional formatting rule. Then in the Menu, select Paint Format.

 

condformatting gs format paint copy

 

  1. Select the destination sheet, then highlight the cells to copy the conditional formatting rule to.

 

condformatting gs format paint paste

AI Formula Generator

Try for Free

See all How-To Articles