Brought to you by:

Hi Reader,

After a few weeks break for the summer 🏖️, we're back with the Google Sheets Tips newsletter #355, your Monday morning espresso, in spreadsheet form!

What's new?

AI continues to dominate the tech headlines, with new features crammed into every corner of the internet, whether they’re needed or not. But beyond the hype, the tools are improving, and that includes in the spreadsheet domain.

These days, when I need to create a complex formula, I often start with a prompt rather than an equals sign. More often than not, the result is correct, or at least a solid starting point.

Case in point, today's tip: we use Gemini to build conditional formatting. (Don't worry, I'll also show you the classic formula approach, if you don't have access to Gemini in Sheets.)

➜ News

I.
​What's in store for Spreadsheets in the age of AI? This is a recording of a chat I had with GRID CEO, Hjalmar Gislason, earlier in the summer:

​AI & Spreadsheets Discussion​

II.
​Sticking with AI + Spreadsheets, OpenAI has entered the chat... In this video, they showcase their Agent model building a spreadsheet (a CSV file) from public data on the web.

It looks impressive, but I think we're still a ways off from truly agentic, hands-off spreadsheets. (At least that's my hope, so I can still add value to the chain 🤓.)

​ChatGPT agent Makes Spreadsheets​

Brought to you by: Bricks Bricks AI Spreadsheet: Create Dashboards & Reports in 1-Click Bricks makes creating reports and dashboards from your spreadsheet data a breeze. Just import your data and Bricks creates interactive dashboards for you in seconds - complete with charts, KPIs, insights, and tables you can customize and share. Get Instant Insights: Bricks analyzes your data, surfacing patterns, trends, and key insights Generate AI-Powered Summaries and Tables: Bricks organizes and summarizes your data, complete with neatly formatted tables Automatically Create Charts: Bricks builds charts for you and updates them when your spreadsheet data changes Join 10,000+ professionals from organizations like Amazon, Nvidia, and Stanford who use Bricks to get their reporting done 10x faster. Create your first dashboard

➜ Sheets Tip #355: Highlight the Top 5 Values using Gemini AI

For today's tip, let's see how to highlight the top 5 and bottom 5 in a set of data, using conditional formatting.

We're using this student dataset:

Let's see how you can do this with two approaches:

1. with Gemini AI, and
2. with traditional formulas.

1) Using Gemini AI

Gemini in the sidebar is available to Google Workspace subscribers or personal accounts that pay for an AI plan.

And with Gemini, you can now apply conditional formatting to data with prompts instead of writing the formulas yourself.

This is a HUGE benefit!

Open Gemini via the star button in the top right corner:

Click on a cell somewhere in your dataset and then enter this prompt in the Gemini sidebar:

(I added the third sentence to help Gemini understand how my numbers were formatted. It took trying a few different prompts for Gemini to give me a correct answer.)

Gemini proposes a plan and the formula looks like it will work:

Best of all, you can click the "Apply" button and have Gemini set up the rule in the actual Sheet.

The output is that the 5 rows with the top exam scores are highlighted yellow:

Impressive!

The formula works, although the second condition ($B2<="1") and the AND formula are not required. But it managed to get all the $ signs in the correct places to ensure the whole row was highlighted.

2) The DIY Approach (if you don't have access to Gemini in Sheets)

If you don't have access to Gemini in your Sheets, you can still apply conditional formatting in the regular way.

Highlight your dataset and go to the menu: Format > Conditional Formatting

Under the Format Rules, choose "Custom formula is" and enter one of the formulas below.

To highlight the top result:

To highlight the top 5 results:

To highlight the bottom result:

To highlight the bottom 5 results:

The order of the rules is important. The rules at the top take precedence.

So, you need to drag the "Top" rule above the "Top 5" rule, because the top result is in both and you want the "Top" formatting to override the "Top 5" formatting.

In your Sheet:

Good work team!

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Enjoy this amazing spreadsheet rap song from reader Andrew!