Google Sheets: Copy conditional formatting [2024 how-to]

Jason Gong
June 6, 2024

Use the paint format tool or copy-paste special to copy conditional formatting.

By the way, we're Bardeen, we build a free AI Agent for doing repetitive tasks.

Since you're working with Google Sheets, check out our AI integration. It automates tasks and saves you time.

Conditional formatting in Google Sheets is a powerful ally when tackling a wide variety of spreadsheet use cases. And copying any setup is as easy as moving a file.

By the time you’re done reading this guide, you’ll be an expert at copying conditional formatting across different cells, or from one Google Sheet to another.

Using conditional formatting means easier surface-level analysis for folks reviewing your spreadsheets. Conditional formatting can be especially useful when dealing with large datasets or when you need to maintain a consistent format across multiple sheets.

Outside of a data analysis-enhancing copy-paste trick, you can use the paint format tool and formulas to spread conditional formatting throughout your whole worksheet. Easy, right?

You can copy conditional formatting by:

  • Using the paint format tool
  • Copy and paste special action
  • Applying a relative reference formula to your sheet

2 ways to copy conditional formatting from one Google Sheet to another

To copy conditional formatting across different sheets, take advantage of either the paint format tool or the copy and paste special feature. Here are the steps for each method:

Paint format tool

1. First, select the cell with the conditional formatting you wish to copy. 

2. Then, click on the Paint format tool in the toolbar. 

3. Finally, go to the other sheet and select the cells where you want to apply the formatting.

Copy and paste special

1. Select the range of cells with the conditional formatting, right-click, and choose 'Copy' or use the shortcut Ctrl+C (Cmd+C on Mac). 

2. Next, go to the target sheet, select the destination cells, and right-click. 

3. Hover over 'Paste special', and select 'Paste format only' or use the shortcut Ctrl+Alt+V (Cmd+Option+V on Mac).

You can also select 'Paste -> conditional formatting only,' but there's no keyboard shortcut for this.

Note that copying conditional formatting from one sheet to another creates a new rule for the second sheet.

Doing manual repetitive tasks in Sheets can be a pain. Download our Chrome extension to save yourself precious time. Completely free, with a 14-day trial of premium features.

How to copy conditional formatting to another cell, same Google Sheet

Copying conditional formatting allows you to apply the same visual criteria to different cells or sheets. If you’re using a Google Sheets calendar, you could change a cell's background color to highlight key phrases.

When you need to apply conditional formatting to individual cells or a smaller range within the same sheet, you can use the same methods mentioned above. 

The paint format tool is handy for quickly applying formatting to another cell, while the copy and paste special feature is more suited for applying formatting to a range of cells.

How to copy Google Sheets conditional formatting with relative references

Understanding relative references and absolute references is crucial when copying conditional formatting rules. Especially if you want the formatting to adjust dynamically, based on the cell's position.

To ensure that conditional formatting rules apply correctly with relative references, do the following:

Use the '=SEARCH("Order",A2)' formula

Using  '=SEARCH("Order",A2)' as a custom formula in conditional formatting makes each row search its respective A column cell. This formula ensures that the conditional formatting checks the content of each row individually, maintaining the relational integrity of your data.

Use the INDIRECT function in combination with ROW() and COLUMN()

For more complex scenarios, where you need the conditional formatting to reference specific rows or columns relative to the cell being formatted, you can combine the INDIRECT function with ROW() and COLUMN().

You can use:

  • 'INDIRECT("A"&ROW())' for rows
  • 'INDIRECT("R"&ROW()&"C"&COLUMN())' for both rows and columns

When going more complex, you may even want to take advantage of Google Sheets automation and macros to get this done.

These methods allow you to maintain the dynamic nature of your conditional formatting rules when copying them across your Google Sheets.

Become a Google Sheets wizard with Bardeen

While people love updating spreadsheets manually over and over and over again, you can make your life easier by using automation for conditional formatting and related tasks. Working with Bardeen can significantly enhance productivity and ensure data consistency.  

With hundreds of playbooks that can automate Google Sheets tasks, what’s not to love? Imagine scraping social media information and saving it all into a worksheet or assisting with data translations, in minutes.

Here are a few popular ways to work with Bardeen:

1. Close all 50 of your tabs with this automation — set your criteria and let the integration pull all the properties that match your set criteria into a spreadsheet that you can review anytime.

2. Instead of loading up your clipboard copy and pasting from LinkedIn to a sheet, just run this simple automation to extract contact data in seconds.

3. Whether you're an avid shopper or looking for new products to market, this playbook will automatically pull top sellers from Amazon into a Google Sheet for review every week.

Automate Google Sheets with Bardeen's AI

Save time by automating repetitive tasks in Google Sheets with Bardeen.

Get Bardeen free

Related frequently asked questions

Guide to Writing Essays with ChatGPT: 5 Steps

Learn how to guide ChatGPT to write essays with detailed prompts, specific length requests, and utilizing ChatGPT Playground for creativity.

Read more
Step-by-Step Guide to Creating Salesforce Templates

Discover how to create Salesforce templates for emails and general communication, including steps for HTML, Visualforce, and testing for effectiveness.

Read more
How to Insert Copied Rows in Google Sheets: A Guide

Learn how to insert copied rows in Google Sheets without overwriting existing data, enhancing your data management and productivity.

Read more
Easy Guide to Add Footnotes in Google Docs: 4 Steps

Learn to easily add footnotes in Google Docs, including Chicago style and multiple footnotes, for both web and mobile app versions.

Read more
Insert Newline in Google Sheets Cell: 4 Easy Steps

Learn how to insert a newline in Google Sheets cells using keyboard shortcuts, CHAR(10), and text wrapping for improved data readability.

Read more
Lowercase Conversion in Google Sheets: A Step-by-Step Guide

Learn how to easily convert uppercase to lowercase in Google Sheets using the LOWER function or add-ons like Power Tools for bulk changes.

Read more
how does bardeen work?

Your proactive teammate — doing the busywork to save you time

Integrate your apps and websites

Use data and events in one app to automate another. Bardeen supports an increasing library of powerful integrations.

Perform tasks & actions

Bardeen completes tasks in apps and websites you use for work, so you don't have to - filling forms, sending messages, or even crafting detailed reports.

Combine it all to create workflows

Workflows are a series of actions triggered by you or a change in a connected app. They automate repetitive tasks you normally perform manually - saving you time.

get bardeen

Don't just connect your apps, automate them.

200,000+ users and counting use Bardeen to eliminate repetitive tasks

Effortless setup
AI powered workflows
Free to use
Reading time
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
By clicking “Accept”, you agree to the storing of cookies. View our Privacy Policy for more information.