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

LAST UPDATED
September 4, 2024
Jason Gong
TL;DR

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.

Bardeen can automate copying data between sheets, which would also bring over the conditional formatting in one click. Try these playbooks:

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.

Bardeen can help you quickly populate your Google Sheet with data from various sources, making it easier to apply conditional formatting rules:

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 Realtor.com 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.

Contents

Automate Google Sheets with Bardeen's AI

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

Get Bardeen free
Schedule a demo

Related frequently asked questions

Add Dashboard to Salesforce Homepage: A Step-by-Step Guide

Learn how to add a dashboard to the Salesforce homepage for enhanced data visibility and decision-making, including a step-by-step guide and tips.

Read more
Export Salesforce Reports to Excel & Google Sheets in 4 Steps

Learn to export Salesforce reports to Excel, Google Sheets, and automate the process for efficient data management and analysis.

Read more
Enable Dark Mode in Notion: Easy Steps (2024)

Learn how to enable dark mode in Notion on desktop and mobile to reduce eye strain and improve visual appeal across all workspaces.

Read more
Import Data Across Sheets in Google Sheets: A Guide

Learn to import data across Google Sheets using IMPORTRANGE, QUERY, and direct cell references to streamline workflow and ensure data synchronization.

Read more
Boost Excel Productivity with ChatGPT: Full Guide

Learn how to integrate ChatGPT with Excel for automating tasks, generating formulas, and advanced data analysis.

Read more
How to Add Zoom to Outlook: Easy Integration Guide 2024

Learn to integrate Zoom with Outlook effortlessly. Follow our step-by-step guide to enhance your meeting scheduling in 2024.

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.