How to use AI in Google Sheets to automate analysis and reports

AI works much better with tidy data. First things first:

1. Put a clear header row in the first row (Date, Category, Amount, Client).
2. Make sure each column has one type of data (dates with dates, numbers with numbers).
3. Remove fully empty rows or obvious duplicates.

It doesn’t have to be perfect: in step 3 the AI helps you clean up what’s left.

This path costs nothing and works with any sheet (Google Sheets or Excel).

1. In your sheet, select the data range and copy it (Ctrl + C on WindowsMac with Cmd + C).
2. Open Claude (claude.ai) or ChatGPT (chat.openai.com) and paste the table into the chat.
3. Say what you want. For example:

“Here are my sales for the month. Summarize income and expenses by category, point out the 5 largest amounts, and tell me if anything looks off compared to the rest.”

4. Copy the answer back into your sheet, or ask it to return the result as a table so you can paste it straight in.

For large files, you can export the sheet to CSV (File > Download > CSV) and upload the file to the chat.

This is the part that saves the most time. Instead of remembering the syntax, describe the formula and let the AI write it.

In Claude or ChatGPT, paste a couple of sample rows and ask:

“In Google Sheets, give me the formula to sum column C only when column B says ‘Paid’. My data starts in row 2.”

It returns something like =SUMIF(B2:B, "Paid", C2:C) with the explanation. Copy it into your cell and you’re done. It works for VLOOKUP, pivot tables, counting, averaging and cleaning text.

Tip: if a formula gives an error, copy it along with the error message and ask “why is it failing and how do I fix it?”.

If you want the AI inside the sheet (without leaving for another chat), Google has Gemini built in. It’s paid: it comes with Google AI Pro (personal account) or Google Workspace Business Standard and up (businesses). Confirm prices and availability on the official page.

With Gemini active you can:

1. Open the Gemini side panel (the star icon in the top right) and ask it: “create a table with these columns” or “summarize this sheet”.
2. Use the =AI function in a cell: =AI("classify this text as complaint or praise", A2) and the AI fills the result down.
3. Use Smart Fill: type a couple of examples and Sheets completes the pattern on its own.

Note: the =AI function and the panel roll out by region and plan. If you don’t see them, check that your account includes Gemini.

With your data cleaned and analyzed, ask the AI for the final report. In Claude or ChatGPT:

“With this data, write me a one-page monthly report in plain language: total sales, comparison with last month, the 3 categories that grew the most, and one recommendation.”

And for the visual dashboard:

“Tell me step by step which charts to create in Google Sheets to see income versus expenses by month and the margin.”

You review, adjust and present. The heavy lifting is already done by the machine. If you do this often, also see how to automate marketing reports with AI and how to create presentations with AI for the next step.

• The formula shows #ERROR! or #N/A: copy the formula and the error into the chat and ask for the fix. It’s almost always a mistyped range.
• The AI “made up” a number: give it less data at a time and ask it to show how it got the result. Always double-check by hand.
• I don’t see the Gemini panel or the =AI function: your account doesn’t include Gemini, or it’s not available yet in your region. Use the free path from step 2.