Gemini AI Revolutionizes Google Sheets with Formula Assistance and Error Correction

Gemini now explains why your Sheets formula failed

Nine months after Google infused Gemini AI into Google Sheets, the AI system graduated from text and charts to taking on formulas. (Even those plopped into messy wedding planning spreadsheets, I found.) Gemini's chatbot appears on the right-hand side of Sheets. Now, when you ask how to manipulate data, Gemini will respond with suggested formulas and step-by-step instructions about how to make the formulas work -- and why the formulas fail when they do. Errors will be followed up with a guide for how to correct the issue, Google says. If multiple formulas can do the same thing, Gemini will explain the options to the user. Formulas, for me, often involve the pesky problem of figuring out which function I need to make it work with my data. (Is it COUNTIF or COUNTIFS, or maybe the wild card of COUNTUNIQUE?) So, I tried Gemini's formula capability out for my wedding guest spreadsheet, a massive jumble of names, categories, email addresses, and checkboxes that my partner and I used to attempt to keep a handle on an ever-growing list of people with whom we wanted to celebrate our big day. We got married in May, but this spreadsheet still gives me nightmares. I expected that the math I would need to examine my guest list would be fairly straightforward, but testing Gemini's explanation feature on this messy spreadsheet would be the test, for me, about whether it would help my daily life. I first chose from Gemini's suggested formulas that offered to "create a formula to count the number of guests who responded 'Yes' to the invitation." Out popped a formula using the function COUNTIF in the column that I had labeled "RSVP." (Typically, it would have taken me two or so Google searches to remember which function to use when tallying the number of items in a list that meet certain requirements.) I could insert the formula directly into my spreadsheet by clicking the button that appeared next to the formula. I remember my wedding day as a joyous occasion full of the people that I loved most in the world -- but Gemini's answer told me that not a single person had said yes to my invite. That's when I realized I had not actually tracked RSVPs in that column, despite the column header saying as much in the top row. Acceptances, I remembered, were tracked on the wedding website my partner and I used. I had quite literally forgotten about this website until Gemini told me that I had zero guests who RSVP'd yes to my wedding. I tried again, this time on a more complex query: to calculate how many miles my guests traveled, collectively, to attend my wedding. (Given that my partner and I grew up on different coasts and our friends and family are spread out across the world, I knew the number would be high, but I wondered how high.) With all my guests' addresses in the spreadsheet, and the address of the wedding venue on hand, I figured it would be possible, but just very annoying to do by hand. Gemini also found my request annoying, responding, "I'm still learning and can't directly calculate distances for you in the spreadsheet." But then the response provided me with a way forward: "However, I can guide you on how to do this manually using a custom script in Google Sheets or by providing a formula for straight-line distances." The first option required an adventure into the wild west of Application Programming Interfaces, or APIs, which is the software that two computer programs use to talk to each other. "Calculating driving distances requires using the Google Maps API, which isn't natively supported by Google Sheets with a simple formula," Gemini informed me. The other option would be calculating "straight-line (as-the-crow-flies) distance, which is less accurate for road travel but simpler to implement" using a formula called the Haversine formula. I needed only the latitudes and longitudes, which Gemini apparently would not do for me. I found one set of coordinates to try it out. I had to ask for the Haversine formula, and the first response was in mathematical script, which was not going to do it for me. Then I asked for the Sheets formula that calculates the Haversine formula and got: =6371 * ACOS(COS(RADIANS(90 - A2)) * COS(RADIANS(90 - E2)) + SIN(RADIANS(90 - A2)) * SIN(RADIANS(90 - E2)) * COS(RADIANS(B2 - F2))) Gemini's response included a brief description of the "6371" number, which is the Earth's radius in kilometers. For miles, I could use 3959. Gemini got an error because my latitude and longitude cells were in a different column. Once I subbed in the cells for my latitudes and longitudes and switched to miles (because, ya know, America), the formula spat out a distance that another calculator website confirmed. Ultimately, I did not want to find the coordinates for each guest address, so my enthusiasm for this quest quickly dissipated. But I did feel confident that I could with help from Gemini.

Gemini in Google Sheets can now explain and fix broken formulas

The AI assistant can also diagnose and fix broken formulas automatically If you've ever stared blankly at a Google Sheets formula that spits out #REF! or #VALUE!, well, now you can ask Gemini for help. Google has upgraded its AI assistant for Sheets to fix and explain all those formulas, even the broken ones. Gemini can now not only generate spreadsheet formulas but explain why they work, or arguably more importantly, why they sometimes don't. The AI will explain it in simple language when there's an error and how to fix it. It can even correct any errant elements of an equation automatically or provide multiple options if you ask it to set up a formula for a task. No more copying error codes into search bars or scrolling through forum threads. Say you're trying to calculate the number of people who RSVP'd "yes" in a list filtered by zip code, and your formula crashes. Gemini might explain that the column it's referencing is formatted as text instead of dates, or that a range doesn't match your filters. Then it could offer a corrected formula that works. And if there's more than one way to calculate your answer, it can show you multiple formula options. This kind of support isn't just for novices, either. Even seasoned spreadsheet users occasionally mistype a cell range or forget that a column is full of inconsistent data types. Gemini's new ability to self-diagnose errors and explain them offers a safety net that lets you move faster without fear of hidden bugs -- or worse, quiet miscalculations that go unnoticed. The explanations are detailed enough to teach, not just patch. Google had already begun embedding its AI tools in Sheets, but that was limited to automation and chart creation. Now, Gemini has more of a fixer and educator role. Google says it's part of an ongoing effort to make complex workflows more approachable and to put AI where people already work. It also raises the stakes in the productivity AI arms race. Mainly, that means pitting Sheets and Gemini against Microsoft's Copilot in Excel, but there are smaller rivals around as well. But, Gemini's offering of real-time formula correction and multi-method explanations sets Google up with a specific niche, turning Sheets into a kind of dynamic learning environment rather than just a blank canvas for numbers. That focus on education is what could make this stick. Gemini's ability to explain its formulas as it generates them gives users a chance to understand what they're actually doing, instead of just trusting the black box of AI. Of course, it's not truly magic, and Gemini can still struggle with poorly labeled sheets or confusing requests. But being able to use words instead of math or code to describe what you want the Sheet to do is potentially an enormous time saver. You can just say, "I want to count orders over $50 that happened last month," and let Gemini figure out the filter logic and the time-based math. The update is already live for Google Workspace users with Gemini on the Business Standard and Plus, Enterprise tiers, as well as for those who subscribe to Google's AI Pro or Ultra plans. You can check by seeing if you've got the "Ask Gemini" spark icon in the upper right corner of your Sheet.