How to Use AI in Excel: A Complete Step-by-Step Guide

Excel has been around for decades, but the way people use it is changing fast.

AI tools now make it possible for anyone to write complex formulas, clean messy data, build pivot tables, analyse thousands of rows, and even generate VBA macros, all without needing to memorise formulas or code.

There are four different ways you can bring AI into your Excel workflow, and each works differently depending on what you need and what tools you have access to.

This guide covers all four methods, with step-by-step instructions, real prompts you can use immediately, and clear guidance on which approach fits your situation best.

What Is AI in Excel?

AI in Excel refers to using artificial intelligence to perform tasks inside or alongside Excel. This includes writing formulas, cleaning and organising data, analysing datasets, building pivot tables, creating charts, writing VBA macros, and summarising information.

The key shift AI brings is this: instead of needing to know the exact syntax of a function or the right sequence of menu clicks, you simply describe what you want in plain English, and the AI determines how to do it.

There are four main ways people use AI with Excel today:

ChatGPT (copy-paste method): You go to ChatGPT, describe what you need, copy the formula or code it provides, and paste it into Excel. No plugins, no installation, and it works on any version of Excel.

ChatGPT (file upload method): You upload your actual Excel or CSV file directly into ChatGPT and ask it to analyse, clean, or summarise your data. This requires ChatGPT Plus.

Microsoft Copilot: Microsoft’s built-in AI assistant lives directly inside Excel’s interface. You type prompts in a sidebar and Copilot acts on your spreadsheet directly. This requires a Microsoft 365 subscription.

AI add-ins: Third-party tools such as GPT for Work install inside Excel and allow you to use AI directly in your cells through functions. These work independently of Copilot and often offer free starter plans.

Here is a quick comparison of all four methods so you can decide where to start:

Method	What It Is	Best For	Requires
ChatGPT (copy-paste)	Use ChatGPT to write formulas or macros, then paste into Excel	Formulas, VBA macros, quick help	Free ChatGPT account
ChatGPT (file upload)	Upload your .xlsx or .csv file directly into AI for analysis	Data analysis, cleaning, charts	ChatGPT Plus ($20/mo)
Microsoft Copilot	AI built into Excel’s ribbon and sidebar	All-in-one native workflow	Microsoft 365 + OneDrive
AI Add-ins	Third-party tools like GPT for Work that add AI inside Excel cells	Bulk processing, no Copilot needed	Add-in install (many free)

How to Use AI in Excel

This is the main section of the guide. Below you will find detailed steps for each of the four methods, including what to click, what to type, and example prompts you can copy and use immediately.

Method 1: Use AI Add-ins Inside Excel

AI add-ins install directly inside Excel and bring AI into your spreadsheet cells through functions. 

Instead of switching to another tool, you write an AI prompt as a cell formula and the result appears right in your sheet. This is the best method for bulk processing large datasets and for users who do not have Copilot access.
How to install an AI add-in

1. In Excel, go to the Insert tab in the ribbon.
2. Click Get Add-ins or Add-ins depending on your Excel version.
3. Search for the tool you want, for example GPT for Work, Formula Bot, Ajelix, or GPTExcel.
4. Click Add and follow the setup steps.
5. Most add-ins will add a new button or tab to your ribbon. Some require you to connect your own API key during setup.

You can write AI prompts directly as cell formulas and apply them down entire columns at once. If you have 5,000 rows of customer feedback and need each one classified, summarised, or translated, you write one formula and copy it down. The AI processes every row automatically.

Here are some example formulas you can use once an add-in is installed:

Prompt:  =GPT(“Classify this customer feedback as Positive, Negative, or Neutral. Reply with one word only.”, A2)

Prompt:
=GPT(“Extract only the city name from this address string. Reply with the city name only.”, A2)

Prompt:
=GPT(“Translate this product description from English to Spanish.”, A2)

Prompt:
=GPT(“Summarize this customer review in one sentence.”, A2)

Write the formula once, drag it down the column, and the add-in processes every row in the background.

Note: Most AI add-ins require connecting your own API key.
Costs are typically very low for standard tasks but check each tool’s pricing before running large batches.

Method 2: Use ChatGPT to Write Formulas and Macros (No Plugin Needed)

This is the most accessible method and works for everyone, even on the free version of ChatGPT. You do not need to install anything or have a special subscription. The idea is simple: you ask ChatGPT to write a formula or piece of code, then paste it into Excel yourself.

This method is best for writing formulas you do not know, understanding formulas you have inherited, generating VBA macros, and getting step-by-step instructions for Excel tasks.

• Go to chat.openai.com and sign in or create a free account.
• Open your Excel file in a separate window.

How to get a formula from ChatGPT

1. First, look at your spreadsheet and clearly identify what you want to calculate.
2. Next, note the relevant column letters and row numbers. For example, Column A might contain product names and Column B might contain sales figures.
3. Then open ChatGPT and write a clear prompt explaining your column structure and what you want to calculate.
4. ChatGPT will provide a formula along with an explanation of how it works.
5. Click the copy button on the formula code block in ChatGPT.
6. Go back to Excel, click the cell where you want the formula, paste it using Ctrl + V, and press Enter.
7. Finally, test the formula on a few rows to make sure it works correctly.

Example prompts you can use

Prompt:
Write an Excel formula to calculate total sales in column D where the region in column C is “West. My data starts at row 2.

Prompt:
I have customer IDs in column A and need to look up their email addresses from a separate table where column F has IDs and column G has emails. Write the XLOOKUP formula.

Prompt:
Write a formula that checks if the value in column E is greater than 1000. If it is, return “High”. If it is between 500 and 1000, return “Medium”. If it is below 500, return “Low”.

Prompt:
I have full names in column B in the format “Last Name, First Name”. Write a formula that extracts just the first name into column C.

Prompt:
Write an Excel formula to calculate the average sales for Q1 only. Column A has dates and column D has sales values.

How to get a VBA macro from ChatGPT

ChatGPT can write complete VBA macros based on a plain English description. Here is how to use them:

• First, describe the task you want to automate in detail so ChatGPT understands exactly what you need.
• Then copy the VBA code that ChatGPT provides.
• Open Excel and press Alt + F11 to open the Visual Basic Editor.
• In the editor, click Insert, then choose Module.
• Paste the copied VBA code into the module window.
• Finally, press F5 or click Run to execute the code.

Example prompts for macros

Prompt: Write a VBA macro that copies all rows where column C says ‘Pending’ to a new sheet called ‘Pending Items’.

Prompt: Write a VBA macro that applies bold formatting to the header row, adds alternating row colors to the data, and auto-fits all column widths.

Prompt: Write a VBA macro that loops through all rows, checks if the value in column D is blank, and highlights those rows in yellow.

Note: Always test macros on a copy of your file first. Macros cannot be undone with Ctrl+Z.

How to Use ChatGPT to Explain or Fix an Existing Formula

If you have inherited a spreadsheet with formulas you do not understand, just paste the formula into ChatGPT and ask it to explain what it does.

Prompt: Can you explain what this Excel formula does?

=IFERROR(INDEX($G$2:$G$100,MATCH(1,(A2=$E$2:$E$100)*(B2=$F$2:$F$100),0)),””)

ChatGPT will break it down function by function in plain English. 

You can also paste a formula that is throwing an error and ask it to find and fix the problem.

Method 3: Upload Your Excel File to ChatGPT for Data Analysis

If you want to skip formulas entirely and simply ask questions about your data, this is the method to use.
Instead of writing formulas yourself, you attach your Excel or CSV file directly to an AI chat and ask it to analyse the data.

• First, open ChatGPT and start a new chat.
• Click the paperclip or attachment icon in the message input bar.
• Select your .xlsx or .csv file and wait for it to upload.
• In the same message box, type a clear prompt describing what you want ChatGPT to do with the data.
• Press Enter. ChatGPT will show an “Analysing” or “Thinking” indicator while it processes the file.
• Finally, review the response. You can ask follow-up questions in the same chat to explore the data further.

Example prompts for file analysis

Prompt: I’ve uploaded a sales report for Q3. The columns are Date, Product Name, Region, Units Sold, and Revenue. Please give me a summary of total revenue by region.

Prompt: Here is my customer data file. Can you identify which customers have not placed an order in the last 90 days? The order date is in column C.

Prompt: Please clean this dataset. Remove any duplicate rows, standardise all dates in column A to MM/DD/YYYY format, and flag any rows where column D is blank.

Prompt: Create a bar chart showing monthly revenue trends from this data. The date column is column A and revenue is column E.

Prompt: What are the top 5 performing products by total units sold? Summarise the results in a table.

Note: ChatGPT reads your file as static values. It cannot recalculate live Excel formulas. If your file is formula-heavy, export it with values only (Paste Special → Values) before uploading.

Method 4: Use Microsoft Copilot (Built Into Excel)

Microsoft Copilot is an AI built directly into Excel’s interface. Unlike the methods above, Copilot acts on your spreadsheet in real time.

You type a prompt in the sidebar and Copilot makes changes, creates pivot tables, writes formulas, builds charts, and cleans data directly in your file without requiring copy-and-paste.

Copilot is the most seamless of the four methods but also the most restricted in terms of access.

• Open your Excel file and make sure it is saved to OneDrive. You can check this by looking for the AutoSave toggle in the top-left corner and turning it on.
• Next, click the Home tab in the ribbon.
• On the right side of the ribbon, click the Copilot button (sparkle icon).
• The Copilot sidebar will open on the right side of your screen.
• Finally, type your request in the chat box at the bottom of the sidebar and press Enter.

Note: If you do not see the Copilot button, your account may not have Copilot enabled. Check your plan at account.microsoft.com or contact your IT administrator.

Using Copilot to generate formulas

Type a plain English description into the sidebar and Copilot will write the formula and offer to insert it directly into your selected cell.

Prompt: Calculate the total revenue for all rows where the Region column says ‘North’. My data is in columns A through E.

Prompt: Write a formula that looks up the customer name in column A and returns their total spend from the summary table on Sheet2.

Prompt: Flag every row where the profit margin in column F is below 15% with the word ‘Review’. 

Using Copilot to clean data

Prompt: Remove all duplicate rows from this dataset based on the values in column B.

Prompt: Standardise all entries in column D. Right now, some say ‘USA’, some say ‘United States’, and some say ‘US’. Change them all to ‘United States’.

Prompt: Find all blank cells in the dataset and add the text ‘Missing’ in a new column called Data Status.

Note: Copilot always shows a preview before applying any destructive changes. You will see a confirmation step before anything is deleted or overwritten.

Using Copilot to analyse data and build pivot tables

Prompt: Which product category generated the most revenue in Q2? Show me the breakdown.

Prompt: Create a pivot table showing total sales by region and product category. Put it on a new sheet.

Prompt: Sort the pivot table by highest revenue first and add a slicer so I can filter by year.

Prompt: What trends do you see in the monthly sales figures in column D?

Using Copilot to create charts

Prompt: Create a line chart showing monthly revenue trends for 2025 using the data in columns A and E.

Prompt: What chart type would best represent this data and why?

Using Copilot to write VBA macros

Copilot can write macros just like ChatGPT, but it inserts them directly into the Excel VBA editor for you.

Prompt: Write a macro that highlights every row where the value in column C is greater than 500 in green.

Prompt: Write a macro that copies the contents of Sheet1, pastes it into a new sheet, and saves the file with today’s date in the filename.

Benefits of Using AI in Excel

Here is a clear picture of what actually changes when you bring AI into your Excel workflow:

Benefit	What It Means in Practice
No formula memorisation	Describe what you want in plain English and AI writes the formula, including complex ones like XLOOKUP, SUMIFS, and INDEX MATCH
Faster data cleaning	Tasks like removing duplicates, fixing formats, and standardising text that take 30 to 45 minutes manually are done in seconds
Accessible to non-experts	Users who have never written a macro or built a pivot table can now do both through a simple typed request
Works at scale	Add-ins like GPT for Work apply AI operations across thousands of rows at once, which is not practical to do manually
Fewer formula errors	AI-generated syntax is almost always correct, eliminating bracket mistakes, typos, and wrong function names
Faster reporting	Whole workflows from cleaning to analysis to chart creation can run in a single session without switching tools

Best Practices for Using AI in Excel

Using AI in Excel can save hours of manual work, but the quality of your results depends on how you use it. Clear instructions, clean data, and the right workflow make a significant difference.

Be specific about your data structure. Instead of saying “Analyse my sales data,” clearly mention the column letters, what each column represents, and the result you want.

Always include your column layout in formula prompts. AI tools do not know your spreadsheet structure, so specifying which column contains what ensures the formula references the correct cells.

Use follow-up prompts instead of starting over. Since AI remembers the context of your conversation, refine the result if it is close rather than rewriting the entire prompt.

Verify formula logic before applying it at scale. Even if the formula does not show errors, test it on 5 -10 rows before applying it to thousands.

Protect your original data. When asking AI to clean or restructure data, specify that the original data should not be modified and that the output should appear in a new column or sheet.

Clean your data before uploading files. Remove merged cells, ensure the first row has clear headers, delete empty rows, and remove sensitive information for better analysis.
Pick the right method for the task. Use the copy-paste method for quick formulas, the file-upload method for full dataset analysis, Copilot for working directly inside Excel, and bulk-processing add-ins for handling large volumes of data.