How to Copy Conditional Formatting from One Sheet to Another in Google Sheets

We are pretty familiar with the process of applying Conditional Formatting in the same Google Sheets. But to copy Conditional Formatting from one sheet to another Google Sheets may seem unusual. Here in the following article, we’ll talk about 5 fast methods to copy Conditional Formatting from one sheet to another Google sheets with clear steps and images.


A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.


5 Simple Methods to Copy Conditional Formatting from One Sheet to Another in Google Sheets

Let’s get introduced to our dataset first. Here we have some Names in Column B and their respective Math scores in Column C. Moreover, we have Conditional Formatting in Column C from Cell C5 to C11 such that if any value from Column C is greater than 60 then the value would be highlighted as green color. We have named the sheet Dataset. Now we’ll see 4 easy methods to copy Conditional Formatting from one sheet to another Google Sheets.

How to Copy Conditional Formatting from One Sheet to Another in Google Sheets


1. Using Copy and Paste Command

In the beginning, we can use the simple Copy and Paste Command to copy Conditional Formatting from one sheet to another Google Sheets. This process directly copies and pastes the original format and values together into another sheet.

Steps:

  • Firstly, copy Cells C5 to C11 in Dataset by pressing Ctrl+C together.

Using Copy and Paste Command to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • Finally, select Cell D5 in the new sheet and paste it by pressing Ctrl+V together. You’ll get exactly the same values and formatting as the previous sheet.


2. Applying Copy to Command

We can also use the Copy to Command to get results like the 1st method. This process is a bit longer than the former process. Basically, it will copy the whole sheet, so the conditional formatting will be copied too. The only advantage of this method is that we can copy and paste the Conditional Formatting to a new workbook also. For applying this method we have given a name to our current sheet. The name is Copy to Command.

Steps:

  • First, Right-Click on the sheet name Copy to Command.
  • Next, go to Copy to > Existing Spreadsheet.

Applying Copy to Command to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • After, select our current spreadsheet as shown in the picture and click the Select Button.

Applying Copy to Command to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • Then, you’ll get a message that the sheet was copied successfully. Lastly, click the OK Button.

  • At last, you’ll get a new sheet named Copy of Copy to Command which will have the past Conditional Formatting.


3. Using Paste Special Command

Now we have a different set of data. We have names in Column B and their English Score in Column C unlike the math scores in the preceding dataset. We want to apply the same condition here also. So we have to copy the previous Conditional Formatting from our former Dataset and paste it here. We can do this simply by the Paste Special Command.

Using Paste Special Command to Copy Conditional Formatting from One Sheet to Another Google Sheets


3.1. Format Only Command

The Paste Special Command has 2 options. The first one is Format Only Command. We’ll apply it here.

Steps:

  • In the beginning, copy the Cells from C5 to C11 of the source Dataset by following the 1st step of 1st Method.
  • Afterward, select the Cells from C5 to C11 in the current sheet.
  • Then Right-click on the mouse and go to Paste Special > Format only.

  • In the end, you’ll get the desired result.


3.2. Conditional Formatting Only Command

The second command under Paste Special Command is the Conditional Formatting Only Command. Let’s see how to apply it.

Steps:

  • Foremost, copy the Cells from C5 to C11 of the source Dataset like the 1st step of 1st Method.
  • Then, select the Cells from C5 to C11 in the current sheet.
  • Next, go to Paste Special > Conditional Formatting Only.

Conditional Formatting Only Command to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • At last, you’ll get your output as shown in the picture


4. Assigning Paint Format Command

We can also use the Paint Format Command to copy Conditional Formatting from one sheet to another Google Sheets. This method gives the output very fast than the previous method.

Steps:

  • Before all, select the Cells from C5 to C11 in Dataset and click on the Paint Format Button from the Toolbar.

Assigning Paint Format Command to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • Then go to the new sheet which has English scores and just click on Cell C5. The Conditional Formatting will automatically get updated very quickly.


5. Duplicating Conditional Formatting Formula

We have a different situation now. We have a custom formula applied to Cells C5 to C11. The formula is-

=C5:C>=70

At present, we won’t copy the conditional formatting directly rather will copy this formula into another sheet.

Duplicating Conditional Formatting Formula to Copy Conditional Formatting from One Sheet to Another Google Sheets

Steps:

  • First of all, we copy the formula from the Conditional Format Rules Menu.
  • Next, we go to the new sheet and select the Cells from C5 to C11.
  • After that go to Format > Conditional Formatting.

Duplicating Conditional Formatting Formula to Copy Conditional Formatting from One Sheet to Another Google Sheets

  • Next, select the Custom formula is option under the Format rules menu and Paste the formula in the formula box.
  • At last select green color from the Color Button and press Done.

  • Finally, we get our desired output.


How to Copy Conditional Formatting with Relative Cell References in Google Sheets

In addition to our former processes, we can copy Conditional Formatting with relative cell references in Google Sheets. In the following dataset, we have a Conditional Formatting applied to Cells C5 to C11. We’ll copy it into the next column, Column D.

How to Copy Conditional Formatting with Relative Cell References in Google Sheets

Steps:

  • In the first place, copy the Cells from C5 to C11 by pressing Ctrl+C together.

How to Copy Conditional Formatting with Relative Cell References in Google Sheets

  • Next, select the Cells from D5 to D11.
  • After that, go to Paste Special > Conditional Formatting Only.

  • Finally, you‘ll see that the cell references automatically have changed to Cells C5 to D11.


Conclusion

That’s all for now. Thank you for reading this article. In this article, I have tried to discuss 5 quick methods to copy Conditional Formatting from one sheet to another Google Sheets. I have also mentioned how to copy Conditional Formatting with relative cell references in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.

Sajid Ahmed

Sajid Ahmed

Hi! This is Sajid Ahmed. At present, I am working as an Executive, Content Development at OfficeWheel.com. Here I will be posting articles related to different problems of Google Sheets. I have completed my graduation from Bangladesh University of Engineering and Technology in Naval Architecture and Marine Engineering. Despite a different background, I have a great interest in the research and development of data science. I try to make innovative solutions to different problems regarding Google Sheets. Please follow my blog at Officewheel.com to get different solutions regarding Google Sheets.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo