How to apply conditional formatting to multiple sheets in Microsoft Excel

Conditional formatting in Excel is a powerful tool that allows users to format cells based on specific criteria or conditions. This feature can highlight data that meet certain thresholds, such as values above or below a certain number, or distinguish data based on specific criteria visually. Conditional formatting can help users quickly identify trends, outliers, or other patterns in their data.

In this tutorial, we will learn how to apply conditional formatting to multiple sheets in Microsoft Excel. In Excel, we can apply Conditional formatting to multiple sheets in Excel using the Format Painter feature or we can also use the Paste Special option by selecting multiple sheets.

In the current data set, we have applied conditional formatting on some cells in sheet4. We want to apply the same format onn other sheets.

Method 1: Using the Format Painter

Step 1 – Select the Range or the Cell on which Conditional Formatting is Applied

• Select the range or the cell on which conditional formatting is applied.

Step 2 – Click on the Format Painter Icon 

• Click on the Format Painter icon in the Clipboard section of the Home tab.
• The cursor will convert into a white plus sign.

Step 3 – Select the Range or Cell on which the Format is to be Applied

• Select the range or the cell in the other sheet on which the format is to be applied.
• The same conditional formatting will be applied to the selected range.

Method 2: Using the Paste Special Option

Step 1 – Copy any of the Cell on which Conditional Formatting is Applied

• Copy any of the cells on which the conditional formatting is already applied.

Step 2 – Select the Range of Cells

• Select all the cells on which you want to apply the conditional formatting.

Step 3 – Right-Click on the Selected Range

• Right-click anywhere on the selected range.
• A context menu will appear.

Step 4 – Click on the Paste Special Option

• Click on the Paste Special option in the context menu.
• The Paste Special dialog box will appear.

Step 5 – Select the Format Option and Click on OK 

• Select the “Format” option in the Paste section.
• Click on OK in the Paste Special dialog box.
• Conditional formatting will be applied to the selected range.