TL;DR
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 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.
.png){kind=icon}
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.
.svg)
.svg)
.svg)
