Skip to content
Tutorials Center
My learning

Topics

Lesson 81 of 93 ยท Automation and Collaboration

Copilot and AI Features in Excel

Learn what Copilot in Excel can do, how to enable it, how to prompt for formulas, charts, analysis and forecasts, how Analyze Data differs, and the accuracy, limits and privacy rules to follow.

Excel now has built-in AI. Copilot lets you type a request in plain language ("show total sales by region and highlight the top three") and Excel proposes formulas, charts, PivotTables or formatting. An older, non-generative feature called Analyze Data finds patterns and suggests charts. Both save time on routine work, and both can be confidently wrong, so the skill is knowing what to ask, how to check, and what not to share.

This lesson describes how these features work and which habits make them safe. Copilot needs a Microsoft 365 account with a Copilot license and an internet connection, so none of it runs on this site. The TryIts are small JavaScript stand-ins for the checking steps: a data-readiness checker (the shape Copilot likes) and a verification of an AI-suggested number against a formula you control. They do not call any AI and are not Copilot.

Feature names, limits and availability change often. Treat the details below as a guide and check Microsoft's current documentation for your version.

What Copilot in Excel can do

Task Example prompt
Explain "Explain what column F calculates."
Formulas "Add a column that shows revenue minus cost as a percentage of revenue."
Analysis "Which three products had the biggest month-on-month drop?"
Charts and PivotTables "Create a PivotTable of units by region and a column chart of it."
Formatting "Highlight rows where the status is Overdue in red."
Cleaning "Split the Full Name column into First and Last."
Forecast and what-if "Forecast next quarter's sales based on the last 12 months."

Many answers appear as a preview you can Insert, Edit or discard, which keeps you in control. Results that change the sheet should be treated like any other edit: save a copy first and use Undo (Ctrl+Z) if you do not like it.

Getting set up

  1. You need a Microsoft 365 subscription with Copilot (a work, school or personal plan that includes it, depending on your region and the current offering). Check Home for a Copilot button.
  2. Copilot works best on data in a structured form: a range with one row of headers and no blank rows or columns, formatted as an Excel Table (Ctrl+T). Tables give columns names that Copilot can refer to (Excel Tables).
  3. For files stored in OneDrive or SharePoint, AutoSave must be on for some features to work. Local-only files may be unsupported by some versions.
  4. Copilot reads the workbook's content, so it respects your sensitivity labels and your organization's data policies. Ask your administrator about restrictions.
JS Is this range ready for Copilot?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Writing good prompts

AI works best with specific, grounded requests. A useful pattern is task + columns + condition + output:

  • Vague: "Analyze this." Better: "Compare average order value by Region for 2025 and show the result as a bar chart."
  • Name your columns exactly as the headers appear ("Net Revenue", not "income").
  • Say what you want back: a formula in a new column, a PivotTable, a chart, a summary sentence.
  • Ask for one step at a time and refine: "Now sort it descending and keep the top five."
  • Ask it to show the formula it used, so you can read and audit it (Auditing Formulas).

Copilot can suggest formulas such as SUMIFS, XLOOKUP or LET (XLOOKUP, LET and LAMBDA), which is an excellent way to learn them: ask, read the formula, and test it on a few rows. If you want it to use a particular approach, say so ("use XLOOKUP, not VLOOKUP").

Analyze Data

Home > Analyze Data (formerly Ideas) is a separate feature that does not require Copilot in every version. It scans a selected range and offers suggestions as cards: trends, rankings, outliers and ready-made charts and PivotTables you can insert with a click. You can also type a question in its search box ("total revenue by month"). It works best on a clean Table, handles limited sizes of data, and offers a fixed set of analysis types, so it is less flexible than free-form Copilot but also more predictable. Both are quick ways to find a starting view; neither replaces choosing the right chart yourself (Choosing the Right Chart).

AI for text analysis: sentiment and keywords

Survey comments, reviews and notes are text that formulas handle poorly. AI can classify each comment (positive, neutral, negative), pull out keywords or topics, and summarize themes. In Excel this appears two ways: asking Copilot to add a "Sentiment" column, or a function that sends a cell's text to the model with your prompt and returns the result into the cell (where available, as a COPILOT-style worksheet function). Treat the output as a first pass:

  • Spot-check a sample (for example 30 rows) against your own judgment.
  • Results can differ between runs; if you need a fixed answer, paste values once you are satisfied.
  • Sarcasm, mixed opinions and other languages are error prone.
  • For a scalable and auditable alternative, classify with fixed rules (a keyword table and COUNTIF, SEARCH) or with Power Query (Power Query Basics), then use AI for the leftovers.

The stand-in below is a deliberately simple keyword counter (not AI, not Copilot): it shows the rule-based alternative and why it can miss sarcasm.

JS A rule-based sentiment and keyword pass (not AI)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Verify, then trust

AI can produce a plausible number that is wrong: it may use the wrong column, include a total row, or misread a date. A simple habit prevents most trouble: recompute the result with a formula you understand, on a sample or in total.

JS Checking an AI-suggested total against your own formula

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Other checks: compare counts (=ROWS(Table1)), spot-check three rows by hand, look at the chart's axis and filters, and read every formula it inserts. For anything financial or legal, a person must own the final number.

Limits and privacy

  • Accuracy: AI can misread ambiguous headers, mix units, or invent a column that does not exist. It is weakest on messy, merged or multi-table layouts.
  • Size and structure: very large ranges, multiple tables on one sheet and files with heavy formulas can exceed what it can analyze. Work on a clean table or a PivotTable summary.
  • Data stays under your policies: with a work or school account, prompts and responses are covered by that organization's Microsoft 365 commitments (not used to train the underlying models, per Microsoft's documentation). Confirm with your administrator rather than assuming; personal accounts differ.
  • Sensitive data: do not paste confidential data into any AI service your organization has not approved. Remove or mask personal identifiers (names, IDs, health or financial details) if you are unsure.
  • Cost and availability: features roll out in stages, differ between Windows, Mac and the web, and may need a particular license. If a button is missing, it is usually one of license, version or admin policy.
  • Reproducibility: a prompt may not return the same wording or even the same classification twice. Save the final result as values or formulas, not as "ask again".

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What makes data easiest for Copilot to analyze?

    Named columns in a Table give the AI an unambiguous structure; merged cells and several blocks per sheet confuse it.
  2. 2. Copilot gives you a total that looks too high. What is the best next step?

    Independent verification catches wrong ranges, included total rows and misread columns. Never trust an AI number you have not checked.
  3. 3. How is Analyze Data different from asking Copilot a free-form question?

    Analyze Data scans a range and offers cards of trends, ranks and charts, whereas Copilot accepts open-ended prompts and can edit the sheet.
  4. 4. Why spot-check AI-generated sentiment labels?

    Classification is a probabilistic first pass. Checking a sample reveals systematic mistakes before you rely on the column.

Summary

  • Copilot turns plain-language prompts into formulas, charts, PivotTables, formatting and summaries; it needs a Copilot-enabled Microsoft 365 account.
  • Prepare a clean Table, name columns exactly, ask for one step at a time, and ask for the formula to be shown.
  • Analyze Data offers predictable suggestions; Copilot is more flexible; AI text tools classify sentiment and keywords but need spot-checks.
  • Always verify numbers with your own formula, avoid sharing sensitive data with unapproved tools, and save final results as values or formulas.

Next: Project: Monthly Budget Tracker.