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

Embed Spotify Playlists in Notion: Easy Guide

Learn to embed Spotify playlists in Notion with our easy guide. Enhance your workspace with music without switching apps. Simple steps included.

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
Guide to Uploading Files in Salesforce: Methods & Tips

Learn how to upload files in Salesforce individually, in bulk, or automate with the REST API. Manage documents efficiently within your CRM.

Read more
Quick Excel Upload to ChatGPT: Step-by-Step Guide

Learn how to quickly upload your Excel file to ChatGPT for efficient data analysis and task automation.

Read more
Create Recurring Events in Notion: A Step-by-Step Guide

Learn to create recurring events in Notion with this step-by-step guide. Manage your schedule efficiently with Notion's calendar feature.

Read more
Convert Apple Numbers to Google Sheets: A Step-by-Step Guide

Learn how to convert Apple Numbers to Google Sheets using direct export or CloudConvert for easy cross-platform collaboration and sharing.

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.