Artificial intelligence has arrived inside Excel — and it changes how fast you can work. Whether you use Microsoft 365 Copilot built directly into the Ribbon, or bring in ChatGPT as an external assistant, AI can generate formulas, explain errors, clean messy data, summarise datasets, build charts, and write VBA macros on your behalf. This module teaches you how to work effectively with AI tools in an Excel context — what to ask, how to prompt, how to verify the output, and which tasks AI handles best.
Several AI tools are now relevant to Excel users. Understanding what each one does — and does not do — prevents frustration and wasted time.
| Tool | Where It Lives | What It Does | Requires |
|---|---|---|---|
| Microsoft 365 Copilot | Inside Excel — Copilot pane (Home tab → Copilot button) | Analyses your actual spreadsheet data; generates formulas; creates charts and pivot tables; summarises data; answers questions about the open workbook | Microsoft 365 Copilot licence (Business or Enterprise add-on — paid separately) |
| ChatGPT (OpenAI) | Browser (chat.openai.com) or mobile app — used alongside Excel | Writes formulas on request; explains errors; generates VBA code; helps structure spreadsheet layouts; explains concepts; creates sample data | Free account (ChatGPT 3.5) or ChatGPT Plus subscription (ChatGPT 4) — does not access your actual Excel file unless you paste data in or use a plugin |
| Excel's Built-In AI Features | Inside Excel — various tabs and features | Ideas/Analyse Data (Home → Analyse Data); Flash Fill (automatic pattern detection); Recommended Charts; Recommended Pivot Tables; Error checking with explanations; Accessibility Checker; Smart Lookup | Standard Microsoft 365 subscription — most features available without Copilot |
| Claude (Anthropic) | Browser (claude.ai) — used alongside Excel | Writes and explains complex formulas; generates VBA; analyses pasted data; creates structured datasets; explains concepts; strong at detailed step-by-step instructions | Free tier available; Claude Pro subscription for extended usage |
| Google Gemini in Sheets | Google Sheets (not Excel) | Generates formulas and charts in Google Sheets — similar to Copilot but for the Google ecosystem | Google Workspace subscription |
Before spending money on Copilot or ChatGPT Plus, explore what Excel 2024 already offers through its built-in intelligent features — available to all Microsoft 365 subscribers.
Excel's most powerful built-in AI feature — it scans your dataset and automatically generates meaningful insights, charts, and pivot table suggestions.
Flash Fill watches what you type and detects patterns — then completes the remaining rows automatically. This is AI-powered data transformation with zero formula writing.
| Column A (source) | You type in B1 | Flash Fill completes B2:B5 |
|---|---|---|
| John Smith | Smith, John | Jones, Mary |
| Mary Jones | Nkosi, Thabo | |
| Thabo Nkosi | Dlamini, Ayanda |
How to trigger Flash Fill:
Flash Fill use cases: Extract first/last names; reformat phone numbers (0821234567 → 082 123 4567); capitalise text; extract domain from email addresses; combine columns; extract dates from long text strings.
When a cell shows #VALUE!, #REF!, #NAME?, #DIV/0! or another error:
Microsoft 365 Copilot is the full AI assistant embedded directly inside Excel. Unlike ChatGPT (which works externally), Copilot can read, analyse, and modify your actual open workbook — it sees your data, your formulas, and your structure.
| Task Category | Example Prompt | What Copilot Produces |
|---|---|---|
| Formula generation | "Add a column that shows each employee's salary as a percentage of the department total" | Writes the formula and offers to insert it into a new column — you click Add Column to apply |
| Data analysis | "Which product category had the highest average sales in Q3?" | Answers in plain English and optionally creates a chart or pivot table to visualise the answer |
| Chart creation | "Create a bar chart showing monthly revenue by region" | Generates the chart and inserts it into the workbook — you can then format it further |
| Pivot table | "Create a pivot table showing total sales by salesperson and by month" | Builds the complete pivot table on a new sheet — configured with the right rows, columns, and values |
| Sorting & filtering | "Sort this table by sales amount from highest to lowest" | Sorts the table instantly |
| Conditional formatting | "Highlight all cells in the Profit column where the value is negative in red" | Applies the conditional formatting rule — you can undo or modify it after |
| Data summary | "Summarise the key insights from this dataset in plain English" | Produces a written summary — useful for report introductions or management briefings |
| Data cleaning suggestions | "Are there any inconsistencies or missing values in this table?" | Identifies blank cells, inconsistent formats, duplicate rows, and outliers |
Even without a Copilot licence, external AI assistants like ChatGPT and Claude can dramatically accelerate your Excel work. The key difference is that these tools work alongside Excel in a browser — you describe your problem in text (and optionally paste data) and the AI responds with formulas, code, or step-by-step instructions that you then apply in Excel yourself.
The quality of the AI's response depends almost entirely on the quality of your prompt. Use this structure:
| Task | ❌ Poor Prompt | ✅ Good Prompt |
|---|---|---|
| VLOOKUP | "Write me a VLOOKUP" | "I have an Excel sheet where column A contains employee ID numbers and column B contains their names. On a second sheet, I have a list of IDs in column A. Write a VLOOKUP formula in Sheet2 B1 that looks up each ID in Sheet1 and returns the employee's name." |
| IF formula | "How do I use IF?" | "In Excel, column C contains monthly sales figures. Write an IF formula in column D that shows 'Target Met' if the value in column C is greater than or equal to 50000, and 'Below Target' if it is less." |
| Formula error | "Why is my formula wrong?" | "My Excel formula =VLOOKUP(A2,Sheet2!A:B,2,0) is returning #N/A even though I can see the value from A2 in Sheet2 column A. The values in Sheet2 A column were imported from a CSV. What are the likely causes and how do I fix each one?" |
| Data analysis | "Analyse my sales data" | "I have a spreadsheet with columns: Date (dd/mm/yyyy), Product, Region (North/South/East/West), Salesperson, and Revenue (Rand). I want to find which region had the highest total revenue in Q2 2025 (April–June). What formula or pivot table steps should I follow?" |
You can paste a portion of your Excel data directly into the chat window for the AI to analyse. However:
These are the situations where AI saves the most time — tested prompts for the most common Excel frustrations.
| Error | What to Ask AI |
|---|---|
| #N/A | "My VLOOKUP returns #N/A. The lookup value is text in one column and a number in the other. How do I fix it?" |
| #REF! | "I deleted a row and now several cells show #REF! — explain what happened and how to fix it without undoing the deletion." |
| #DIV/0! | "How do I modify =A2/B2 so it shows 0 or a dash instead of #DIV/0! when B2 is empty or zero?" |
| #VALUE! | "My SUM formula returns #VALUE! — what are all the possible causes and how do I diagnose which one it is?" |
| Circular Reference | "Excel is warning me about a circular reference in my formula. Explain what a circular reference is and how to find and fix it." |
| Wrong result, no error | "My formula =SUMIF(A:A,"January",C:C) returns 0 but I can see January values in column A. What could be causing this?" |
| Problem | AI Prompt to Use |
|---|---|
| Dates stored as text | "My date column in Excel shows dates but they are stored as text (left-aligned, not right-aligned). Write a formula to convert text dates in format 'DD/MM/YYYY' to proper Excel date values." |
| Numbers with spaces or commas | "Column B contains numbers formatted like '1 250 000' (with spaces as thousands separators). Write a formula to convert these to proper numeric values Excel can calculate with." |
| Inconsistent capitalisation | "Column A has names in mixed formats — some ALL CAPS, some all lowercase, some Title Case. Write Excel formulas to standardise them all to Proper Case." |
| Extra spaces | "Some cells in my spreadsheet have extra leading, trailing, or double spaces. What formula removes all excess spaces while keeping single spaces between words?" |
| Split one column into two | "Column A contains full names like 'Thabo Nkosi'. Write formulas to extract the first name into column B and the last name into column C." |
One of AI's best uses — breaking a complex requirement into a formula you couldn't write alone:
Writing VBA code is where AI provides arguably its greatest value for Excel users. Non-programmers can describe what they want in plain English and receive working, commented VBA code in seconds — code that would otherwise require hours of learning or hiring a developer.
If the first attempt doesn't work, describe the problem back to the AI:
AI is a powerful assistant but not infallible. Understanding its limitations makes you a more effective — and safer — user.
| Limitation | What to Do About It |
|---|---|
| Wrong formula syntax — AI may generate a formula that looks correct but has a syntax error or wrong argument order | Always test the formula on a few cells with known values before rolling it out. Compare the result to a manual calculation. |
| Version mismatch — AI may suggest XLOOKUP or LET or LAMBDA which are only available in Excel 365/2021+ | Tell the AI which version of Excel you have: "I use Excel 2019, not Excel 365 — avoid functions only available in 365." |
| Assumes wrong data structure — AI makes assumptions about your layout that may not match reality | Be explicit about column letters and row numbers: "Column A = names starting from A2; column B = sales starting from B2." |
| VBA bugs — AI-generated VBA may work on simple data but fail on edge cases (empty rows, special characters, very large datasets) | Always test on a backup copy. Ask AI to add error handling: "Add On Error GoTo ErrorHandler and show a message if anything goes wrong." |
| Hallucinated functions — AI occasionally invents function names that do not exist | If Excel shows #NAME? when you use an AI formula, the function name may be made up. Ask Excel's Help or Formulas tab to verify the function exists. |
| Outdated knowledge — ChatGPT's training has a knowledge cutoff; it may not know about the newest Excel features | For cutting-edge features (Python in Excel, Copilot-specific features), verify with Microsoft's official documentation at support.microsoft.com |
Microsoft has begun integrating Python directly into Excel cells — allowing you to write Python code (pandas, matplotlib, scikit-learn) that runs on Microsoft's cloud servers and returns results into your spreadsheet. This is a significant development for data analysis:
=PY( in a cell to open the Python editorQ1: You need to find which salesperson had the highest total sales in each region. You don't know which formula to use. How would you use an external AI tool like ChatGPT to get the answer, and what information must you include in your prompt?
✓ Open ChatGPT in your browser alongside Excel. In your prompt, include: (1) the column structure — e.g., "Column A = Salesperson name, Column B = Region, Column C = Sale amount, headers in row 1, data starts row 2"; (2) what you want — "I want to find which salesperson had the highest total sales in each region"; (3) any constraints — "I use Excel 365". ChatGPT will likely suggest SUMIFS to total by salesperson+region and MAXIFS to find the maximum — or suggest a pivot table approach. Copy the formula exactly, test it on known data, then apply it to the full dataset.
Q2: Your VLOOKUP is returning #N/A for some rows but not others. You have pasted the formula and a sample of the data into ChatGPT. It suggests the issue is "leading spaces". What Excel function do you use to fix this, and how would you apply it?
✓ Use TRIM() to remove leading and trailing spaces. Modify the VLOOKUP to wrap the lookup value: =VLOOKUP(TRIM(A2), Sheet2!A:B, 2, 0). If the lookup table itself has spaces, wrap the range column too — or use a helper column in the lookup table: =TRIM(A2) copied down, then reference that helper column. Alternatively, use Find & Replace to clean spaces: Ctrl+H → Find " " (space) → Replace with "" — but be careful not to accidentally remove spaces from inside names.
Q3: What is the difference between Microsoft 365 Copilot and ChatGPT when used with Excel, in terms of data access?
✓ Microsoft 365 Copilot reads your actual open Excel workbook directly — it can see all your data, column names, formulas, and structure, and can make changes directly to your spreadsheet (insert formulas, create pivot tables, apply formatting). ChatGPT has no access to your Excel file at all — it only knows what you type or paste into the chat window. This means Copilot is more powerful for working with your real data, but ChatGPT is more accessible (no expensive licence) and useful for generating formulas, explaining concepts, and writing VBA based on descriptions you provide.
Q4: A colleague has been pasting their full employee salary database (including ID numbers and salaries) into ChatGPT to get help with formulas. Why is this a problem and what should they do instead?
✓ This is a serious data privacy risk. Pasting personal information (ID numbers, salaries, employee data) into an external AI tool sends that data to remote servers outside the organisation's control. This likely violates South Africa's POPIA (Protection of Personal Information Act) and the organisation's data protection policies. The correct approach: (1) Replace real data with dummy/anonymised data for testing and prompting; (2) Describe the structure to the AI without pasting real values — "Column A has ID numbers, column B has salaries between R15,000 and R80,000"; (3) If working with real sensitive data, use only Microsoft 365 Copilot which has enterprise data protection agreements with Microsoft.
Q5: You use the AI-generated formula =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B) but Excel shows #NAME? error. What is the most likely cause?
✓ XLOOKUP was introduced in Excel 365 and Excel 2021 — it does not exist in Excel 2016, 2013, or 2019. The #NAME? error means Excel does not recognise the function name. Fix: tell the AI which Excel version you have and ask for an alternative. For Excel 2019 or older, the equivalent is: =IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0), "") — VLOOKUP achieves a similar result and is available in all Excel versions.
Q6: You want to use the built-in Excel AI feature that automatically detects patterns and suggests insights, charts, and pivot tables from your data without needing Copilot. What is this feature called and where do you find it?
✓ The feature is called "Analyse Data" (previously named "Ideas" in older versions). Find it on the Home tab → Analysis group → Analyse Data. Click inside your data table first, then click the button. A pane opens on the right showing automatically generated insights, suggested charts, and pivot table recommendations. You can also type specific questions in the search box within the pane to get answers about your data — for example "which month had the highest sales?" — and Excel generates a response with a supporting visual.