Copilot in Excel: 12 Techniques That Save Hours Weekly

Excel is the application most office workers use without ever touching 80% of its capability. Microsoft's own 2024 productivity research put the share of users who write more than the most basic formulas at under 30%. Copilot in Excel is the most consequential change to that ratio in a decade — not because it teaches you Excel, but because it removes the need to know the syntax. Plain-English questions about a dataset now produce real formulas, real PivotTables, real charts. Below are the twelve techniques that actually save hours, the prompts that produce them, and the places where Copilot still falls down hard.

Table of contents

• What Copilot can and can't do in Excel
• Generating formulas from plain English
• Cleaning messy data
• Finding insights in a dataset
• Building charts from a prompt
• Pivot table generation
• Building dashboards
• When you still need Power Query
• Frequently asked questions
• The bottom line

What Copilot can and can't do in Excel

The single rule that determines whether Copilot in Excel will work: your data must be in a Format-as-Table. Highlight the range, press Ctrl+T, confirm headers — and Copilot can now read it. Without this step, Copilot returns the polite-but-useless "I need your data in a table to help with that" message. This catches more new users than any other quirk.

What it does well: writes formulas, summarises numerical data, explains a column, builds basic charts and PivotTables, and surfaces simple insights ("the top 5 customers represent 64% of revenue"). What it does badly: anything cross-sheet, anything that depends on cell formatting (merged cells, colour-coded data), and anything involving macros. It also genuinely hallucinates statistics if your prompt is too vague — ask "what's interesting about this data?" and you'll get plausible-sounding nonsense. Specificity in prompts is non-negotiable.

Generating formulas from plain English

Formula generation is where Copilot in Excel earns most of its salary for most people. The prompt pattern that works best: describe the column you want as if you were briefing a junior analyst. Bad: "calculate cost." Good: "for each row, multiply the Quantity column by the Unit Price column, then add the Tax Rate column as a percentage."

Three formula categories where the time saving is largest:

Date arithmetic. "Days between Order Date and Shipped Date, only counting weekdays" produces NETWORKDAYS in seconds. The syntax is correct on the first attempt about 95% of the time in our testing. The 5% that fails usually involves regional date formats — Copilot defaults to US date order even on UK locales.

Text manipulation. "Extract the domain name from the email column" produces a clean RIGHT/FIND/LEN combination. "Capitalise the first letter of each word in the Customer Name column" returns PROPER. These are formulas most people would have to look up; Copilot returns them in two seconds.

Conditional aggregation. "Sum the Revenue column where Region equals UK and Status equals Closed Won" produces SUMIFS with the right syntax — including the awkward range-and-criteria pairing that trips up most people. Combined with our prompt engineering guide, you can extend this pattern to nested conditions.

Cleaning messy data

Data cleaning is the unglamorous half of analyst work and the place where Copilot has improved fastest. The 2025 Excel updates added explicit data-cleaning intents — "find inconsistent capitalisation in column B," "flag rows where the email format is invalid," "highlight duplicates excluding column D." These now work end-to-end without you needing to know Conditional Formatting syntax.

The single most useful data-cleaning prompt: "Identify rows where the values in column X look like data entry errors." Copilot will compare the column against itself, find outliers (too long, wrong format, statistical anomalies), and add a column flagging them. On a 10,000-row sales export, this catches 90% of the obvious typos in 10 seconds.

Where Copilot still falls down: it does not modify cells in place. It will write a helper column ("=IF(..., 'flag', '')" or a corrected version), but it will not overwrite your original data. If you need to mass-correct, you still need Find & Replace or Power Query. The reason is safety — Microsoft does not want Copilot silently changing values you might not have wanted changed.

Finding insights in a dataset

Copilot's "find insights" panel is the feature most prone to disappointment. The high-level prompt — "what's interesting about this data?" — produces shallow observations. The pattern that works: ask specific questions about specific dimensions.

Compare these two approaches on a 50-column sales export:

Lazy: "Tell me what's interesting." → Returns three banal observations about the largest customer and the median order value.

Specific: "Compare order frequency between customers acquired in 2024 vs customers acquired in 2025. Look at the column called Acquisition Cohort and the column called Order Date." → Returns a properly grouped analysis with retention rates, average orders per customer per month, and a chart suggestion.

Copilot is not a replacement for an analyst with hypotheses; it is a much faster way to test the hypotheses you already have. Treat it accordingly. The general approach to prompting analytical work is covered in prompt engineering examples.

Building charts from a prompt

"Create a chart showing monthly revenue trend by region for the last 12 months" produces a clean line chart with one series per region in roughly four seconds. The chart type Copilot picks is usually correct; the styling is generic. Plan to spend a minute customising colours and titles after, or apply a saved chart template post-hoc.

Two prompting moves that produce better-looking charts: name the chart type ("a stacked bar chart, not a line chart") and name the comparison ("year-over-year, not month-over-month"). Copilot is reading your data and inferring intent — it gets the right answer faster when intent is specified.

The chart types Copilot handles well: line, bar, column, stacked column, scatter, pie (when not too many slices). The chart types it handles poorly: combo charts with secondary axes, waterfall, radar, geographic. For those, draft the chart manually and use Copilot only for the data prep.

One subtle move that improves chart quality across the board: ask Copilot to add a chart title and axis labels in the prompt itself. "Create a column chart of monthly sales by region. Title it 'Q4 Sales by Region', label the x-axis 'Month' and the y-axis 'Revenue (USD)'." The titled-and-labelled chart is presentation-ready in one step instead of two. Most users skip this and end up editing the chart afterwards. The 15 seconds of prompt extension saves a minute of post-hoc formatting.

Pivot table generation

PivotTables are the feature where Copilot most reliably saves hours per week, because PivotTables are the feature most office workers know exists but cannot build from scratch. The prompt is short: "Make a pivot table showing sum of Revenue by Region by Quarter." Copilot produces it on a new sheet, formatted, with the right hierarchy.

The killer extension: "Make the same pivot but with year-over-year growth percentage." Copilot adds the calculation as a measure. This is genuinely advanced PivotTable usage, and Copilot collapses the learning curve to one English sentence.

Where it falls short: any PivotTable that requires data from more than one source. If your figures live in two sheets that need to be joined, you still need Power Query first. Copilot in Excel does not do joins — it operates on a single table only.

Building dashboards

"Build a dashboard summarising this data" is too vague. Copilot will produce a sheet with a few PivotTables and charts, none of them particularly thoughtful. The pattern that works: ask for the components one at a time.

A working dashboard prompt sequence:

• "Add a KPI card showing total Revenue at the top of a new sheet."
• "Add a chart showing monthly Revenue trend, last 12 months, below the KPI."
• "Add a chart showing top 10 customers by Revenue, sorted descending."
• "Add a small table showing the bottom-performing region by gross margin."

Each prompt produces a discrete, correct output. Stitched together, you get a credible dashboard in under 10 minutes — work that used to take an analyst a half-day in 2022. For the broader principle, see our complete Copilot training guide.

The dashboard pattern works because each prompt is bounded. Copilot in Excel is much more reliable on narrow, specific requests than on broad, multi-part ones. The temptation when you're new to it is to ask for "a sales dashboard" and expect a finished artefact. The temptation when you're experienced is to break it into atomic asks, each with a single output. The latter approach is more typing but hits more reliable results. Treat it like talking to a junior analyst — specific, sequential, verifying each step before moving on.

When you still need Power Query

Power Query is not dead. It is the right tool whenever you need to (a) join data from multiple sources, (b) refresh on a schedule, (c) handle very large datasets (10M+ rows), or (d) document a transformation step-by-step for someone else to maintain.

Copilot can describe what a Power Query step does and can suggest M code for simple transformations, but it cannot author and maintain a multi-step ETL pipeline. The mental split: Power Query for repeatable data preparation, Copilot for analysis on prepared data.

The teams that get the most out of Excel in 2026 are using both. Power Query handles the messy ingestion and joining; Copilot handles the analysis on top of the cleaned table. Keeping these separate prevents Copilot from being asked questions it cannot answer reliably and prevents Power Query from being used for the conversational "what's interesting about this" questions where it adds nothing. The clean separation is faster than trying to make either tool do the other's job.

Frequently asked questions

Why does Copilot say it can't help with my spreadsheet?

The most common cause is that your data isn't in a Format-as-Table. Highlight your data range, press Ctrl+T, confirm headers, and Copilot will now process it. The second most common cause is merged cells in the data — un-merge and try again. The third is sheets where header rows are in row 4 instead of row 1; Copilot expects headers in the first row of the table.

Can Copilot in Excel write VBA macros?

No. Copilot in Excel does not generate or execute VBA code. For macro-style automation, use Office Scripts (the modern equivalent for the web/Excel desktop M365 stack) — Copilot can write Office Scripts in TypeScript when prompted. Older VBA workflows still need a human.

Will Copilot edit my data directly?

No. Copilot writes new columns, formulas, and sheets but will not overwrite existing cell values. This is intentional — Microsoft chose safety over speed. If you need to mass-correct values, use Find & Replace or Power Query. Copilot can suggest the corrections; the act of applying them is yours.

Does Copilot work in Excel for the web?

Yes, with the same M365 Copilot licence. The web Copilot is occasionally a release behind the desktop version on new features. The desktop app remains the most reliable surface for Copilot use; the mobile apps support a smaller subset of features.

How does Copilot handle confidential financial data?

Microsoft 365 Copilot processes the prompt and the visible table data in Microsoft's cloud under the same enterprise data protection terms as the rest of M365 Copilot — no model training on your data, no cross-tenant leakage, data stays in your tenant region. Sensitivity labels (Confidential, Restricted) are honoured: Copilot will refuse to summarise content into a destination with a lower sensitivity classification.

The bottom line

Copilot in Excel is not a replacement for knowing Excel — it is the formula reference, the PivotTable expert, and the chart builder you've been pasting into ChatGPT for the last two years, now sitting natively in the ribbon. The single setup step that unlocks everything is Format-as-Table: do this on every dataset before asking Copilot to help.

The four moves with the highest ROI: formula generation in plain English, conditional aggregation, PivotTable creation from a prompt, and incremental dashboard building. Master these in 30 minutes and the licence pays for itself in a week.

For the broader picture across all Office apps, see our Microsoft Copilot complete training guide, or browse all our Microsoft Copilot guides.

One last note: Excel's Python in Excel feature, layered with Copilot, is the most underused capability in the 2026 Microsoft stack. For analysts who have learned Excel but not Python, the natural-language route to a forecast or a clustering analysis collapses what was a meaningful technical barrier. If your role does any quantitative analysis, this is the feature most worth a Saturday morning of exploration. The skills you build there are immediately applicable in your daily work and are what will distinguish a 2026-grade analyst from a 2022-grade one.

Last updated: January 2026