How to Use AI in Microsoft Excel (Copilot, ChatGPT and Plugins to Analyze Data, Build Formulas and Automate Office Tasks)

If you have Microsoft 365 Personal or higher (with Copilot):

1. Open the Excel desktop app (Microsoft 365 version, not Office 2021 or earlier).
2. Open or create a workbook with data. Copilot works best when data is formatted as a table: select your data and press Ctrl + T to convert it to a table.
3. Click the “Copilot” button in the ribbon (Home tab, right side). A side panel opens.
4. You can now type what you need in plain language (see following steps).

If you don’t see the Copilot button, update Excel to the latest version from Home > Account > Office Updates. Confirm that your Microsoft 365 plan includes Copilot at microsoft.com.

If you use ChatGPT (free, without Copilot):

1. Open chatgpt.com in your browser.
2. Keep Excel open in another window. The flow is: describe the problem in ChatGPT, get the formula or analysis, and apply it manually in your sheet.
3. You don’t need to install anything extra.

This is the most popular use case: asking AI for the exact formula without having to search Google or remember the syntax.

With Copilot (integrated in Excel):

In the Copilot panel, type what you need:

Create a formula that sums the sales in column C only when column B is "North".

In column E, calculate the percentage of each value in column D relative to the total of D.

Find the 5 highest values in column F and highlight them in yellow.

Copilot shows you the formula and explains it. Accept with the “Insert” button and it applies it directly to your sheet.

With ChatGPT (without Copilot):

1. Describe your sheet and what you need:

I have an Excel sheet with these columns: A (Date), B (Region), C (Product), D (Sales).
What formula do I use to sum the sales in column D only when column B is "North" and column C is "Shoes"?
Briefly explain how the formula works.

2. ChatGPT gives you the formula (likely a SUMIFS). Copy and paste it directly into the Excel cell.
3. If it gives an error, copy the error message and paste it in ChatGPT: “Excel gives me this error: #VALUE! What’s wrong?”

Formulas most often requested from AI:

Use case	You ask AI
Look up a value in another table	”formula to look up in another sheet by ID” → VLOOKUP or XLOOKUP
Count with condition	”count rows where column X = Yes” → COUNTIF
Date math	”days between two dates” → DAYS or DATEDIF
Text	”extract first name from full name column” → LEFT + FIND

Beyond formulas, AI can analyze your tables and give you conclusions in plain language, without you having to interpret the numbers yourself.

With Copilot in Excel:

In the Copilot panel, write direct questions about your data:

What are the 3 best-selling products this month?

Summarize the sales trends by region in one or two sentences.

Are there any outliers or anomalies in the sales column?

Copilot reads your table and responds in natural language, and can also create charts automatically:

Create a bar chart with sales by region.

With ChatGPT (without Copilot):

1. Copy a portion of your data (don’t paste confidential data into external services).
2. Paste it in ChatGPT with this instruction:

These are my sales data for [month]. Analyze them and tell me: (1) which is the best-selling product, (2) which region performs best, (3) what trend stands out.

[Paste your data here, in table or CSV format]

3. ChatGPT analyzes the data and gives you the summary. You can also ask it to generate macro code to apply something to your sheet.

If you do this kind of analysis frequently, also check out the tutorial How to Automate Marketing Reports with ChatGPT to take automation one step further.

Excel macros (VBA) automate repetitive actions: formatting a report every week, generating a summary from one sheet to another, renaming sheets by date. They used to require programming knowledge; with AI, you just describe what you want.

With Copilot in Excel:

Create a macro that takes the data from the "Sales" sheet, calculates the total by region and copies the results to a new sheet called "Summary".

Copilot generates the VBA code and can apply it with one click.

With ChatGPT:

1. Describe the repetitive task you want to automate:

I want an Excel VBA macro that does the following:
1. Goes through all the rows in column A.
2. If the column B cell in that row says "Paid", highlights the entire row in green.
3. If it says "Pending", highlights the row in yellow.

Briefly explain how to run the macro once I have it in Excel.

2. ChatGPT gives you the complete VBA code with instructions on how to insert it.

How to run a macro in Excel:

1. Press Alt + F11 to open the Visual Basic editor.
2. Click Insert > Module.
3. Paste the code from AI.
4. Press F5 or close the editor and go to View > Macros > Run macro.

If you see a “Macros disabled” message, go to File > Options > Trust Center > Trust Center Settings > Macro Settings and enable “Enable all macros” (only for trusted files).

Problem	Fix
The Copilot button doesn’t appear in Excel	Update Excel to the latest version (Home > Account > Office Updates); also confirm your Microsoft 365 plan includes Copilot at microsoft.com
The formula AI gave me throws an error	Copy the error message and paste it back in ChatGPT or Copilot: “It gives this error: #NAME?, what’s wrong?”
ChatGPT gives me the formula in English but my Excel is in Spanish	Ask: “Give me the formula in Spanish Excel (e.g., SUMAR.SI instead of SUMIF)“
The macro doesn’t work as expected	Describe to AI exactly what it does differently from what you wanted: “The macro works but also highlights the header row. How do I fix it?”
Copilot doesn’t understand my question about the data	Make sure your data is formatted as a table (Ctrl + T) and columns have descriptive headers; Copilot needs that structure to read the data correctly