RESEARCH REPORT PART VI APR 2026 ARUP DIGITAL ADVISORY WORKFLOWS

The Excel
workflow, reimagined.

How the advisory team should use Copilot in Excel — from messy data cleanup to client-ready analysis to AI-powered formulas. Five distinct Excel AI tools, six use-case workflows, and the vibe-working pattern that ties them together.

What is "vibe working"?
Microsoft's name for the new pattern of human-AI collaboration in Office — inspired by "vibe coding" in software development. You describe intent in plain language; Copilot plans, executes, evaluates its own output, and iterates with you. In Excel this means: instead of building formulas one cell at a time, you hand off the whole modelling problem and steer.
5 tools
Distinct Excel
AI surfaces
6
Use-case
workflows
=COPILOT()
Native Excel function
for AI in cells
Mar 2026
Edit with Copilot
(formerly Agent Mode)
Contents
  1. Vibe working in Excel — the mental model
  2. The five distinct Excel AI tools
  3. Prerequisites — prepare your data first
  4. Use case 1 — clean + reshape messy data
  5. Use case 2 — AI formulas with COPILOT()
  6. Use case 3 — build a dashboard from scratch
  7. Use case 4 — formula generation + explanation
  8. Use case 5 — advanced analysis with Analyst
  9. Use case 6 — reconcile across multiple files
  10. Decision matrix & do's / don'ts
01 / VIBE WORKING

The pattern that changes everything.

Before the tools, the mental model. Vibe working is Microsoft's name for the new collaboration pattern between a human and Copilot — and it's where Excel has changed the most. Understanding it is the difference between using Copilot as a formula helper and using it as an analyst.

In September 2025, Microsoft launched what it calls vibe working — a deliberate parallel to vibe coding in the developer world. The idea: instead of executing isolated, one-shot commands against AI, you describe intent in plain language, let the AI plan and execute multi-step work, watch it reason, stop it if it goes wrong, and iterate with it until the output is right.

"It's like you're handing off work to an Excel expert — while you steer and guide." — Microsoft, on Agent Mode in Excel

In Excel specifically, vibe working shows up in two places: Edit with Copilot (what Microsoft originally called Agent Mode) inside the Excel app itself, and Office Agent inside Copilot Chat for generating entire spreadsheets from a conversation. Both are powered by reasoning models — GPT-5.2 in Excel, Anthropic's Claude Opus 4.5 as an option — that can plan, execute, evaluate their own output, fix issues, and repeat until the outcome is verified.

The three shifts in how you work

From precise to descriptive. Old pattern: "Write a VLOOKUP to match column A in Sheet1 to column C in Sheet2 and return the value from column E." New pattern: "Enrich my project list with the regional manager's name from the staff roster." The AI works out the mechanics.

From one-shot to iterative. Old pattern: Copilot does one thing, you accept or reject, move on. New pattern: Copilot plans a multi-step task, shows you its reasoning, you watch it execute live, stop it when it strays, redirect with follow-up prompts. The conversation is the work.

From you operating Excel to you supervising it. Old pattern: You write the formulas, you build the pivot, you format the output. New pattern: Copilot does all three while you review. You're no longer the driver — you're the director.

This matters because most advisory work in Excel — portfolio analysis, benchmarking, stakeholder surveys, KPI dashboards — is exactly the kind of multi-step, iterative analytical work that vibe working is built for. The consultants who get good at this pattern will build in 20 minutes what used to take them a morning.

Why "Agent Mode" is now called "Edit with Copilot"
Microsoft rebranded Agent Mode to Edit with Copilot in March 2026. The capability didn't change — just the language. Microsoft's reasoning: agentic capabilities are becoming standard across Copilot, so calling it a separate "mode" overstates the distinction. Expect to see both names in docs, screenshots, and training materials for the next year. They mean the same thing.
02 / THE FIVE TOOLS

Five distinct Excel AI surfaces — not one.

"Copilot in Excel" means at least five different things. Every one of them uses AI differently, costs differently, and works differently. Before the workflows, the tour.

Core surfaceHome ribbonWeb, Windows, Mac
1 · Core Copilot in Excel
The ribbon-button Copilot everyone sees first. Conversational help with data that's already in a table.
Click the Copilot icon on the Home tab. A side pane opens. Ask it to highlight, sort, filter, summarise, generate a chart, build a pivot, explain a formula, or identify an insight. Your data must be in an Excel Table (Ctrl+T) or a properly-formatted range — headers in row 1, no merged cells, consistent types. Without that, core Copilot will politely refuse.
Good for
  • Formula generation and explanation
  • Quick pivots and chart suggestions
  • Highlighting rows that match a condition
  • Simple trend and outlier summaries
  • Natural-language data exploration
Not for
  • Multi-step workbook changes (use Edit with Copilot)
  • Cross-file analysis (use Analyst)
  • Heavy statistical work (use Analyst)
  • In-cell persistent AI (use COPILOT function)
Vibe workingGA Mar 2026GPT-5.2 or Claude Opus 4.5
2 · Edit with Copilot (formerly Agent Mode)
The vibe-working surface in Excel. Hands off multi-step tasks to a reasoning model that plans, executes, and fixes its own work.
Opened from the Copilot pane — switch the toggle or click Edit with Copilot. The reasoning model plans a step-by-step approach, shows its thinking, makes live changes to your workbook, verifies results, and re-runs when something's off. Supports Anthropic's Claude Opus 4.5 and OpenAI's GPT-5.2 — pick via the model dropdown. (Claude requires your admin to enable "AI providers for other large language models" in the M365 Admin Center.) Workbook must be saved to OneDrive or SharePoint. Stop button pauses any time.
Good for
  • Reshaping and cleaning messy data
  • Merging multiple sheets into one view
  • Building dashboards with multiple charts + pivots
  • Complex reports combining 4-5 operations
  • Financial models from a plain-language brief
Important to know
  • No preview — changes happen live
  • Only works on the currently open workbook
  • Never use on production client files without a backup
  • Requires OneDrive/SharePoint (cloud-connected)
  • Can take minutes for complex requests
In-cell AIRolling out 2026Lightweight OpenAI model
3 · The COPILOT() function
Native Excel formula. AI inside a cell — spills into the grid, recalculates with the data, chains with other formulas.
An actual =COPILOT() function you type into a cell. Syntax: =COPILOT(prompt_part1, [context1], [prompt_part2], [context2], …). Spills results into adjacent cells, recalculates when referenced cells change, can be nested inside IF, SWITCH, LAMBDA, WRAPROWS. Available via the M365 Insider or Frontier program; EU/UK still Beta-channel only. Hard limit: 100 calls per 10 minutes. Can't run in workbooks labelled Confidential or Highly Confidential.
Great for
  • Classify sentiment across 500 comments
  • Extract structured data from free-text cells
  • Normalise inconsistent product or place names
  • Categorise records against a predefined list
  • Generate sample data, lists, taxonomies
Never use for
  • Numerical calculations — use SUM / AVG / IF
  • Lookups against a table — use XLOOKUP
  • Legal, regulatory, or compliance outputs
  • Live web or internal data (doesn't have access)
  • Anything requiring deterministic reproducibility
Chat surfaceWeb modeCross-file
4 · Copilot Chat with a workbook
Conversational chat about an Excel file without changing it. For questions, summaries, and analysis rather than edits.
Upload the workbook (or reference it by /filename) into Copilot Chat at m365.cloud.microsoft/chat. Ask questions, get summaries, request analysis — but no changes are written back to the file. Good when you want answers without risk, or when you're discussing a client file you shouldn't modify. Supports multiple files in the same chat.
Deep analysisPython-powered25/month cap
5 · The Analyst agent
Chat-first, Python-powered data science. The deepest analytical tool in the M365 stack — and the right tool for any deck-backing analysis.
Not inside Excel — inside Copilot Chat. Switch to the Analyst agent, upload one or more Excel files, and Analyst plans analytical steps, writes and runs Python (pandas, matplotlib, scikit-learn), validates results, produces charts. Handles cross-file reconciliation, forecasting, regression, clustering, anomaly detection. Counts against the combined 25 Researcher + Analyst queries/month — spend them on genuinely hard analysis.
Use when
  • You need rigour: Python-validated outputs
  • Analysis spans multiple Excel files
  • Statistical or predictive modelling required
  • Output is going into a client deck
  • Accuracy is critical (Analyst shows the code)
Don't burn 25/month on
  • Questions a PivotTable could answer
  • Simple summaries of one file
  • Formulas Core Copilot handles fine
  • One-step filtering and sorting
What used to be here — Copilot in Excel with Python
If you remember "Copilot in Excel with Python" (natural language → Python code inserted into a new sheet, Think Deeper mode), Microsoft announced deprecation in late Feb 2026. The capability split three ways: Edit with Copilot for workbook edits, Copilot Chat for questions, Analyst for deep reasoning. If any existing Arup workflow still points at Copilot-in-Excel-with-Python, migrate it to Analyst. There's no direct in-Excel analog.
03 / PREREQUISITES

Prepare your data first.

The single highest-leverage move before any Copilot in Excel work. Five minutes spent on data structure saves hours downstream — and the difference between "Copilot is useless" and "Copilot is brilliant" almost always comes down to this.

Copilot in Excel reads your data through the same lens Excel's formula engine does. Tables with clean headers, consistent types, no merged cells, no blank header rows. Every shortcut you take at the data-preparation stage is paid back as a bad Copilot response later.

The five-minute pre-flight checklist

1. Convert ranges to Tables. Select the data, press Ctrl+T, confirm headers. Copilot treats named Tables as first-class objects — it references them by name, handles them structurally, and knows where they end. A loose range is something it has to reverse-engineer.

2. Fix the headers. Every column needs a descriptive, unique header in row 1. No "Column1". No merged header cells. No two-row headers. If a column's meaning requires explanation, the explanation goes in the header — "Budget_GBP_Thousands" beats "Budget" any day.

3. One value per cell. If cells contain "London, UK" but you want to filter by country, split them. Copilot can do the split (Core Copilot handles this well), but giving it clean data up front means every downstream prompt produces better results.

4. Consistent types per column. A column of dates should be actual dates, not text. A column of numbers should be numbers, not "£1,200" with the currency mixed in. Use Excel's cell formatting for display; keep the underlying data clean.

5. Save to OneDrive or SharePoint. Edit with Copilot explicitly requires cloud storage. Core Copilot works locally but some features are cloud-gated. If the file lives only on your C: drive, move it.

None of this is Copilot-specific — it's just good Excel hygiene. But it's the difference between a working session and a frustrating one.

If in doubt, start by asking Copilot to fix it
If you've inherited a messy spreadsheet and don't want to manually clean it, paste this as your first prompt in Edit with Copilot: "Audit this workbook. Identify any structural issues — merged cells, inconsistent types, missing headers, duplicate rows — and produce a clean Table from this data. Show me what you changed." It'll do 80% of the work. Use cases 1 and 6 below walk through this properly.
04 / USE CASE 1

Clean and reshape messy data.

The first real test of vibe working. You've been sent a spreadsheet that's half data, half commentary, with inconsistent columns and merged cells. The classic advisory scenario. Edit with Copilot is built for exactly this.

~15 minLow effortEdit with CopilotSingle file
Take a mess, get a clean Table
Save the original. Let Edit with Copilot propose a clean version in a new sheet. Review, iterate, accept.
Use this when
You've received data from a client, another practice area, or an external source that isn't analysis-ready. Typical scenarios: stakeholder survey exports, project registers with free-text status columns, BIM schedule exports, contractor cost submissions, procurement lists.
01
Save a backup — always work on a copy
02
Open the file from OneDrive / SharePoint
03
Open Copilot → Edit with Copilot toggle on
04
Paste the audit prompt → watch reasoning
05
Review change log → iterate or accept
You are a senior data analyst. I want you to clean and reshape this workbook for analysis. ## What the data is [e.g. A stakeholder survey export from our transport client. Around 240 responses, 15 questions, exported from SurveyMonkey into a single sheet with merged header rows and inconsistent multi-select answers.] ## What I need 1. Audit the workbook. Flag: merged cells, inconsistent types, missing or duplicated headers, blank rows, non-standard date/number formats, and any obvious data-entry errors. 2. Produce a clean version in a NEW sheet called "Cleaned". Keep the original sheet untouched for audit. 3. In the clean sheet: one row per response, one column per question, every column with a descriptive unique header, consistent types throughout, and convert multi-select answers into separate TRUE/FALSE columns. 4. Add a "Quality" column flagging any row that's incomplete or internally inconsistent, with a short reason. ## How to work - Show me your step-by-step plan before making changes - Make changes one step at a time so I can interrupt - After finishing, produce a change log on a "Changes" sheet listing every transformation you made and why Begin with your audit report. Do NOT start making changes until I confirm the plan looks right.
The "new sheet, not in place" rule
Always tell Edit with Copilot to produce cleaned output in a new sheet, not to modify the original. Edit with Copilot has no preview mode — changes happen live. If it misinterprets your intent and overwrites good data, there's no single-click undo. A new-sheet workflow gives you the review step that Edit with Copilot's architecture doesn't.
05 / USE CASE 2

AI formulas with =COPILOT().

The most underused tool in the stack and — for the right problem — the most elegant. AI as a native formula, spilling into the grid, recalculating when data changes, chainable with any other function. Perfect for text at scale.

~5 min per useVery low effortCOPILOT functionText-heavy columns
Text classification and extraction at scale
Free-text columns become structured data in one formula. Classify, extract, normalise, summarise — in-cell, persistent, recalculating.
Use this when
You have a column of free text that needs to become a column of structure. Typical scenarios: stakeholder comments needing sentiment scores, project descriptions needing category tags, open-text survey answers needing theme coding, messy location strings needing standardisation.

Four ready-to-paste formulas

# In a new column next to your comments column: =COPILOT("Rate the sentiment of this comment as Positive, Neutral, or Negative", B2) # Then drag down. Or use the spilling form: =COPILOT("Rate each comment's sentiment as Positive, Neutral, or Negative", B2:B241)
# Categorise a project description against a fixed taxonomy. # L2:L7 holds your categories (Transport, Buildings, Energy, Water, Advisory, Other). =COPILOT( "Categorise this project description", B2, "using only the best matching option from this list", $L$2:$L$7 )
# Pull the department from a free-text "role" column. =COPILOT("Extract just the department name from this role description", C2:C200) # Pull the UK region from a messy address column. =COPILOT("Return the UK region (North, Midlands, South, Scotland, Wales, NI) for this address", D2:D200)
# Ask for a structured 2-column table output and tell Copilot to return it that way. =COPILOT( "For each comment, return a 2-column table with headers [Theme, Severity]. Theme options: Data quality, Communication, Scope, Budget, Schedule, Risk, Other. Severity: High, Medium, Low.", B2:B50 )
Quota and confidentiality gotchas
Hard limits: 100 calls per 10 minutes, 300 per hour. A dragged formula across 500 rows counts as 500 calls. Use the array form (B2:B500) not dragged single-cell form to save quota. Also: COPILOT cannot run in workbooks labelled Confidential or Highly Confidential under Purview — a deliberate data-protection feature. If your output matters permanently, Copy → Paste Values to freeze the result before the model version changes.
06 / USE CASE 3

Build a dashboard from scratch.

The most dramatic demo of vibe working. From a raw data table to a multi-sheet, chart-populated, pivot-driven dashboard in fifteen minutes. The workflow Microsoft's own demos lead with — because this is where the time savings are genuinely spectacular.

~20 minMedium effortEdit with CopilotGPT-5.2 recommended
Raw data → client-ready dashboard
One prompt kicks off a multi-step reasoning run. Watch Copilot plan, build pivots, add charts, apply conditional formatting, and produce an interactive dashboard.
Use this when
You have a clean dataset and need a visual summary for a client or internal team. Typical scenarios: portfolio KPI dashboards, project status overviews, benchmarking comparisons, risk registers, spend analysis. Not for anything that will be used for financial reporting or regulatory output — build those manually.
01
Data prepared as a Table on sheet 1
02
Save to OneDrive / SharePoint
03
Copilot pane → Edit with Copilot → GPT-5.2
04
Paste the dashboard spec prompt
05
Watch reasoning → iterate → accept
You are a senior data analyst building a portfolio dashboard for an Arup advisory client. ## The data Sheet "Projects" contains [describe — e.g. 40 live projects, monthly data Jan 2024–present, columns: Project, Region, Status, SpendToDate_GBP, BudgetTotal_GBP, ScheduleVariance_Days, RiskScore_1to10, ClientSector, SponsorContact]. ## Build me a dashboard on a new sheet called "Dashboard" Include these elements, in this order, top to bottom: 1. **Header** — a bold title "Portfolio Health · [Month Year]" and a subtitle showing total active projects, total spend YTD, and % on track. 2. **KPI row** — four large KPI cards with big numbers: - Projects on track (green if >80%, amber 60-80%, red <60%) - Total spend YTD (GBP) - Average schedule variance (days) - Average risk score 3. **Charts** (arranged 2×2 below the KPIs): - Bar chart: project count by region - Stacked bar: status breakdown per region - Scatter: risk score vs schedule variance, one point per project - Line: cumulative spend by month 4. **Top-5 risks table** — the 5 projects with the highest risk scores, with conditional formatting on the score column. 5. **Data refresh note** — single cell at the bottom showing "Dashboard generated [date] — refresh by re-running the Copilot prompt." ## Styling - Use Arup brand colours: primary red #E61E28, dark navy text (#0C1019), white or cream backgrounds (#F5F1E8). - Keep fonts consistent throughout. - Every chart should have a clear title and axis labels. ## How to work Show me your plan before building. Flag any assumptions you're making about the data. Build step by step so I can interrupt and correct.
Model choice matters here
For dashboard builds, GPT-5.2 tends to produce cleaner visual output, while Claude Opus 4.5 tends to produce more robust data logic. If your team has both models enabled, run the same prompt through each once to see which you prefer for your use case. For most Arup advisory dashboards, either is fine — the difference is marginal.
07 / USE CASE 4

Formulas — generate and explain.

The humble use case that still saves hours every week. Core Copilot writes formulas from plain language and explains formulas someone else wrote. Formula Completion — the newer sibling — suggests the right formula the moment you type =.

~1 min eachVery low effortCore CopilotFormula Completion
Stop Googling XLOOKUP syntax
Write what you want in plain English. Get the right formula. Explain any formula you don't understand. Two tools, both in-app, both free with your licence.
Use this when
You know what you want the data to do but can't remember the exact syntax. Or you've inherited a spreadsheet with formulas that are opaque. Or a DAX measure in a Power BI dataset needs explaining to a client sponsor.

Formula generation prompts

# Talk to Copilot in plain English — it returns a pasteable formula. Write a formula that calculates the variance between Budget (column E) and ActualSpend (column F), expressed as a percentage of Budget, and rounds to one decimal place. # Or for a column-level pattern: Suggest a formula column that flags any project where ScheduleVariance_Days is more than 30 AND RiskScore is 7 or higher, returning "Escalate" or blank. # Or lookup with fallback: I need to match ProjectCode in column A to the same code in Sheet "Lookup" column B, and return the Regional Lead from column D of that sheet. Return "Unassigned" if no match.
# Paste the formula or point to the cell — Copilot explains it. Explain what the formula in F12 does, step by step. I will present this to a non-technical client sponsor, so use plain language and give an example of what it returns for a typical row of my data. # For nested or complex formulas: This LAMBDA in cell J4 is throwing #VALUE errors. Explain what it's trying to do, what's likely wrong, and write me a corrected version.

Formula Completion — the newer trick

Type = in a cell. Wait two seconds. Copilot analyses the surrounding columns, headers, and nearby cells, and suggests a complete formula inline — tab to accept. No prompt needed. Works best when your column headers are descriptive (e.g. a column named YoY% triggers a year-over-year growth formula suggestion automatically). Currently US-English only on Excel for the web, rolling out to Windows Insiders. If you don't want it, disable via File → Options → Copilot.

The highest-leverage habit
When someone hands you a spreadsheet with formulas you don't immediately understand, don't Google — ask Copilot to explain each one. It takes five seconds per formula, builds your intuition for future sheets, and means you'll understand exactly what any inherited model is doing before you modify it. This single habit compounds into a significant skill uplift across a team.
08 / USE CASE 5

Rigorous analysis — Analyst agent.

When the answer ends up in a client deck, you can't afford to be wrong about the numbers. Analyst writes Python, shows its working, validates results, and produces publication-grade charts. The right tool for anything where accuracy is the deliverable.

~30 minMedium-high effortAnalyst agentCounts against 25/month cap
Python-backed analysis for client-grade output
The two-stage workflow from the PowerPoint report applies here too. Stage 1 builds the brief; Stage 2 hands the data to Analyst.
Use this when
Your output is going into a client deck, a board report, or any deliverable where getting the numbers wrong is a credibility problem. Typical scenarios: portfolio KPI analysis for government sponsors, infrastructure cost benchmarking, forecasting completion dates, regression on schedule-overrun drivers, survey response statistical analysis.
01
Prep data (use case 1 if needed)
02
Open Copilot Chat → switch to Analyst
03
Upload Excel + paste analytical brief
04
Review Python code + validation output
05
Iterate → export charts for deck
You are a senior Arup advisory consultant and data analyst. ## Data I have uploaded /Portfolio_Infrastructure_FY26.xlsx. It contains [describe — e.g. 127 live infrastructure projects across 8 regions, monthly data from Jan 2024, with project code, region, client sector, budget, actual spend, planned completion, actual completion, and a 1-10 risk score per project.] ## Analysis I need (use Python, show your code) 1. **Data validation** - Report any missing values, duplicates, obvious outliers - Flag projects with illogical data (e.g. negative variance where it shouldn't be) - Don't proceed until I confirm the data quality report 2. **Portfolio health** - % of projects on time, on budget, both, neither - Regional breakdown of the above - Trend over time (are things getting better or worse?) 3. **Risk analysis** - Correlation between RiskScore and ScheduleVariance - Top 10 projects by risk-weighted overrun exposure - A simple regression: what predicts schedule overrun best? 4. **Forecasting** - Project expected completion for each live project using historical velocity - Aggregate expected portfolio completion by region ## Output format - Show your Python at each step so I can audit the logic - Use Arup brand colours in charts (primary red #E61E28, dark navy text) - At the end, produce a numbered list of 5-7 "so-what" findings I can lift directly into a client deck Start with data validation. Wait for my OK before continuing.
Why Analyst beats Edit with Copilot for this
Edit with Copilot is excellent at reshaping and building. It's weaker at rigorous statistical analysis because its reasoning happens in natural language, not Python. Analyst writes Python, runs it, shows you the output, and shows you the code. For anything where you'd want to peer-review the calculation, Analyst is the right tool — because the calculation is visible. For anything where you just want a clean workbook fast, Edit with Copilot wins.
09 / USE CASE 6

Reconcile data across multiple files.

Edit with Copilot only sees the currently open workbook — a real limitation. Analyst sees all the files you upload. The cross-file workflow belongs in Copilot Chat, not in Excel itself.

~40 minHigh effortAnalyst (multi-file)Counts against 25/month
Four files become one reconciled view
Cost register, schedule register, risk register, contractor submission. Different formats, different cadences, different owners. One Analyst session pulls them together.
Use this when
Analysis requires combining data from multiple sources that were never designed to talk to each other. Typical scenarios: month-end reconciliation across cost/schedule/risk; merging submissions from multiple contractors into one comparison; combining output from different practice areas on a shared project; any "single view" reporting where the inputs live in silos.
You are a senior Arup advisory consultant doing month-end portfolio reconciliation. ## Uploaded files (all of these) - /Cost_Register_March26.xlsx — finance team's cost tracker - /Schedule_Register_March26.xlsx — PMO schedule tracker - /Risk_Register_March26.xlsx — risk owner's log - /Contractor_Submission_ACME_March26.xlsx — contractor's view ## The reconciliation problem Each file uses a different project identifier convention: - Cost register uses "Project Code" (e.g. ARUP-24-0312) - Schedule register uses "Project ID" (e.g. P312) - Risk register uses "Ref" (e.g. R-0312) - Contractor uses "Works Package" (often a free-text name) I believe they all refer to the same ~40 underlying projects, but the joins are not obvious. ## What I need 1. **Identity reconciliation** - Propose a joining key for each file. Show me your logic. - Produce a single crosswalk table mapping all four identifier systems to a canonical project code. - Flag any project that appears in one file but not others. 2. **Reconciled view** - Build a single "Master" table with one row per project and columns from all four sources: budget, spend, schedule variance, open risks, contractor status. - Note any value conflicts (e.g. cost register shows £1.2m, contractor shows £1.35m). 3. **Exception report** - Projects with data in only some files - Projects where sources disagree by >5% on any metric - Top-10 highest-priority items for me to investigate ## Ground rules - Python every step. Show the code. - Don't invent mappings — if a join is ambiguous, flag it and ask before resolving. - Output format: a clean Excel file I can download. Start by inspecting the four files and proposing the identifier crosswalk. Wait for my review.
When the cross-file join really is too hard for AI
Analyst is genuinely impressive at cross-file reconciliation when the join is inferable. It cannot reliably join files where the relationships are only in someone's head — where "Project Phoenix" in one file is called "Stage 2" in another because of an organisational history you haven't told it. For those, the prep work is human: build the crosswalk yourself, then use Analyst for the actual reconciliation. The AI can only join on what's visible in the data.
10 / DECISION MATRIX

Which tool, when?

Six use cases, five tools, one table. Match your scenario on the left, read the tool on the right.

Your scenarioUse thisSurfaceTime
Messy file needs cleaning into a usable TableEdit with CopilotExcel app~15 min
Column of free text needs structured labelsCOPILOT() functionCell formula~5 min
Build a multi-chart dashboard from a clean tableEdit with CopilotExcel app~20 min
Need a formula but can't remember syntaxCore Copilot + Formula CompletionExcel app~1 min
Rigorous statistical analysis for a client deckAnalyst agentCopilot Chat~30 min
Reconcile data across 3+ Excel filesAnalyst agentCopilot Chat~40 min
Just ask a question about a workbookCopilot Chat (upload file)Copilot Chat~2 min
11 / DO'S & DON'TS

Common failures — and their fixes.

Patterns from training the team on these tools over the past six months. Almost every bad Copilot-in-Excel experience maps to one of these.

Do
  • Convert your data to a Table (Ctrl+T) before inviting Copilot in. Named Tables are first-class objects; loose ranges are not.
  • Save a backup before any Edit with Copilot session — the tool has no preview mode, changes happen live.
  • Work in a new sheet, not in place. Tell Copilot explicitly to produce its output in a new sheet labelled "Cleaned" or "Dashboard."
  • Ask for a plan first, not an execution. "Show me your step-by-step plan before making changes" is the best opening line in Edit with Copilot.
  • Use the COPILOT() array form (B2:B500) rather than dragging single-cell formulas to save your quota.
  • Freeze COPILOT() outputs with Copy → Paste Values once you're confident. The model will evolve, and so will your results.
  • Escalate to Analyst when the output is going into a client deck. Python you can see beats reasoning you can't.
  • Explain inherited formulas before modifying them. Five seconds of Copilot explanation prevents hours of debugging.
Don't
  • Use COPILOT() for numerical calculations. It's non-deterministic. SUM, AVG, and IF exist for a reason.
  • Use COPILOT() for lookups. XLOOKUP is deterministic and reliable. COPILOT() will occasionally make up plausible-looking but wrong values.
  • Use Copilot on a production client model without a backup. Ever.
  • Use Copilot for financial reporting, legal outputs, or regulatory deliverables. The non-determinism is the problem — you can't audit what you can't reproduce.
  • Burn your 25/month Analyst cap on questions a PivotTable could answer. Save them for genuinely hard work.
  • Trust a Copilot-generated number on faith. Every client-facing number gets a human spot-check against a known calculation.
  • Leave Edit with Copilot running unattended. Watch its reasoning. Hit Stop the moment it goes off-track. Don't wait and pray.
  • Forget that COPILOT() won't run on files with sensitivity labels. A Confidential-labelled workbook won't calculate the function at all.
The one mental model that ties it all together
Think of Excel + Copilot as three surfaces at three speeds. In the cell — the COPILOT() function for persistent AI work that recalculates. In the app — Core Copilot for quick help, Edit with Copilot for multi-step tasks on the current workbook. Outside the app — Analyst for anything that crosses files or needs peer-reviewable rigour. Almost every Arup advisory Excel scenario fits cleanly into one of these three buckets. The hard part isn't the tools — it's knowing which bucket you're in.
Playbook implication
Excel is where the advisory team spends most of its analytical time, and where Copilot delivers its biggest hour-for-hour returns. This chapter is second only to PowerPoint in likely impact — because once the team internalises vibe working, the productivity floor moves up permanently.