How to Use AI in Excel: Copilot, ChatGPT, and Smart Automation for Faster Analysis

Introduction

Stop wasting hours writing formulas manually. AI is now built directly into Excel and it can cut your analysis time in half.

This guide covers the five most practical AI tools in Excel today, with real examples and formulas you can apply immediately.

Why AI in Excel Is a Game-Changer

Most Excel users are still working the same way they did five years ago: writing formulas by hand, fixing errors one by one, and copy-pasting between sheets.

Meanwhile, Microsoft has shipped one of the most powerful AI integrations in any productivity tool. The problem is not lack of features. It is awareness and adoption.

Copilot, Analyse Data, and Python in Excel can handle in seconds tasks that would normally take 30 to 60 minutes.

Step-by-Step: Using AI in Excel

Step 1: Activate Microsoft Copilot in Excel

Copilot is available in Excel for Microsoft 365 subscribers. Here is how to get started:

1. Open Excel and load your dataset.

2. Click the Copilot button in the Home ribbon tab, far right.

3. A Copilot panel opens on the right side of your screen.

4. Type a plain-English request such as: Summarise total sales by region and show a chart.

Important: Copilot works best on structured Excel Tables. Press Ctrl + T to convert your data range first.

Pro Tip: Be specific. Instead of analyse my data, try: Calculate the average order value per product category for Q1 2024, sorted highest to lowest.

Step 2: Use Analyse Data for Instant Insights

The Analyse Data feature uses AI to surface patterns automatically, without you needing to ask a specific question.

1. Select any cell inside your dataset.

2. Go to Home > Analyse Data in the Analysis section of the ribbon.

3. A sidebar appears with auto-generated charts, pivot table suggestions, and trend insights.

4. Click any suggestion to insert it directly into your workbook.

Example: on a 5,000-row sales dataset, it might surface: Revenue in the North region grew 23% vs. the same period last year.

Step 3: Use ChatGPT or Claude to Write Complex Formulas

You do not need Microsoft 365 to use AI for Excel. External tools like ChatGPT and Claude write formulas on demand.

1. Describe what you need in plain English.

2. Copy the suggested formula into Excel.

3. Test it on a small sample before applying to the full dataset.

Example prompt: Write an Excel formula that looks up a customer ID in column A, finds their most recent purchase date from a table on Sheet2, and returns that date.

=MAXIFS(Sheet2!C:C, Sheet2!A:A, A2)

Quick time savings comparison:

• Nested XLOOKUP: 5 to 10 min manually vs. 10 seconds with AI

• Dynamic pivot summary: 15 to 20 min manually vs. under 1 minute with AI

• Cleaning inconsistent data: 30 to 60 min manually vs. 5 minutes with AI

• Conditional formatting rule: 5 to 15 min manually vs. 30 seconds with AI

Step 4: Automate Data Cleaning with Flash Fill and AI

Data cleaning is often the most time-consuming part of any project. Excel built-in AI tools cut that time dramatically.

Flash Fill detects patterns automatically. If column A has full names like John Smith, type John in column B and Jane in the next row, then press Ctrl + E. Excel fills the rest with first names extracted from column A.

For complex cleaning tasks, describe the problem to an AI tool and paste the resulting formula. Example: standardise text casing and remove extra spaces.

=PROPER(TRIM(CLEAN(A2)))

This removes extra spaces, non-printable characters, and converts text to proper case in one step.

Common Mistake: Flash Fill only works reliably when source data follows a consistent format. Always review output on a sample row before applying to the full dataset.

Step 5: Run Python-Powered Models Directly Inside Excel

Python in Excel is rolling out for Microsoft 365 users. It lets you run Python code including machine learning models directly inside a spreadsheet cell.

1. Select a cell and type =PY( to open the Python editor.

2. Write or paste your Python code.

3. Press Ctrl + Enter to run. Results appear directly in your spreadsheet.

Example using pandas to group revenue by region:

import pandas as pd df = xl('SalesTable[#All]', headers=True) df.groupby('Region')['Revenue'].sum()

This unlocks forecasting, clustering, and anomaly detection, all within the familiar Excel interface.

Practical Example: AI-Assisted Monthly Sales Report

Scenario: you receive a raw CSV from your CRM every month. 10,000 rows, messy formatting, inconsistent region names.

1. Clean: use Flash Fill and a SUBSTITUTE formula to standardise region names.

2. Structure: convert to an Excel Table with Ctrl + T and add clear column headers.

3. Analyse: ask Copilot to show total revenue by sales rep, sorted highest to lowest.

4. Discover: let Analyse Data surface unexpected patterns or outliers automatically.

5. Export: use a ChatGPT-generated macro to copy the summary to a new formatted sheet.

What used to take 2 to 3 hours now takes under 30 minutes.

Bonus Tips and Common Mistakes

Tips to get the most from AI in Excel:

• Always convert your range to an Excel Table before using Copilot for best results.

• Use descriptive column headers like Revenue_EUR instead of Col_C. AI reads them to understand your data.

• Save useful AI-generated formulas in a personal reference sheet for easy reuse.

• Combine Copilot with Power Query for a fully automated pipeline from source to report.

Common mistakes to avoid:

• Vague prompts: always specify metric, time period, and output format when using Copilot.

• Skipping validation: test AI formulas on a sample row before applying to the full dataset.

• Blind trust: Copilot can misinterpret complex logic. Always review suggestions critically.

• Poor headers: weak column names are the biggest cause of inaccurate AI output in Excel.

Download the Practice File

We have prepared a free sample workbook so you can follow along with every example in this guide. The file contains four sheets:

• SalesData: a 50-row structured sales table, pre-formatted as a Copilot-ready Excel Table with descriptive headers

• FormulaExamples: working MAXIFS, PROPER, TRIM, and SUMIFS formulas from this article, ready to inspect and copy

• FlashFillPractice: a before-and-after sheet where you can practise Flash Fill with Ctrl + E on real name data

• PythonSnippet: copy-paste Python code snippets ready to use inside the =PY() editor in Microsoft 365

All data in the file is clearly labelled as demo/sample content. The file requires Excel 2019 or Microsoft 365 for full formula compatibility.

Download AI_in_Excel_Sample.xlsx from the File Share section of this site.

Final Thoughts

AI has not replaced the need to understand Excel. It has made Excel more powerful than ever.

Start small: pick one task you repeat weekly and try it with AI. Once you see how fast it works, the old way will feel impossibly slow.

Subscribe for more Excel and Power BI tips. Explore our templates and tools in the Resources section.