How To Use Google Sheets Copy Conditional Formatting [3 Easy Methods]

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 sheets. Suppose 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 get highlighted in red.

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

Both methods allow for Google Sheets copy conditional formatting — Paste Special and 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 Ctrl + C for Windows or Cmd + 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 the “Paste format only” option
   • Alternatively, use the keyboard shortcut Ctrl + Alt + V for Windows or Cmd + Option + V for Mac
     

     

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

If you want to copy the formatting to multiple ranges, you can repeat steps 3 to 5 without formatting the cells again.

Also, these same steps 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 extends the formatting to include this new range in the current formatting rule (as shown below).

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

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

Google Sheets Copy Conditional Formatting Using the Paint Format Tool

Like the Paste Special method, you can also use the Paint Format method.

Paint Format (or 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 the 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 “Paint Format” in the toolbar to activate the tool
   

   

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

   

You can also use the same steps 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 must select the cells that have the formatting and activate the paint format tool again.

While it’s easy to copy formatting from one cell or 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.

How To Duplicate the Conditional Formatting Rules

Duplicating is as simple as copying and pasting the conditional formatting rules. 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 Files

Since you can copy formatting from one sheet to another (but not onto a different Google Sheet file), here is a simple way to use Google Sheets’ copy conditional formatting in 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 the 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”

And 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 outside of 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 formula of:

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

Simply follow these steps:

1. Go to “Edit” > “Copy,” and select the range of cells in column E
2. Then, 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 have copied.

If the two cell ranges are not connected, and there is a gap, 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, that won’t change when you copy the conditional formatting rules.

The above steps will copy the sheet to the other Google Sheets file.

Conclusion

Now you know how to use the Google Sheets copy conditional formatting tool to move data from one cell to another and into 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!

If you want to upskill your Google Sheets knowledge, then check out this Google Sheets masterclass! It covers useful hacks on hidden features, how to use advanced functions, and more!

Related:

• • 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