Comprehensive Guide to Using AI With Google Sheets

Everyone’s job relies on data, and spreadsheets are how we keep track of that data. There’s a wealth of information waiting to be unlocked in the spreadsheets that you work on regularly, and there are much easier ways to create, analyze, and synthesize Sheets with Google’s suite of AI tools. Google recently introduced the ability to work with more advanced formulas using AI, simplifying one of the core functions of spreadsheets.

​

Google Sheets has always been the easiest spreadsheet editor to work with, allowing for easy real-time collaboration with colleagues, just like any other app in Google Workspace. It can safely be said that making data easy to work with and discover is Google’s superpower, and they just made it that much easier for you to do your job with AI tools in Google Sheets. Here’s your guide to unlocking the power of those tools.

​

​

With Gemini, all you have to do is tell Gemini what you want and it will do it. Create a new Sheet, click your Gemini icon at the top right, and ask it to create the empty shell of what you need. For example, you can ask to create a sentiment analysis report for social media posts, or a budget forecast for the next quarter building on historical data from the last four quarters.

​

It will then create what you need, and you can refine as necessary with other prompts. Then you can input your data. In some cases, Gemini will guess what needs to be filled out, but will always ask your permission before entering data.

​

​

Once you’ve created your spreadsheet, the Gemini sidebar will show suggested formulas to use. You can also ask it to create a formula for you with natural language. This saves quite a bit of time and reduces the learning curve for people who are new with working in Google Sheets.

​

Recently, Google introduced the following AI features in Sheets:

​

If you’re generating a complex formula, you can now have it broken down in plain language by Gemini, allowing you to make sure it is what you really want. To do this, just add “Explain how the formula works” at the end of your prompt.

​

Gemini will now tell you why a formula failed and suggest steps to correct it.

​

You can create multiple formulas with one prompt. For example, if you want different formulas for different product categories, you can now create them using one prompt.

​

​

Do you have to write two hundred unique product descriptions or emails? No problem. You can generate, categorize, or summarize unique text at scale by inserting commands into each cell.

​

With Gemini working for you, you don’t even need to import data from ChatGPT or other sources to do this. It can work on thousands of rows in the background at once, and won’t keep you waiting long for the result. Discover how to unlock this extremely useful feature here.

​

​

Gemini for Google Sheets may solve the blank spreadsheet problem, but are you wondering how to build prompts for it? Google’s got you. They’ve created a clear prompt writing guide for every department, from administration to sales. It isn’t just for Google Sheets, either - the prompts work in Google Docs, Sheets, and Slides.

​

Start here to learn how to write an effective prompt and then select your department for more granular advice. If you want even more training, check out the Google AI Essentials course on Coursera. Note that Coursera has a small monthly fee if you can’t complete the course after your seven-day free trial.

​

​

Once all of that juicy data is in, you can conduct data analysis with Gemini. This allows you to do everything from getting raw data to creating charts and graphs based on the data. Just enter a prompt like “find out what departments generated the most revenue” or “predict my cashflow for the next two quarters.”

​

Charts can be anything you want them to be, from heatmaps to pie charts and more. The only drawback with chart creation is that they cannot be edited after the fact, but you can always erase them and try another prompt if they weren’t quite what you had in mind.

​

​Are you stuck with having to analyze data from multiple Google Sheets, such as a bunch of monthly reports? This is a fairly common task to see trendlines, create budget forecasts, and more. NotebookLM is an extremely powerful tool that uses Gemini to analyze multiple data sources, so you can even throw in other document types to the mix to create a detailed briefing guide, a video, or just get the insights you need if you’re working with multiple files.

​

Upload your files to NotebookLM (you’ll need to save them as .txt files first), and then ask it the questions you need answers to. Examples may be “How did engagement change on social media between Q1 and Q4” or “Give me the total spend for events in Q1 and Q2.”

​

This powerful way of synthesizing data can let you get the insights that previously took you hours to put together manually.

​

If you just want to have fun with it, you can even ask it to create a podcast-style audio overview that features trend highlights. Does that sound familiar from your 2024 Spotify Wrapped roundup? That’s because Spotify used NotebookLM to create those “podcasts” based on your own data.

​

Just a note that you can download your Sheets as PDFs, but NotebookLM is less likely to pull the raw data you’re looking for from that file type. The .txt format is much better for detailed analysis.

​

All of these AI enhancements to Google Sheets just made it the go-to app for those who don’t love spreadsheets but have to use them anyway. For the dataset of spreadsheet lovers, they offer easy ways to create, refine, and explain advanced formulas that you just aren’t going to get with Copilot. If you think it’s time to make the switch, or even if you’d just like to try it out, get in touch with us. We’ve managed digital change at so many organizations at this point that - well, we’d have to use a spreadsheet instead of a document.

​

Contact us today for a demonstration of how the new AI-powered Google Sheets goes head-to-head with the competition.

​