Training

AI for Excel: formulas, clean-up and analysis with Copilot, ChatGPT and Gemini

AI for Excel helps you write formulas, explain inherited ones, clean data and summarise a table. This guide compares Copilot, ChatGPT and Gemini task by task, with ready-made requests and the four checks that catch mistakes.

AI for Excel: three ways of working

Using AI for Excel needs no programming. You explain in plain words what you want from a sheet: a formula that joins two tables, a summary by client, a list of odd amounts. The tools differ in where they do the work.

Copilot sits inside Excel. With a Microsoft 365 Copilot licence it can apply formulas across several sheets, create PivotTables and charts, and point out trends or outliers in the workbook itself, according to Microsoft support. It works this way in Excel for Windows, Mac and the web.

ChatGPT and Gemini work outside. You upload the file, they analyse it and return a summary, a chart, a table to download or the formula to paste into Excel. According to OpenAI's help centre, ChatGPT accepts .xls, .xlsx and .csv files, and Google says Gemini reads XLSX files uploaded from your computer or from Drive.

Copilot in Excel, ChatGPT or Gemini: which to use for each task

Each tool's features change often. This is the most stable division of labour.

  • Copilot in Excel: changes to the workbook itself

    Adding a calculated column, building a PivotTable or a chart linked to the data. Useful when the result must stay in the file the team uses. Each person needs the paid licence.

  • ChatGPT for Excel: analysing an exported file

    Comparing two months, spotting duplicates or summarising an export from your accounts system. It returns tables you can download as CSV. Its analysis environment cannot reach the web, so upload all the data it needs.

  • Gemini: if your company runs on Google

    It analyses an Excel file uploaded from Drive or your computer. If your team uses Google Workspace, Gemini in Google Sheets is usually the more natural route.

  • Any assistant: a single formula

    To write or explain one formula, there is no need to upload the file. Describe the columns, give an example row and say which version of Excel you use.

Five ready-made requests for AI in Excel

Change the column and sheet names to yours. The more specific the context, the less you have to fix.

  • Write a lookup formula

    "In the Orders sheet, column A holds the product code. In the Prices sheet, A is the code and C the price. Give me a formula that brings in the price and shows 'not found' if it is missing." Expected answer: =XLOOKUP(A2,Prices!A:A,Prices!C:C,"not found").

  • Sum with several conditions

    "Add up the amounts in column D where the region (B) is Leeds and the type (C) is Red." Expected answer: =SUMIFS(D:D,B:B,"Leeds",C:C,"Red"). Ask it to explain each argument.

  • Explain an inherited formula

    Paste the formula and ask: "Explain step by step what this does, what happens if a cell is empty and how you would simplify it." Very handy with sheets left by someone who has moved on.

  • Clean up an export

    "This column has client names spelt several ways. Suggest a single list and tell me which rows you would change." Review the suggestion before applying it.

  • Summarise and flag the unusual

    "Summarise sales by client and by month, and list the amounts that look unusual for each client, with the reason." Treat that list as leads to check.

How to prepare the file before you ask anything

OpenAI's help centre gives much the same advice any Excel expert would. It applies to all three tools.

  1. 01

    One table per sheet

    Put unrelated tables on separate sheets. Remove blank rows and columns that split the data.

  2. 02

    Clear headers in the first row

    Descriptive column names in plain words: "Order date", not "ORD_DT". No merged cells.

  3. 03

    One record per row

    One order, client or invoice per row, with the same date and amount format all the way down each column.

  4. 04

    Remove what should not leave the company

    If you use ChatGPT or Gemini, delete or replace personal data the analysis does not need, and use the approved business account.

  5. 05

    Work on a copy

    Save the original before Copilot or you make changes. You can then compare and roll back.

Four checks before you trust the result

AI gets Excel right often, and when it is wrong it sounds just as sure. These checks take a few minutes.

  • A total you already know

    Compare the result with a reliable figure, such as the month's sales in your accounts. If it does not match, find out why before going further.

  • Three rows by hand

    Work out three rows by hand or on a calculator, including one awkward case: a return, a zero amount, a mistyped code.

  • The full range

    Check that the formula covers every row, including those added next month, and does not skip hidden or filtered rows.

  • Language and separators

    A formula written for an English Excel will fail in a Spanish or French version, which use other function names and semicolons. If a colleague abroad gets an error, ask for the version that matches their Excel.

Example

An Edinburgh wine wholesaler speeds up its monthly report

A hypothetical wine wholesaler in Edinburgh with nine staff produces a monthly margin report by client and region. The office manager combines the order export from the business system with the price list, and it takes most of a working day.

First she cleans the export using the five preparation steps. Then she asks ChatGPT, on the company account, for formulas that bring in each product's cost and calculate the margin, and asks it to explain them. She pastes them into Excel and checks them against three orders worked out by hand, one of them a return.

As she has a Copilot licence, she asks Copilot in Excel for a PivotTable of margin by region and month and a chart. Finally, she asks for a list of clients whose margin has dropped well below their average for the previous three months, and goes through it with the sales director.

To measure it, she logs the time the report took in the three months before and the three months after, checks included. She also notes how often the total failed to match the accounts and why. If the checks keep finding the same kind of error, she adjusts the requests and writes them down for whoever covers her holidays.

From one-off formulas to a team method

AI in Excel saves the most time when the team shares the same ready-made requests, preparation rules and checks. Without that method, each person learns alone and the same mistakes come back.

Obinova is an AI agency based in Barcelona and Paris. We train your teams on ChatGPT, Claude, Gemini and Microsoft Copilot using your own spreadsheets and tasks, on a dedicated video platform or in person at your company. They leave with requests and checks fitted to their work.

Frequently asked questions

What is the best AI for Excel?

It depends on where you want the result. If it must stay in the workbook, as columns, PivotTables or charts, Copilot works inside Excel but needs a paid licence. To analyse an exported file or write a formula, ChatGPT and Gemini do the job well. Quality depends mostly on how clean the table is and how specific the request is.

How do I use Copilot in Excel?

Open the workbook in Excel for Windows, Mac or the web with an account that has a Microsoft 365 Copilot licence, and open the Copilot pane. Ask in plain words for what you need, such as a calculated column, a PivotTable or a summary of trends. Work from a table with clear headers and review each change before sharing the file.

Can ChatGPT read an Excel file?

Yes. According to OpenAI's help centre, ChatGPT accepts .xls, .xlsx and .csv files, analyses them and can return charts and tables to download as CSV. It works best with one header row, one record per row and no mixed tables on the same sheet. With customer or staff data, use your approved business account.

Is Copilot in Excel free?

Copilot Chat, at no extra cost on eligible Microsoft 365 plans, works with files you upload. For Copilot to change the workbook inside Excel, with formulas, PivotTables and charts, each person needs a Microsoft 365 Copilot licence. Microsoft changes its plans often, so check what yours includes.

Does AI get Excel formulas wrong?

Yes, sometimes, and with complete confidence. The usual errors are incomplete ranges, functions in the wrong language, wrong separators and special cases left unhandled, such as empty cells. Always check the result against a known total and three rows worked out by hand before using it in a report.

Get your team working with AI in Excel

Book a free 30-minute video call. We will look at two or three of your regular spreadsheets and suggest training built on your own data and tools.

Free 30-minute diagnosis →
Free diagnosisWhatsApp

WhatsApp · Reply within 2 hours during business hours