How to Copy Conditional Formatting in Google Sheets

Okay, let’s say you are a sales manager at a medium-sized retail company, and you use Google Sheets to track your team’s performance. Each week, you record the sales figures for your team in a spreadsheet named “Weekly Sales”.

To quickly identify top performers and those needing support, you use conditional formatting to highlight sales above $1,000 in green and below $500 in red.

Now, you want to apply the same formatting to another week’s column, from Week 4 to Week 5, the data you have just updated in the table.

The best part of conditional formatting is that you can copy it from one range of cells to another range of cells. This tutorial will explore the steps for copying and pasting conditional formatting in Google Sheets.

Use Copy only Conditional Formatting from Paste Special

You’ve applied conditional formatting to columns B through E to highlight sales data. Now, you want to copy this formatting to column F for Week 5.

Here’s a simple way to do it:

1. First, select the range of cells in column E (Week 4) with the conditional formatting you want to copy (E2:E6). Right-click on the selected cells and choose “Copy”, or use the keyboard shortcut Ctrl + C.
2. Next, select the range of cells in column F (Week 5) where you want to apply the same formatting (F2:F6). Right-click on these cells, choose “Paste special,” and then select “Conditional formatting only.”

This will apply the same conditional formatting rules from Week 4 to Week 5, making it easy to see which sales are above $1,000 and below $500.

Use Paint Format to Copy/Paste Conditional Formatting

Copy and paste the conditional formatting using the “Paint Format” button. Here are the steps you can use for this:

1. First, select the range of cells in column E (Week 4) with the conditional formatting you want to copy (E2).
2. After that, click on the “Paint Format” button from the toolbar at the top of the Google Sheets Window (it looks like a paint roller). This button will copy the formatting from the selected cells.
3. Next, click and select the range of cells in column F (Week 5) where you want to apply the same formatting (F2:F6).

Note – If you know that your data will extend in the future, you can use a dynamic named range or the new “Convert to table” in Google Sheets for auto-expanding conditional formatting.

Expanding the Range in the Rule to an Additional Column

Suppose you are dealing with continuous ranges, as in our example. In that case, you can expand the existing conditional formatting rules to include additional columns instead of copying and pasting.

The existing rules will be displayed in the panel that opens on the right. Click on the rule you want to expand. In the “Apply to range” box, adjust the range to include the new column (change it from B2 to F6). This will expand the conditional formatting to the new column (F), applying the same rules.