Status arrows are little up or down symbols added to data that make it quick and easy to understand what’s happening. They’re a useful way to highlight changes in your data.
Consider the following sales data which has a % change column:
Now take a look at the same data with colors and arrows added to call out the % change column:
It’s significantly easier/quicker to read and absorb that information.
How to Add Status Arrows
Hidden in the Custom Number Format of Google Sheets is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0, or less than zero. This can be used to show the up or down status arrows.
Step 1. Highlight the % column and go to the custom number formatting menu:
Step 2. Copy and paste this custom number format rule into the Custom number formats popup:
[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬
What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.
The square brackets to specify the color you want e.g. [color50] for green.
The cells in the data are still numbers even though the status arrows are showing. The arrows are a visual layer on top of the number.
The AI Function in Google Sheets brings the power of Gemini AI directly into your cells.
It uses Gemini’s large language models (LLMs) to process the text prompts and return helpful responses. It’s akin to using a prompt in the chat window of Gemini, but with the convenience of being accessible in your Sheet.
And it’s as easy to use as any other formula!
Who has access to the AI Function?
The AI Function is available to people on the following Google Workspace plans:
Business Standard and Plus
Enterprise Standard and Plus
Customers with the Gemini Education or Gemini Education Premium add-on
Google AI Pro and Ultra
Anyone who previously purchased these legacy add-ons will also have access to the AI function:
Gemini Business
Gemini Enterprise
Additionally, it’s available to users enrolled in the Google Workspace Labs program.
☠️ Note: If you don’t have access to the function, you can achieve any of these results by copy-pasting data into Gemini (or ChatGPT or Claude) and running the prompt there. And then you can paste the results back into your Sheet. Obviously, it’s not as convenient!
How to use the AI Function
The AI function is simple to use and has the following form:
=AI( "prompt" , [optional range] )
It takes two arguments:
the prompt, which is a description of the action you want the function to perform, enclosed in double quotes, and
an optional range, which is a reference to a single cell or range containing information that provides extra context to the AI function.
The AI function is most useful for working with text. There are four broad actions that it can perform:
Sentiment analysis
Summarizing text
Categorizing text
Generating text
Example 1: Sentiment Analysis with the AI Function
Suppose you manage an AirBnb and you have a Google Sheet containing reviews. You can use the AI function to analyze the sentiment of these reviews to identify positive and negative reviews.
Use this prompt:
Categorize sentiment as positive, negative, or neutral
inside the AI function as follows:
=AI( "Categorize sentiment as positive, negative, or neutral" , D6 )
where the cell D6 contains the review.
Alternatively, we can include the reference to cell D6 directly in the prompt argument by concatenation:
=AI( "Categorize sentiment of the review in " & D6 & "as positive, negative, or neutral" )
In this case, we don’t specify the option range argument.
Perhaps the only benefit of this second approach is that it lets you reference non-contiguous ranges, i.e. multiple cells in different parts of your Sheet.
For example, this text generation AI formula references cells in column B and D (i.e. non-adjacent), which wouldn’t be possible with a single range argument:
But if you are using a single cell or range, it’s easier to include it as the second argument of the AI function.
Example 2: Sentiment Analysis as a Rating Chip
An alternative way to show sentiment analysis is to ask the AI function to output a rating between 1 and 5 and then format the cells as Rating chips.
The AI formula is:
=AI( "Rate the feedback in this review between 1 and 5, with 1 being the worst score and 5 being the best score" , D6 )
This gives an output between 1 and 5 (technically, 0 to 5 would also work).
Then highlight the cells with the rating numbers and change them to Rating chips via the menu:
Insert > Smart chips > Rating
Alternatively, if your data is in a Table, you can change the column type to a Rating chip under the column menu:
Edit column type > Smart chips > Rating
Example 3: Summarize Text
With the review text in cell D6, we can use this formula to summarize the text:
=AI( "write a summary of the user's feedback in under 10 words" , D6 )
Example 4: Categorize Text
Suppose we have a list of products that we need to categorize into groups. It’s a hugely time consuming task.
The AI function can dramatically speed that process up.
With the data in column B, we can use a formula like this to categorize the products:
=AI( "categorize the product into one of these consumer electronics categories: Audio,Home Entertainment,Mobile Device,Wearable,Home Appliance,Gaming,Smart Home,Computing,Photography,Kitchen Appliance,Drones,Mobile Accessory" , B7 )
Example 5: Generate Text
In this example, we use the AI formula to generate marketing copy for products on Instagram. We tell Gemini to add emojis and hashtags in the output.
Note also that because the product name is in column B and the key features are in column D, we need to insert the references into the prompt:
=AI( "Generate a short less than 20-word description for instagram for this product " & B6 & " with these features " & D6 & ". Make it funny, and include emojis and add relevant hashtags" )
This just inserts the value from cells B6 and D6 into the text string prompt before the AI formula runs it.
Example 6: Generate Email Drafts
We use a longer, ultra-specific prompt to ensure the emails are formatted exactly as we want them.
And the formula references data in the range B6 to G6 (i.e. the whole Table row) for context:
=AI( "Write a professional email to the parent or guardian of this student, summarizing their academic performance and classroom behavior. The email should be written from me, Ben Collins. I am their teacher. Start each email with a subject line formatted exactly as: Subject: Name of Student’s Progress Repor For example: Subject: John Smith’s Progress Report. The tone should be warm, supportive, and informative—highlight both strengths and areas for improvement. Avoid overly technical language, and keep the email clear and easy to understand.",B6:G6)
However, if you can handle complex formulas, the old-school, deterministic method is still more convenient since we can’t nest the AI function inside other functions yet.
But for reference, here’s how we can create an AI formula to unpivot data.
1) it cannot perform calculations like regular functions. If you try, you’ll get an error message:
With all the hype around AI at the moment, it’s tempting to apply it to everything we do.
So it’s good to step back and ask yourself if you really need an AI function. Most spreadsheets tasks that involve data will still be in the domain of regular, deterministic functions, like SUM, AVERAGE, FILTER, etc.
So don’t fall into the trap of trying to apply AI to everything!
2) it cannot be nested inside other functions.
If you try to nest the AI function, for example inside an IF function as shown here, you’ll get an error message:
3) the AI function has limits to how much it can generate in a 24-hour window. If you hit the limit, you won’t be able to click the “Generate” button until 24 hours has passed.
4) if you select multiple cells with AI functions and click to generate outputs, only the first 200 cells will be generated. Once the first batch of 200 is complete, you can generate the next batch of 200 cells, and onwards.
5) if you access Sheets through other cloud storage providers (e.g. Dropbox, Box, etc.) then you can’t generate content with the AI function.
6) The AI function cannot generate other functions, pivot tables, or charts.
Did you know that by changing the URL of a Google Sheet we can change how it behaves?
In this post, we look at 11 incredibly useful URL Tricks for Google Sheets.
For example, we can create a URL that automatically downloads the Sheet as a PDF. Or create a template ready for copying. And much more.
URL Tricks for Google Sheets
Take a look at any Google Sheets URL in the address bar of your browser.
It will take the following form:
https://docs.google.com/spreadsheets/d/ + file ID + / extension
We can change the /extension at the end of the URL to ensure a different action happens when a user uses that URL.
Feel free to click on any of the links below to see how they work. They are all linked to the following template, which you can copy for your own reference (bonus points if you use the correct URL trick to do that).
1. Standard Sharing Link Format
This is the default sharing link for Google Sheets. Users with appropriate permissions can view or edit the sheet.
Advantages: Provides direct access to the Sheet with full functionality.
Limitations: If set to “Anyone with the link can edit,” any users can make changes.
Best For: Team collaboration when you want users to view or edit the Sheet.
Hardly a week passes at the moment without some technology announcement that makes me go “wow!”.
This week it’s Google’s AI Studio that has been impressing me, offering a glimpse into what the future of software education could look like.
What’s different with this tool is that it lets you share your screen and have a verbal conversation with the AI model (in this case Gemini), so it can walk you through a problem.
In my case, I used it to walk me through building a pivot table. And whilst it didn’t get everything correct (yet!), it’s impressive and feels like you’re talking to an assistant, not your computer.
Sign in with your Google account. It’s free to use. When you log in, the homepage looks like this:
Click on the “Stream Realtime” in the left menu (shown by the red arrow).
Share your screen and have a conversation with AI Studio! For example, you could say: “I want you to walk me through building a pivot table that summarizes the total sales price by property type.” (And say it out loud, don’t type it in.)
Because the Pivot Table once existed under the data menu, AI Studio initially guided me there first (the only serious mistake it made). I said “I don’t see the pivot table option under the data menu, is it under one of the other menus?” So, don’t hesitate to interject if you can see it making a mistake.
Once you have a basic pivot table, you could ask for help sorting the data or adding another category.
Another great use case is asking it to explain lines of code to you. For example, share your screen showing a code block and try something like this “can you explain what the code on lines 52 – 56 does?”
It’s early days, so it’s far from perfect. It does make mistakes so you can’t rely on it blindly. You still need to cultivate your own knowledge.
But it’s a glimpse into what’s around the corner when we have infinitely patient AI assistants at our beck and call. And I think that’s a bright future, where we can be dramatically more efficient, focused on insights and outcomes, not code syntax or formula issues.
The drop down menu feature in Google Sheets is useful for efficient data entry. It can also be used to create dynamic spreadsheets, for example, dashboards that change based on choices a user makes.
At the end of July 2024, Google announced multiple selections in drop downs (scheduled to roll out in late-August/early-September). This is great news that will give us even more flexibility in our spreadsheets.
In this post, we’re going to take a look at this new drop down feature:
How to Enable Multiple Selections in Drop Downs
To enable multiple selections, create a regular dropdown and then check the box that says “Allow multiple selections”:
Boom! 💥 That’s it.
Working with Drop Down Multiple Selections in Formulas
If you select multiple options from a drop down menu, the output is a comma separated list:
To work with them in formulas, we use the SPLIT function to separate the choices into their separate parts.
Note the space after the comma. We need to be mindful of this in our formulas. Split out the comma-separated lists with this formula, which accounts for the space too:
=SPLIT(B2,", ",FALSE)
It looks like this in our Sheet:
We can use a BYROW function to expand this SPLIT formula to work with a range of multi dropdowns. This single formula in cell C2 splits out all the cells in the range B2:B6 and outputs all the data in C2:E6 range:
=QUERY(TOCOL(BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))),
"select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label Col1 'Option', count(Col1) 'Count'",0)
