Mehrab Imtiaz
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.
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.
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.
Step 2: Select the cell or range of cells on which you are going to paste the formatting. We have chosen Week 2.
Step 3: Right-click over the selected cells to open the options menu. Navigate to Paste special then Format only.
The alternative is to just use the keyboard shortcut to paste format only: CTRL+ALT+V
Our result:
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.
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:
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.
