How to Use AI to Analyse Data in Excel

AI tools have made it much easier to get useful answers from your spreadsheet data without needing to be an Excel expert. Whether you want to identify trends, spot outliers, or generate a plain-English summary of what your data shows, there are now three practical tools you can use — and two of them are completely free. This guide covers each one and shows you exactly how to use them.

The Three Tools for AI Data Analysis in Excel

The tools worth knowing about are Microsoft Copilot (built into Excel, subscription required), ChatGPT (free, works via copy and paste), and Google Gemini (free alternative to ChatGPT). Each has different strengths and different requirements.

Option 1: Microsoft Copilot (Built Into Excel)

Copilot is the most seamless option because it’s directly integrated into the Excel interface. You don’t need to copy data anywhere — you ask questions inside Excel and Copilot reads your spreadsheet directly.

What you need

A Microsoft 365 Copilot subscription, which is an add-on to eligible business plans. It is not included in standard Microsoft 365 Personal or Family plans, although Microsoft is gradually expanding availability. If you have it, you’ll see a Copilot button on the Home tab in Excel.

How to use Copilot to analyse data

1. Make sure your data is formatted as an Excel Table (Insert > Table). Copilot only works with Tables, not plain ranges.
2. Click the Copilot button on the Home tab to open the Copilot panel.
3. Type your question or request in plain English.

Example prompts for Copilot

• “What are the top 5 products by total sales?”
• “Show me which months had below-average revenue.”
• “What percentage of orders came from each region?”
• “Are there any rows with missing values?”
• “Create a pivot table showing sales by category and month.”

Copilot will respond with a direct answer and, where relevant, offer to add a chart, column, or pivot table to your workbook. You can click to accept or reject each suggestion.

Option 2: ChatGPT (Free, Copy-Paste Workflow)

ChatGPT doesn’t connect to your spreadsheet directly. Instead, you copy your data, paste it into the chat, and ask questions. This sounds cumbersome, but in practice it’s quick and works well for datasets that fit in a chat window.

How to export data to ChatGPT

1. Select your data in Excel (including the header row).
2. Copy it (Ctrl+C).
3. Go to chat.openai.com and start a new chat.
4. Paste the data into the message box and add your question below it.

ChatGPT will read the pasted table and answer your question based on it. You can then ask follow-up questions in the same chat without re-pasting the data.

Useful analysis tasks for ChatGPT

Summary statistics

“Here is my sales data [paste table]. Can you give me the mean, median, minimum and maximum for the Revenue column, and the total revenue overall?”

Spotting anomalies and outliers

“Look at the Order Value column in this data. Are there any values that seem unusually high or low compared to the rest?”

Identifying trends

“Looking at the monthly totals in this table, is there an upward or downward trend? What months stand out?”

Creating a narrative summary

“Write a short plain-English paragraph summarising the key findings from this sales data. Focus on the most important trends and anything noteworthy.”

Comparing categories

“Which product category has the best average order value, and which has the most orders? Summarise the differences.”

Context limit for large datasets

ChatGPT has a context limit — it can only process a certain amount of text in one conversation. For very large datasets (thousands of rows), pasting the whole table may exceed this limit. The solution is to use a sample: paste 100–200 representative rows, or paste a pre-summarised version (for example, monthly totals rather than individual transactions). Ask ChatGPT to treat the sample as representative of the full dataset.

Option 3: Google Gemini

Google Gemini (gemini.google.com) works in the same way as ChatGPT for data analysis — copy your data, paste it in, ask questions. Gemini can also access Google Sheets directly if you use the Gemini integration within Google Workspace, which is useful if your data is already in Google Sheets rather than Excel.

For Excel users, ChatGPT and Gemini are effectively interchangeable for this kind of task. Try both and use whichever gives you better results for your data.

Practical Examples: What to Ask

Finding your best and worst performers

“Rank the salespeople in this table by total revenue and tell me who the top 3 and bottom 3 are.”

Identifying seasonal patterns

“Is there a seasonal pattern in this monthly sales data? Which months consistently perform best and worst?”

Checking data quality

“Review this table and tell me if there are any obvious data quality issues — missing values, inconsistent formatting, or values that don’t make sense.”

Generating a report summary

“Write a short executive summary of this quarterly sales data suitable for a business report. Highlight the three most important findings.”

Limitations to Know About

• ChatGPT and Gemini can make errors. Always verify any numbers or calculations they provide against your source data, especially for financial reporting.
• Context limits restrict dataset size. Use sampling or pre-aggregated data for large files.
• Copilot is limited to one workbook. It can’t pull data from external files or the web.
• None of these tools replace proper data analysis skills for complex statistical work — they’re most useful for quick answers and summaries.

When to Use Proper BI Tools Instead

AI analysis in Excel works well for answering specific questions about a dataset and getting quick summaries. But if you need ongoing dashboards, cross-source reporting, or analysis that updates automatically, you’ll get better results from dedicated business intelligence tools.

Microsoft Power BI connects to multiple data sources, refreshes automatically, and lets you build interactive dashboards. It integrates with Microsoft 365 and has a free desktop version. Tableau is the industry standard for data visualisation in larger organisations. Both have a learning curve, but for regular reporting on business data they’re far more powerful than one-off Excel analysis.

For most home users and small businesses, though, ChatGPT or Copilot in Excel will handle the vast majority of data analysis questions — and they’re ready to use right now.