Copilot in Excel: Formula Suggestions, Column Formulas and Analyze Data

Part of the free Module 13: Excel Formulas with AI · Lesson 3 of 9 · Full Excel course

Updated on 5 September 2026 · Requires Microsoft 365 with a Microsoft 365 Copilot licence (or a Personal/Family plan that includes Copilot); Analyze Data works in Microsoft 365 without Copilot. Not available in Excel 2021, 2019 or 2016.

Copilot in Excel is the AI assistant that opens as a pane from the Home tab of Excel in Microsoft 365. You describe what you want in plain English and Copilot proposes a formula column, explains the formula, and inserts it into your table when you approve. It can also highlight, sort, filter, summarise and build pivot tables and charts from the same pane.

What you need before Copilot will work

Most “Copilot does nothing” complaints come from a missing requirement rather than a fault. Check the four items below before you start. Microsoft changes these rules between builds, so if something in your Excel looks different, check your build and licence rather than assuming the feature is broken.

Requirement Why it matters How to check
A Microsoft 365 Copilot licence, or a Personal/Family plan that includes Copilot The Copilot pane is a paid feature. Without it the button is missing or greyed out. Sign in, then look for the Copilot button on the Home tab. Your admin or your Microsoft account page lists the licence.
Excel for Microsoft 365, up to date Copilot is not in Excel 2021, 2019 or 2016 and older 365 builds may lack newer features. File > Account > Update Options > Update Now.
Data formatted as an Excel Table Copilot reads headers and rows from a Table and writes its formula as a column of that Table. Click inside the data and press Ctrl+T, or use Insert > Table. Tick “My table has headers”.
File saved to OneDrive or SharePoint with AutoSave on Historically Copilot needed a cloud copy of the file. Microsoft has been relaxing this in newer builds, so check your build. Look at the AutoSave toggle in the title bar. If it is off, save the file to OneDrive first.

How to ask Copilot for a formula column

Copilot’s most useful formula feature is the formula column. You describe a calculation, Copilot writes a formula that uses your table’s headers, and you decide whether to insert it.

  1. Click any cell inside your Table.
  2. On the Home tab, click Copilot. The Copilot pane opens on the right.
  3. Type your request in the message box and press Enter. Use the column names exactly as they appear in your headers.
  4. Copilot replies with a proposed column: the new header, the formula it will use, and a short explanation of what the formula does. Read the formula before you accept it.
  5. Click Insert column. Copilot adds the column to the right of your Table and fills it with the formula.
  6. Check a few rows by hand. If the result is wrong, reply in the pane with the correction, for example “use Amount minus Cost, not Amount divided by Cost”.

Three prompts that work well because each names a column and states the rule clearly:

Add a column that shows the profit margin as a percentage, using Profit divided by Sales
Add a column that flags orders as Late when Order Date is more than 30 days before today, otherwise leave it blank
Add a column with the customer's first name, taken from the Customer column before the first space

Reading and checking the formula Copilot inserts

Because your data is a Table, Copilot writes formulas with structured references. [@Sales] means “the Sales value in this row”, and [@[Order Date]] uses double brackets because the header contains a space. These references copy down the whole column automatically and do not break when you add rows.

If you need ordinary cell references, for example to paste the formula into a sheet without a Table, click the cell and retype the reference as E2 instead of [@Sales]. You can also convert the whole Table back to a range with Table Design > Convert to Range, which keeps the values and turns structured references into normal ones.

To understand a formula you did not write, select the cell and ask the pane “explain this formula”. Copilot describes each part in plain English. This is the fastest way to learn what a nested IF or a TEXTBEFORE call is actually doing before you trust it.

Other things the Copilot pane can do

Highlight, sort and filter. Ask “highlight the top 10 amounts” or “sort by Ship Date newest first”. Copilot applies conditional formatting, a sort or a filter to the Table and tells you what it did, so you can undo it with Ctrl+Z if needed.

Pivot tables and charts. Ask “show total Amount by Customer as a pivot table” or “chart Amount by month”. Copilot builds the object on a new sheet or next to your data, depending on your build. You can then edit it like any pivot table or chart.

Summarise. Ask “summarise this data” and Copilot returns a short written overview of the columns, totals and obvious patterns. Treat it as a first look, not a finished analysis.

Advanced analysis. On builds that include Python in Excel, Copilot offers an Advanced analysis option. It writes Python code inside a PY() cell to run correlations, forecasts and more detailed statistics. Chapter 14 covers this in depth; check whether your build shows the option.

Copilot only reads the sheet you are on, and it will not change cells outside a formula column without you confirming the action first.

Analyze Data: the free alternative

Analyze Data (formerly Ideas) is included in Excel for Microsoft 365 without a Copilot licence. It answers natural-language questions about a range by returning pivot tables and charts. It does not write arbitrary formulas, so use it for questions rather than calculations.

  1. Click inside your data range or Table.
  2. On the Home tab, click Analyze Data. A pane opens with suggested charts and summaries.
  3. In the question box, type something like “total Amount by Customer” or “which customer has the highest Amount” and press Enter.
  4. Review the suggested pivot table or chart and click Insert PivotTable or Insert Chart to add it to a new sheet.

Worked example: days to ship and a Late flag

Format this Orders data as a Table with Ctrl+T. Dates are real Excel dates, not text.

Order ID Customer Order Date Ship Date Amount
1001 Asha Patel 01-Jul-2026 03-Jul-2026 450
1002 Ben Carter 02-Jul-2026 09-Jul-2026 1,200
1003 Chen Wei 03-Jul-2026 08-Jul-2026 320
1004 Dana Ruiz 06-Jul-2026 14-Jul-2026 875
1005 Emma Jones 07-Jul-2026 10-Jul-2026 610
1006 Farid Khan 08-Jul-2026 15-Jul-2026 2,050

Open the Copilot pane and type:

Add a column showing days between order and ship date, and another that says Late if it is more than 5 days

Copilot should propose two columns. The first, Days, uses:

=[@[Ship Date]]-[@[Order Date]]

The second, Status, uses:

=IF([@Days]>5,"Late","On time")

After you click Insert column for each, the results are: 1001 = 2, On time; 1002 = 7, Late; 1003 = 5, On time; 1004 = 8, Late; 1005 = 3, On time; 1006 = 7, Late. Order 1003 is the edge case: 5 days is not “more than 5”, so it stays On time. If your Days column shows a date instead of a number, set the column’s number format to General.

Copilot versus ChatGPT for formulas

Question Copilot in Excel ChatGPT (or Claude, Gemini)
Sees your data? Yes, the Table on the active sheet Only what you paste or upload
Inserts the formula for you? Yes, as a Table column No, you copy and paste it
Cost Copilot licence or a plan with Copilot Free tier available
Excel version Microsoft 365 only Any version, but tell it which
Best for Quick columns on tabular data Complex or unusual formulas, explanations, debugging

Tips and common mistakes

  • Use the exact header names. “Order Date” in the prompt maps to [@[Order Date]]. Vague words such as “the date” make Copilot guess.
  • One calculation per prompt for anything tricky. Copilot handles two simple columns at once, but complex logic is safer one step at a time.
  • Read the formula before Insert column. The explanation sounds confident even when the logic is wrong.
  • Keep the Table tidy. Blank header cells, merged cells and totals rows inside the data confuse Copilot.
  • Dates must be real dates. Text such as “1 Jul 2026” typed into a General cell breaks date arithmetic.
  • Copilot is not your version of Excel. It may suggest functions such as TEXTBEFORE that exist in Microsoft 365 but not in older versions of the same file’s recipients.
  • Undo works. Every Copilot action can be reversed with Ctrl+Z.

Errors and how to fix them

Problem Likely cause Fix
Copilot button missing or greyed out No Copilot licence, not signed in, or an old build Sign in, run File > Account > Update Now, then confirm your licence
“Copilot can’t work with this data” or similar Data is not a Table, or the file is not saved to OneDrive/SharePoint Press Ctrl+T, save to OneDrive, turn AutoSave on and retry
Formula column gives wrong values Ambiguous prompt or wrong column picked Reply in the pane naming the exact columns, or edit the formula directly
Formula shows #NAME? Function not available in this build or locale Update Excel, or ask Copilot for a version using classic functions
Days column shows a date Excel copied the date format from the source columns Set the column’s number format to General
Analyze Data returns nothing useful Range has merged cells, blank headers or mixed data types Clean the headers, remove merged cells and format as a Table

Practice exercise

  1. Type the Orders table above, press Ctrl+T, and ask Copilot for a column that shows the Amount with 18% tax added.
  2. Ask for a column that shows the month name of each Order Date, then ask Copilot to explain the formula it used.
  3. Ask Copilot to highlight every Late order in red, then undo it with Ctrl+Z.
  4. Open Analyze Data and ask “total Amount by Customer”. Compare the pivot table with one you build yourself.

Key takeaways

  • Copilot in Excel needs a Copilot licence, an up-to-date Microsoft 365 app and data formatted as a Table.
  • Ask for a formula column, read the formula and explanation, then click Insert column.
  • Copilot writes structured references such as [@Sales]; convert the Table to a range if you need cell addresses.
  • The pane also highlights, sorts, filters, builds pivot tables and charts, summarises and can run Python-based Advanced analysis.
  • Analyze Data is free with Microsoft 365 and answers questions with pivot tables and charts, but does not write formulas.

Related lessons

Frequently asked questions

Is Copilot in Excel free?

No. The Copilot pane needs a Microsoft 365 Copilot licence, which businesses buy as an add-on, or a Microsoft 365 Personal or Family subscription that includes Copilot with monthly AI credits. Analyze Data is the free alternative included in Excel for Microsoft 365, but it builds pivot tables and charts rather than formulas.

Why is Copilot greyed out in Excel?

The usual causes are no Copilot licence on the signed-in account, an out-of-date Excel build, or a file that is not saved to OneDrive or SharePoint with AutoSave on. Sign in, update Excel from File > Account, save the file to OneDrive and check your licence with your admin or account page.

Can Copilot write formulas in Excel?

Yes. Ask the Copilot pane to add a column, and it proposes a formula using your Table’s headers, explains it and inserts it when you click Insert column. It writes structured references such as [@Amount]. Always check a few rows, because a confident explanation does not guarantee correct logic.

Does Copilot need my data in a table?

Copilot works best when the data is an Excel Table, and formula columns are added as Table columns. Some builds will offer to convert a plain range for you, but pressing Ctrl+T first avoids errors and gives Copilot clean header names to work with. Check your build if the behaviour differs.

What is the difference between Analyze Data and Copilot?

Analyze Data is free with Microsoft 365 and answers questions about a range with suggested pivot tables and charts. Copilot is a paid assistant that can also write formula columns, highlight, sort, filter, summarise and run Python-based analysis, and it holds a conversation so you can refine each request.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousHow to Write a Prompt for an Excel Formula: The 5-Part TemplateAll lessonsExcel Formulas with AINext →COPILOT Function in Excel: AI Inside a Worksheet Formula