How to Copy Conditional Formatting in Google Sheets

featured image for copy conditional formatting in google sheets

Today, we will look at two very easy ways to copy conditional formatting in Google Sheets step-by-step. We will also see how we can apply these methods when trying to copy the conditional formatting to another spreadsheet/workbook entirely.

Let’s get started.

2 Basic Ways to Copy Conditional Formatting in Google Sheets

1. Using the Paste Special Option

The first method we will look at is as simple as it gets, which is to use copy and paste. But not just your usual CTRL+C and CTRL+V, we will instead use the Paste Special option of Google Sheets.

paste special to copy conditional formatting in google sheets

To show our example, we will use the following dataset. As you can see, we have used conditional formatting to highlight all the underperforming sale numbers in the Week 1 column.

applied conditional formatting to week 1 column

We will try to copy this formatting to the Week 2, and eventually, the Week 3 columns.

Let’s see how we can do that in the following steps:

Step 1: Copy a cell that has conditional formatting applied to it. You can simply use CTRL+C or right-click to copy.

right-click or ctrl+c to copy

Step 2: Select the cell or range of cells on which you are going to paste the formatting. We have chosen Week 2.

selecting the cells on which we want to copy the conditional formatting in google sheets

Step 3: Right-click over the selected cells to open the options menu. Navigate to Paste special then Format only.

navigating to paste special and format only option

The alternative is to just use the keyboard shortcut to paste format only: CTRL+ALT+V

Our result:

copy conditional formatting in google sheets using paste special

We have successfully copied the conditional formatting from the Week 1 column to the Week 2 column.

2. Using the Format Painter Tool of Google Sheets

The next method we will discuss is as easy to use as the previous one. Of course, we are talking about the Format Painter of Google Sheets.

This option is generally overlooked by many users, partly because many of them don’t know how to use it or are aware of it at all!

Let’s go through the process step-by-step and you will realize that it is quite similar to the Paste Special method:

Step 1: Select a cell that is already conditionally formatted and click on the Paint format icon from the Menu Toolbar.

selecting the paint format button

Step 2: Simply click-and-drag to select the cells you want to copy the conditional formatting to and release, the conditional formatting should be applied.

As you can see, the process described is fairly simple and can be achieved in two steps. We hope that many of you readers will be more willing to try the format painter out now.

Can You Copy Conditional Formatting to Another Workbook in Google Sheets?

The short answer is yes. However, it involves a couple of extra steps and utilizes some worksheet options that usually remain unused. This is because we cannot directly copy or paint conditional formatting from one spreadsheet to another as we have seen in our past two methods.

The first thing we must do is transfer our worksheet that contains the conditional formatting to the other worksheet.

To do that, right-click over the said worksheet name and a menu should pop up. What we are looking for is the Copy to option:

right-clicking over the sheet name to open the menu to find copy to option

Chances are that you might already have a spreadsheet created, so you can choose the Existing spreadsheet option to copy the current worksheet with conditional formatting to the other spreadsheet in Google Sheets.

But if you are preempting to use the current conditional formatting for a yet-to-create spreadsheet, you can choose the New spreadsheet option.

After that is done, apply either of the two methods we have discussed previously in this article (Paste Special or Format Painter) to copy the conditional formatting to your desired cells.

Final Words

That concludes all the ways you can use to copy conditional formatting in Google Sheets. We hope that these methods come in handy in your daily spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply