Lesson 02 · Copilot Mastery Pro ~14 min read 4 workflows · Hands-on

Copilot in Excel: build a real dashboard in 15 minutes.

Excel is where Copilot's value really shows. People who get good at Copilot-in-Excel save hours every week on formulas, pivot tables, and analyses that used to require knowing Excel cold. This lesson walks the four workflows that turn a CFO-grade dashboard from a 2-hour project into a 15-minute one — formula generation, chart-from-natural-language, data cleaning, and pivot-tables-on-demand.

The mental model

Copilot in Excel isn't a chatbot — it's a colleague who actually knows Excel.

The mistake most people make: typing prompts like "how do I do a VLOOKUP?" Copilot in Excel isn't there to teach you Excel; it's there to do Excel for you. Tell it what you want to accomplish in plain English, point at the data, and let it generate the formula, build the chart, or restructure the table.

The Reframe

Treat Copilot in Excel like a fast-typing analyst sitting next to you. They know every function, every shortcut, every pivot trick. You bring the question and the context; they bring the execution.

Workflow 01 Formula generation from natural language

1

Stop Googling SUMIFS syntax

Highlight your data range. Open Copilot panel. Describe what you want in plain words. Copilot writes the formula and explains what it does.

The prompt that works

Example promptI have a sales table where column A is the date, B is the salesperson, and C is the amount. Add a column that shows the running 30-day total for each salesperson.

Best use cases

  • Conditional aggregations (SUMIFS, COUNTIFS variants)
  • Date arithmetic and running totals
  • Lookups across sheets
  • Complex IF statements you'd normally nest 4 deep
Copilot occasionally generates correct-looking but subtly wrong formulas — especially on date logic. Always spot-check the first 3 rows before trusting the column.
Time savings: 30 min of formula research → 60 sec. The skill that used to separate Excel power users from everyone else is now democratized.

Workflow 02 Chart from natural language

2

Build a dashboard chart in one sentence

Instead of fighting Excel's chart wizard, just describe the chart you want. Copilot picks the right type, applies decent formatting, and inserts it.

The prompt that works

Example promptShow a stacked bar chart comparing each product line's revenue by quarter for 2025. Use the brand colors. Sort by total descending.

Best use cases

  • Sales/revenue summaries for executives
  • Quick visualizations of trends
  • Comparison charts (this year vs. last, segment vs. segment)
  • Dashboard tiles you'd otherwise spend 30 min styling
Copilot's chart formatting is decent but not magazine-quality. For anything customer-facing, expect to clean up labels, legend position, and colors after.
Time savings: 10-15 min of chart-wrangling → 30 sec. Compounding when you build many.

Workflow 03 Data cleaning at speed

3

Fix messy data without writing macros

Paste in messy data (mixed case, inconsistent dates, dirty addresses). Tell Copilot what "clean" means for your case. It writes the formulas or applies the transformations.

The prompt that works

Example promptColumn D has dates in five different formats: "3/14/26", "Mar 14 2026", "2026-03-14", "14-Mar-26", and "03/14/2026". Normalize them all to YYYY-MM-DD.

Best use cases

  • Standardizing dates, currencies, phone numbers
  • Splitting full names into first/last
  • Cleaning up addresses or email lists
  • Removing duplicates with custom matching rules ("same email or same phone")
Always work on a copy of your data. Copilot's transformations can be irreversible if you don't have an undo path. Highlight, copy to a new sheet, transform there.
Time savings: Hours of manual cleanup → minutes. The boring task that secretly eats analyst time.

Workflow 04 Pivot tables on demand

4

Ask the question, get the pivot

Pivot tables are powerful but confusing. With Copilot, you describe the breakdown you want — and the pivot appears, configured.

The prompt that works

Example promptPivot this data to show total revenue by region (rows) and product line (columns), with quarter as a filter. Add a percentage of total column.

Best use cases

  • Multi-dimensional sales summaries
  • Cohort analyses
  • Inventory by category × location
  • Anything you'd normally build manually with PivotTable + Field List
Pivots break when source data changes shape (new columns, renamed headers). Build your raw data as a structured Table (Ctrl+T) first — pivots stay alive.
Time savings: 20 min of pivot-fiddling → 1 min.

Final challenge: rebuild a real spreadsheet in 15 minutes

Pick a spreadsheet you previously spent 60+ minutes building — a monthly report, a sales tracker, a forecast model. Open a blank sheet next to it. Using only the four workflows above, rebuild it from scratch with Copilot doing the heavy lifting.

Time yourself. If it took 60 minutes the first time, most people complete the rebuild in 12-18 minutes. The skill compounds across every Excel task for the rest of your career.

Back to Copilot Mastery → See pricing →

What you can do now

  • Generate any Excel formula by describing what you want in plain English
  • Build charts and pivot tables faster than writing the prompt to describe them
  • Clean messy data without writing custom macros or VBA
  • Spot-check Copilot's output for date-logic and edge-case errors
  • Recognize the cases where Copilot saves real time vs. where manual is faster
Pro
Up next in Copilot Mastery

Lesson 3 · Copilot in Word: from blank page to draft in 5 prompts

Proposals, reports, memos, RFPs. The prompt structures that get usable first drafts in minutes — and the editing patterns that turn drafts into final docs. See pricing →