excel copilot: Analyze an Excel table and chart

excel copilot: Analyze an Excel table and chart

Direct answer — excel copilot: In Excel for Microsoft 365 (Windows desktop) use the Copilot button to ask for a table analysis and have Copilot create an editable PivotTable, chart, and formula column for review. This guide shows prerequisites, a reproducible worked example prompt, exact steps, how to check results, and common fixes. (Scope: Excel for Microsoft 365 on Windows.)

Prerequisites and scope

  • Excel for Microsoft 365 on Windows and a Copilot-eligible license and tenant settings. See licensing and availability notes below. Excel official help.
  • A workbook containing the data formatted as an Excel Table (select range and press Ctrl+T) so Copilot recognizes structured columns.
  • Save a copy of your workbook before allowing Copilot to edit, if you need a backup.

Worked example (hypothetical)

Hypothetical input: An Excel Table named “Sales” with columns Date, Region, Product, Amount. Goal: monthly totals, a PivotTable on a new sheet, a Growth % column, and a clustered column chart.

Step-by-step actions (Windows Excel)

  1. Open the workbook and confirm the data is an Excel Table (click anywhere inside and verify Table Design appears). If not, select the range and press Ctrl+T. In Table Design, set Table Name to Sales before using the example prompt.
  2. Click the Copilot icon in the lower-right corner of Excel to open Copilot. (If you don’t see it, check license and tenant settings.) Excel official help.
  3. Choose Plan mode first to preview actions, or Edit mode to let Copilot modify the workbook directly. For this example, select Plan mode to review the steps Copilot will take.
  4. Enter this prompt (exact): “Analyze the table named ‘Sales’. Show monthly totals of Amount, create a PivotTable on a new sheet with Month and Sum of Amount, add a column ‘Growth %’ that calculates month-over-month percent change for Amount, and create a clustered column chart of monthly totals. Show the plan.”
  5. Review Copilot’s plan in the pane. If it matches intent, switch to Edit mode (or confirm the plan) and click to apply changes. Copilot will create sheets, PivotTable, formulas, and a chart using Excel features.
  6. When edits finish, inspect the workbook: open the new PivotTable sheet, look at the chart, and check the Growth % column formula. All content remains editable in Excel.

Copilot table-analysis worksheet

Use this worksheet to map inputs to Copilot actions and expected outputs for quick checks. (Hypothetical inputs labeled.)

Hypothetical inputAction for CopilotExpected output
Sales table: Date,Region,AmountAsk: monthly totals, PivotTablePivotTable with Month, Sum of Amount
Request Growth % columnAsk: add formula columnEditable Growth % column with formula
Create chart of monthly totalsAsk: clustered column chartChart linked to PivotTable or data range
Large workbook or protected fileAsk plan only; request editable copyPlan shown; no edits until copy provided

Expected outputs and how to check success

Before relying on a generated calculation, work through Excel formula templates with expected results. If an editable formula shows text, an error, or an unexpected number, use Excel formula troubleshooting to check the symptom before changing the whole table.

  • PivotTable: A new sheet with a PivotTable listing Month in rows and Sum of Amount in values. Check: click the PivotTable and verify the Field List contains Month and Amount.
  • Chart: A clustered column chart on the same or new sheet linked to the PivotTable or source range. Check: right-click chart > Select Data to confirm the source range.
  • Formula column: New column labeled “Growth %” with an editable formula (e.g., using =IFERROR((current – previous)/previous, “”)) applied down the column. Check: click a cell in that column to view the formula bar and edit it.
  • Summary text: Copilot often provides a short textual insight pane you can copy into your workbook or notes.

Common failure cases and fixes

  1. Missing Copilot button — likely license or tenant restriction. Verify your Microsoft 365 license and tenant settings per the Copilot FAQ. Excel Copilot FAQ.
  2. Table not recognized — convert the data to an Excel Table (select range, Ctrl+T) then retry the prompt.
  3. Workbook protected or shared with restrictive permissions — use an editable copy or ask the owner to grant edit access; do not attempt to bypass protections.
  4. Large or external data sources — Copilot may request permission to access files or web data; grant only if acceptable and required.

This tutorial uses the Copilot pane. Microsoft retired the =COPILOT worksheet function on September 14, 2026; it is not required for these steps. An old workbook using that function can return #NAME? when its cells recalculate. See Microsoft’s COPILOT function status.

Limitations and notes

  • Availability, features, and model choices depend on your Microsoft 365 license and tenant settings; some model switches require specific subscriptions. Excel Copilot FAQ.
  • Copilot edits are live in your workbook; always keep a backup if you must preserve the original state.
  • Copilot may fetch web information with citations; verify any externally sourced figures before trusting them.

Next action: Open your workbook, format the data as a table, click the Copilot button, paste the example prompt above, and choose Plan mode to preview Copilot’s steps.


Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top

Privacy and cookie settings