I Quit Writing Formulas in Google Sheets—AI Does It for Me

Google Sheets is a powerful tool, especially when it comes to simplifying spreadsheets and automating repetitive calculations with functions. But let’s face it—crafting complex formulas for advanced data manipulation can be a daunting task, even for experienced users.

With the integration of Google’s AI, Gemini, directly into Google Workspace, I’ve found that 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 the supported countries and territories. You can skip this section if you currently have access to Workspace Labs.

To get started with Gemini in Workspace, sign up on the Workspace Labs website. Check the boxes at the bottom of the page to accept the terms of service and privacy policy, and click Submit.

Google One AI Premium subscribers also have access to Gemini in Google Workspace, alongside several other features. The following window should come up the next time you open Google Sheets.

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 can modify your IF statements with the logical operators AND, OR, and NOT, and even nest multiple IF statements within 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-shaped Ask Gemini icon next to the Share button 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:

Gemini was able to perform the task in a single try and created a formula that worked perfectly. The Insert icon below the formula can be used to add it to the spreadsheet.

2. Automating Data Lookup

With Gemini, you no longer need to write horizontal 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:

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.

Several AI 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 can add 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:

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:

• List the subjects of the last three emails sent to [Individual].
• Summarize the key points in [File Name] in Drive

5. Creating Custom Functions

Custom functions are 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 the essential Google Sheets functions. Gemini is a powerful assistant, but understanding the fundamentals will always be key to getting the most out of your spreadsheets.