Format Painter in Google Sheets: Copy Conditional Formatting

Watch Video – How to Copy Conditional Formatting using Format Painter

Conditional Formatting is an amazing feature that allows us to format cells (highlight cells) based on the value in the cell.

For example, if you have the scores of all the students in a column, you can use conditional formatting to quickly highlight all the scores that are less than 35. This saves time and also makes the data easy to read and more meaningful.

It’s easy to apply conditional formatting to a range of cells, and it’s even easier to copy conditional formatting in Google Sheets.

In this tutorial, I will show you Google Sheets copy conditional formatting from one cell to another in the same sheet, in different sheets in the same document, and in separate Google Sheets documents.

Let’s get to it!

Copy Conditional Formatting in the Same Sheet (or different sheets)

You can also copy conditional formatting across sheetsSuppose you have a dataset as shown below where I have applied conditional formatting to column B (which has the Physics score for the students) so that all the cells with a score less than 35 gets highlighted in red.

And now, I want to apply the same formatting to Column B, which has the Math score.

The two methods let Google Sheet copy conditional formatting – Paste Special and the Paint Format.

Let’s have a look at both of these methods.

Using Paste Special

Below are the steps to copy conditional formatting from one cell to another in Google Sheets using Paste Special:

1. Select the cell or range of cells from which you want to copy the formatting
2. Right-click and then click on Copy (or use the keyboard shortcut Control + C or Command + C for Mac)
3. Select the range of cells where you want to copy the copied conditional formatting
4. Right-click on any of the selected cells
5. Go to Paste Special, and then in the options that appear, click on ‘Paste format only’ option (or use the keyboard shortcut CONTROL + ALT + V or Command + Option + V for Mac)
   

   

The above steps would instantly copy and paste the formatting from column B to column C.

In case you want to copy the formatting to multiple ranges, you can repeat the steps 3 to 5. You don’t need to copy the cells that have the formatting again.

Also, these same steps would also work when you want to copy the conditional formatting to cells in another sheet in the same document.

When you copy conditional formatting from one cell to another in the same sheet, it doesn’t create a new rule for the cells where it’s pasted. Instead, Google Sheets simply extends the formatting to include this new range into the current formatting rule (as shown below).

On the contrary, when you copy conditional formatting from a cell to another one in a different sheet, a new rule will be created for the second sheet.

One thing to know when copying conditional formatting using Paste Special method is that it will copy any other formatting as well. For example, if you have given a border to the cell or changed the font size or made it bold/italics, even these changes would also be copied to the destination cells/ranges.

Google Sheets Copy Conditional Formatting Using Paint Format Tool

Just like the paste special method, you can also use the Paint format method.

Paint format (called format painter in Excel) is a simple tool that copies the format from one cell and can be pasted to other cells or a range of cells.

Below are the steps to use Paint format tool to copy conditional formatting in Google Sheets:

1. Select the cell (or range of cells) from which you want to copy the conditional formatting
2. With the cells selected, click on the Paint format tool in the toolbar. This will activate the Paint format tool
   

   

3. Use the mouse to select the cell where you want to paste the formatting
   

   

You can also use the same steps when you want to copy the conditional formatting to cells in a different worksheet in the same Google Sheets document.

Unlike Excel, you can not use the Paint format tool multiple times. When you activate it, you can only copy formatting on the selected range of cells once. If you want to do it for multiple ranges, you will have to select the cells that have the formatting and activate the paint format tool again.

While it’s quite easy to copy formatting from one cell/range to another in the same Google Sheets file, the same won’t work when you want to copy the formatting to a different Google Sheets document.

That’s how to apply conditional formatting to multiple sheets in Google Sheets.

How to Duplicate the Conditional Formatting Rules

Duplicating is as simple as copy and paste conditional formatting rules. In order to duplicate the format rule, you need to:

1. Select the cell that has the conditional formatting rules you want to duplicate
2. In the menu, go to Edit > Copy
3. Select the cells where you want the formatting rules to copy to
4. Go to Edit >Paste Special > Conditional formatting only

This will paste the formatting rules to the cells you have selected. Read more on how to highlight duplicates in Google Sheets.

Copy Conditional Formatting in different Google Sheets file

Since you can easily copy formatting from one sheet to another (but not on to a different Google Sheet file), here is a simple to how to copy conditional formatting to another sheet:

Move a copy of the sheet (that has the conditional formatting that you want to copy) to the other Google Sheet file and then use any of the above methods to copy formatting to other sheets.

Below are the steps to create a copy of a worksheet in a different Google Sheets file:

1. Right-click on the sheet tab that has the formatting that you want to copy
2. Hover the cursor over ‘Copy to’
3. Click on ‘Existing spreadsheet’
   

   

4. In the dialog box that opens, navigate and select the file to which you want to copy this sheet (or if you have it open, you can also copy and paste the URL of the file)
5. Click on Select

That’s how to copy conditional formatting from one sheet to another

Does the Formula Range Automatically Update the Range When Copied?

Usually, when you copy a conditional format rule to a range of cells that is outside the range in the formatting formula, The range is added to the end of the formula.

For example, in our sheet below, we have the custom if formula =ISEVEN(D2)

If we wanted to copy the conditional format rule to column E then we would Select the cell that has the conditional formatting rules, go to Edit > Copy, the select the range of cells in column E and go to Edit > Paste Special > Conditional formatting only.

If you go to the rules in the conditional format menu, you will see that the range has changed from D1:D11 to D1: E11. The conditional format rule has updated the cell range to include the new cell range you copied.

If the two cell ranges are not connected, and there is a gap between them, then the range will show the original cell range, a comma and the new cell range.

However, if there is a range within the custom formula, then that won’t change when you copy the conditional formatting rules.

The above steps would copy the sheet to the other Google Sheets file. Let’s look at Google Sheets how to copy conditional formatting.

Conclusion

Now you can see how to copy conditional formatting Google Sheets from a cell to other cells in other sheets using paste special or Paint format. If you’re ever stuck trying to copy formatting in Google Sheets, please refer to this article.

I hope you found this Google Sheets copy conditional formatting tutorial useful!

Other Google Sheets tutorials you may find useful:

• • How to Clear Formatting in Google Sheets (Step-by-Step)
  • Apply Conditional Formatting based on Another Cell Value in Google Sheets
  • How to Merge Cells In Google Sheets
  • Formula to Highlight Duplicates in Google Sheet