Hi Reader,

Welcome to the Google Sheets Tips newsletter #287, your Monday morning espresso, in spreadsheet form!

A big thank you to this week's sponsors who help keep this newsletter free to read:

UNITO

Keep Your Sheets in Perfect Sync ⚡

Don’t waste time on manual entry. Use Unito to automatically import, export, and sync data from 40+ leading apps with Google Sheets. It’s the simplest way to gather data, send it somewhere else, and stay in sync with real-time, 2-way updates as changes are made on either end.

Learn more

BOTSHEETS

Create Chatbots Powered by Your Google Sheets

With Botsheets, you can create your own AI chatbots using your Google Sheets. Your chatbots will be able to give your customers up-to-date, personalized responses based on your data, and even ask questions to collect and add more data to your Sheets. No steep learning curve or coding required.

Try Botsheets for free (no credit card needed)

➜ New videos on YouTube

Check out my newest video tutorials on YouTube:

🎥 Common Formula Errors in Google Sheets and How To Fix Them

🎥 How To Filter Dates in the QUERY Function

_______

➜ News

I.
The next Totally Unscripted episode is this Wednesday, 24 January 2024 at 12:00 PT / 15:00 ET / 20:00 GMT.

In this episode, hosts Martin Hawksey (Google Developer Expert) and Charles Maxson (from Google), discuss how developers are shaping the future of Google Workspace with GenAI.

Link to watch >>

_______

➜ Google Sheets Tip #287: Copy and paste conditional formatting rules

In this tip, we look at how to copy and paste conditional formatting rules, saving you time from having to recreate them.

Once you know the tricks, it's as easy as ABC!

Specifically, let's see how to:

Copy conditional formatting rules and apply them elsewhere, and
Change conditional formatting rules to permanent formats.

1. Copy Conditional Formatting Rules Elsewhere

Suppose we have this dataset with a conditional formatting rule that highlights all rows with the word "Seller" in column C:

The custom formula for this rule is:

=$C1="Seller"

The crucial detail here is the "$" in front of the C. It's what applies the conditional formatting rule to the entire row (learn more about how this works in this post).

To apply this rule to a different dataset, follow these steps:

Highlight the current dataset (that contains the rule)
Click the Paint Format icon in the toolbar (the paintbrush)
Navigate to your new dataset and click into the top left cell of the data.

Voila!

Here is this method in action:

The conditional rule(s) is copied and functions the same as it does in the original data.

There are two important caveats:

the range copied remains the same (if you copy A1:D11, the new rule is applied to a range of the same size, starting wherever you click as the top left cell).
The rule applies to columns in the same order as the original. In the example above, the rule checks column C for "Seller". If you copy to a dataset with the Buyer/Seller representation in column B instead of column C, then the rule won't highlight anything (it will still be there, but won't find any "Seller" in column C).

2. Make Conditional Formatting Permanent

Suppose you've used conditional formatting rules to highlight your data but you no longer want it to be conditional.

Maybe you want to share the Sheet with others and worry that the conditional formatting could be accidentally removed.

There's an easy way to convert it to plain formatting.

Here are the steps:

Highlight your whole dataset
Copy your dataset (Ctrl + C or Cmd + C on Mac)
Go to menu: Format > Clear formatting
Right-click somewhere inside your dataset
Select: Paste special > Format only

Now the conditional formatting is permanent. For example, the cells now have background colors and there are no conditional formatting rules.

In the following GIF, you can see the conditional formatting rule disappears when I clear the formats, before I add them back as regular background colors: