How to Copy Conditional Formatting in Google Sheets

Conditional formatting is one of the most powerful tools in Google Sheets. It enables automatic changes to cells based on a set of criteria. But when you have achieved a nice formatting rule for one column, you, sometimes, want to use the same formatting rule on another column. It’s particularly helpful when you have a bunch of data to adjust, or when you want all your sheets to look alike.

In this article, we’ll find out some easy methods to copy conditional formatting rules which can be applied to another cell or even in another worksheet.

Understanding Conditional Formatting in Google Sheets

Before we demonstrate how to copy conditional formatting, let’s explore how conditional formatting works in Google Sheets. With conditional formatting, you can create rules that are able to change the format of cells, depending on what the cell contains or what the value is. Such rules can also set background colors, text colors, font styles, or other types of visuals based on conditions.

Here are a few important things about conditional formatting:

• Rules may apply to individual cells or range of cells
• You can define multiple rules for the same range
• Rules can reference cell values, formulas or date comparisons
• Rules are sequential, such that we process one rule as the next step after all previous rules (after possible override) have been done.
• Rules may be referencing from absolute or relative reference which will change the behavior when copied

Knowing these basics will help you out when you want to start your copy paste of conditional formatting through your spreadsheet.

In this tutorial, I will use a sample dataset on monthly sales performance. For the dataset, it includes products, actual sales, sales targets, category and region. As we can see in the image below, I have already used the conditional formatting feature to color the cells in the column labelled Sales Target so that those which are greater than 100% (over target) are shaded green and those which are less than 80% (significantly below the target) are shaded red.

This conditional formatting is an incredibly useful way to understand which products are working and which are in need of some help. Now, we’d like to copy this formatting so that we can use it on other columns in our spreadsheet.

To begin with, Paint Format is the fastest way to copy formatting from one cell and apply it to another cell or range of cells in Google Sheets.

Steps:

➤ First of all, choose the cell or range with the conditional formatting to copy. As the image shows below, here I selected the D column with conditional formatting.

➤ Once you select the cells, click on the Paint Format tool (a small paintbrush icon) in the toolbar. Your cursor will become a paintbrush icon.

➤  With the cells highlighted, click and drag to the cells where you want the same formatting to apply. Then just let go of the mouse button, and the conditional formatting has been applied to the cells.

When you use the Paint Format tool, you copy all of the formatting, the conditional formats, the font, the border,  whatever you can see. This approach works really well when you only need to transfer rules to proximate places and will allow your formatting rules to be relative in nature.

If you want a little more fine-grained control over what formatting is copied, you can try using Paste Special. Unlike using the Paint Format icon, this method allows you to copy the conditional formatting only without copying other cell formatting. Here how it works:

➤ Like before, let’s begin with selecting the cells that are formatted with the conditional format to copy.

➤ Then Right-click and choose Copy or hit Ctrl+C (Cmd+C on Mac).
➤ Choose the cells to where the format should be copied to.

➤ Then again right click and choose Paste special from the context menu. Here you have to select only Conditional formatting only from the choices.

➤ And here’s the result.

The reason why you should use this method is that you will only be copying the conditional formatting rules and not any other part of cell formatting (if you have font formatting, cell borders, or cell background color). It is especially good when you want to preserve other formatting of the destination cell.

Last but not the least to mention, you can also apply the same conditional formatting to some further data by applying another very simple method in Google Sheets.

Steps:

➤ Find the conditional formatting sidebar by selecting Format > Conditional formatting from the menu bar.

➤ Now locate the rule you wish to copy in the sidebar. Click on the rule to edit it.

➤ Once you select the rule, tap the Select Data Range icon just right to the Apply to Range box.

➤ Then choose Add another range.

➤ Now simply put the additional cell range where you want to expand the selected conditional formatting rule.

➤ Then, tap OK to save the changes and click Done in the sidebar to apply all changes. By doing so, the selected conditional formatting rule would be copied to the selected cells.

Here you can also copy the other rule in the same way. However, this is a good technique when you want to keep one rule for multiple isolated areas of your spreadsheet. It helps keep your conditional format rules organized and manageable, especially if you have numerous rules for a complex spreadsheet.

Frequently Asked Questions

Is there a way for me to “paste special” formatting between two different Google Sheets?

Yes, you can apply conditional formatting from one file and use it in another using the Paint Format option or by copying and pasting special. Just open both sheets and go through the same process.

How come when I copy my conditional formatting to a new location, it doesn’t work?

It is possible that you have used relative cell references in your initial condition. See if your current rule has any relative references in it that would have to be altered at the new destination.

What if I want to edit a conditional formatting rule after I have copied the rule?

You can, you go to Format > Conditional formatting in the menu bar to find the rule in the sidebar and click on it to edit, change as desired and then click Done to save.

Are there limits to the number of conditional formatting rules I can use in Google Sheets?

No published hard and fast rule but too many rules (especially detailed ones) can slow down the performance of the spreadsheet. As a rule (if you’ll excuse the pun), you want your rules to be as organized and centralized as possible.

Summing Up

Learning how to copy conditional formatting in Google Sheets is an important skill that can save you a lot of time and makes the visualization of your data more consistent. Whether you like the quick and easy Paint Format tool, the specific options available in Paste Special, or how logically it runs with the existing rules, now you have three ways to quickly reuse those rules. But still, if you have any query or confusion, feel free to leave it in the comment section.