How to Use AI in Excel for Data Analysis: 3 Ways

You have a sheet with a few thousand rows of sales, survey answers or expenses, and you want to know what it says without building every PivotTable by hand. This guide shows how to use AI in Excel for data analysis with three tools: the free Analyze Data button, Copilot in Excel, and ChatGPT as an outside helper. It is written for everyday Excel users, not data scientists. Expect about 10 minutes to prepare your data and another 10 to 20 minutes to get useful answers back.

Before you start

• A Microsoft 365 subscription if you want Analyze Data or Copilot. Microsoft says Analyze Data is available to Microsoft 365 subscribers, and Copilot only appears if your subscription or your organization's settings include it.
• A modern file format. Save the workbook as .xlsx, .xlsm or .xlsb. Microsoft notes that Analyze Data does not work in compatibility mode with old .xls files.
• One clean table per sheet, with a single header row.
• A copy of the file if the data is sensitive and you plan to upload it anywhere outside Excel.

Stage 1: Get the data ready for AI

AI tools read your sheet the way a new coworker would. If the layout is confusing, the answers will be too. A few minutes here saves a lot of bad output later.

1. Put each record on its own row and each field in its own column. One order per row, one survey response per row, and so on.
2. Give every column a short, plain header in the first row, like "Order Date", "Region" or "Revenue". Microsoft's guidance asks for a single row of unique, non blank labels and warns against double header rows and merged cells.
3. Delete empty rows and columns inside the data, and move any notes or totals off to a different sheet.
4. Check your dates. Microsoft points out that Analyze Data treats text dates such as "2017-01-01" as text, so convert them to real Excel dates first.
5. Click anywhere in the data and press Ctrl+T to turn it into an Excel table. Confirm that "My table has headers" is checked.

Stage 2: Ask questions with Analyze Data

Analyze Data (it used to be called Ideas) is the quickest place to start, because it is built into Excel for Microsoft 365 on Windows and Mac.

1. Click a cell inside your table.
2. Go to the Home tab and select Analyze Data. A task pane opens on the right.
3. Scroll through the suggestions. Microsoft groups them into types such as Rank, Trend, Outlier and Majority, so you might see which region sells the most or which month broke the pattern.
4. Type your own question in the box at the top of the pane and press Enter, for example "total revenue by region" or "which product had the biggest drop last quarter".
5. When a result looks useful, insert it. Answers come back as tables, charts or PivotTables you can add straight to the workbook.

Keep the size limit in mind. According to Microsoft, Analyze Data does not currently handle datasets over 1.5 million cells, so filter big sheets down or copy a slice to a new sheet first.

Stage 3: Go deeper with Copilot in Excel

Copilot can do more than suggest charts. Microsoft's current help page says it can sort, filter and highlight data, write formulas, build charts and PivotTables, and point out trends and outliers, all using Excel's own built in features.

1. Open the workbook and select the Copilot icon in the lower right corner of Excel.
2. Pick a mode from the mode menu in the Copilot pane. Allow editing is the default and lets Copilot change the sheet. Plan lays out the steps first, and Chat only answers without touching your data. If you are nervous about edits, start in Chat only.
3. Ask a specific question that names your columns, such as "Using the Revenue and Region columns, show monthly revenue by region as a line chart."
4. Follow up in plain language: "Highlight any month where revenue fell more than 10 percent" or "Add a column that calculates profit margin."
5. If Copilot heads the wrong way, select Stop on the right side of the chat box and rephrase.

Platform matters. Microsoft lists full editing, chat and plan modes on Excel for Windows, Mac and the web. On iPhone and Android, Copilot is limited to chat for now, so do serious analysis on a computer.

If you only need help writing a tricky formula rather than analyzing a whole dataset, our guide to using ChatGPT for Excel formulas covers that job in detail.

Stage 4: Use ChatGPT when Copilot is not an option

No Copilot license? You can upload the spreadsheet to ChatGPT instead. OpenAI's data analysis help page lists support for Excel (.xls and .xlsx), CSV, PDF and JSON files.

1. Save a copy of the workbook and remove anything you would not want to share, such as names, account numbers or customer emails.
2. Attach the file in a ChatGPT conversation. OpenAI caps files at 512 MB, but spreadsheets and CSV files are limited to roughly 50 MB depending on row size, and you can add up to 10 files per conversation.
3. Start with an overview prompt: "Describe the columns in this file and flag any missing or odd values."
4. Then ask your real questions, one at a time. "Which five customers grew the most year over year?" works better than "analyze everything."
5. Ask for a chart when it helps. OpenAI says bar, pie, scatter and line charts are interactive, while other types are static images.
6. Click View Analysis under a reply to see the Python code ChatGPT ran. This is the easiest way to check its math.

If you are new to attaching files, our walkthrough on uploading a PDF to ChatGPT shows the same upload flow, and how to write better AI prompts helps you ask sharper questions.

Stage 5: Check the answers before you trust them

AI summaries sound confident even when they are wrong. Before a number goes into a report:

1. Recalculate one or two key figures yourself with a SUM or a quick PivotTable.
2. Look at the filters. Copilot and ChatGPT sometimes drop blank rows or a category without saying so.
3. Ask the tool to explain how it got a result, then compare that to what you expected.

Our guide to fact checking AI research has more habits that carry over to spreadsheets.

When the AI tools do not work

The Analyze Data button is missing or gray. Make sure you are on Excel for Microsoft 365, signed in with a subscription, and not in compatibility mode. Microsoft also notes that people on the Semi Annual Enterprise Channel may still see the old "Ideas" name. The full requirements are on Microsoft's Analyze Data support page.

Analyze Data says it found nothing. This usually points to messy structure. Recheck for merged cells, two header rows or text dates, then convert the range to a table and try again.

You cannot find the Copilot icon. Microsoft says Copilot may not be included with your Microsoft 365 plan or may be switched off by your organization. Its Copilot in Excel guide links to a page that shows which license you have. At work, ask your IT team.

ChatGPT will not read the file. Large workbooks often go over the roughly 50 MB spreadsheet limit. Delete unused sheets, save just the table you need as a CSV, and upload that instead.

One last tip: keep a short list of the prompts that gave you good answers. Next month, when the new data lands, you can paste the same questions and have a report in minutes. For more guides like this one, browse our AI section.