Quick Links
Google Sheets is a powerful tool, especially when simplifying spreadsheets and automating repetitive calculations with functions. But let’s face it—crafting complex formulas for advanced data manipulation can be daunting, even for experienced users.
With the integration of Google’s AI, Gemini, directly into Google Workspace, I no longer need to manually write formulas.
How to Add Gemini to Google Sheets
To gain access to this feature, you need to have signed up for Google’s Workspace Labs. It is really easy to do so, as long as you live in one of thesupported countries and territories. You can skip this section if you currently have access to Workspace Labs.
1. Writing Nested IFs Formulas
The IF function allows you to test a specific condition and define a value to return if it is true and another value to return if it is not. you’re able to modify your IF statements with the logical operators AND, OR, and NOT, and evennest multiple IF statementswithin one another for more complex data analysis in Google Sheets.
Nesting is a double-edged sword, though. It is technically capable of a lot of heavy lifting, but it can be quite cumbersome and hard to use if you are just starting out with functions and formulas. However, with Gemini as your co-pilot, you can write long, visually complicated formulas that do exactly what you want.
First, you need to open the spreadsheet in Google Sheets. Click the diamond-shapedAsk Geminiicon next to theSharebutton, and a side panel should appear. Gemini will analyze the content of the spreadsheet and present a summary.
Enter your prompt in the text box at the bottom of the side panel. For calculating the grades of a class of students, I entered the following prompt:
Create a nested IFs formula that can calculate the grade of the students based on their score. A is 90 and greater, B is 80 to 89, and so on.
Gemini was able to perform the task in a single try and created a formula that worked perfectly. TheInserticon below the formula can be used to add it to the spreadsheet.
2. Automating Data Lookup
With Gemini, you no longer need to writehorizontal or vertical lookup formulas. You can leverage Gemini’s ability to parse your spreadsheet’s data and answer requests based on its understanding. This means you can compose your queries in natural language and get the desired results with less time and effort.
With two tables in a spreadsheet, a root table and a lookup table, I can ask Gemini to look up the associated value for an item in my root table.
To find the location of Mark Twain, I entered the following prompt:
Find the address of Mark Twain in the table from F4 to G9
Gemini returned the following formula:=FILTER(G4:G9, EQ(F4:F9, “mark twain”)), which gave me the address I was interested in. This may seem trivial in a spreadsheet with a few items, but it can be a game-changer when you are dealing with thousands of items.
SeveralAI prompting tips and tricks exist, but the most important thing is to be as descriptive and specific as possible.
3. Creating Date and Time Formulas
Gemini can automatically reference all of Google’s inbuilt date and time functions when creating formulas on request. With Gemini, I canadd the current time to my spreadsheet,find the number of days between two dates,convert unformatted text to dates, and use other date and time functions without having to memorize any.
For instance, the prompt below accurately returns a formula to calculate the difference between two dates:
Calculate the number of days between the end and start dates for each task in the project.
4. Text Handling Functions
Apart from creating date and time formulas, Gemini can also handle text manipulation operations in Google Sheets. Gemini can extract specific text from a line, convert text to numbers and vice versa, and format text without you having to write any formulas. It can also generate new text based on prompts, make data validation suggestions, and summarize the text in your spreadsheet within the same workflow.
You can even reference your Gmail inbox and your Drive files in your prompts, such as:
ClickSourcesunder the response to see the files Gemini used in its response.
5. Creating Custom Functions
Custom functionsare a great way to extend Google Sheets’ built-in features. It involves writing code in Google Apps Script, a proprietary, cloud-based scripting platform for Google Workspace.
If you have an idea for a custom function but lack advanced coding skills, you can describe your concept to Gemini and let the AI generate the code for you. For instance, I used Gemini to create a simple script that converts feet to centimeters. Gemini can also handle more complex tasks, such as language translation or fetching live updates from the internet.
While Gemini is not perfect by any means, it successfully generates the correct formulas about seven times out of ten, in my experience. Despite its imperfections, it’s a significant time-saver and can even be a valuable learning tool. However, if you frequently need to write formulas, there’s no substitute for mastering theessential Google Sheets functions. Gemini is a powerful assistant, but understanding the fundamentals will always be key to getting the most out of your spreadsheets.
