Turn Data Into Clear Insights

Copilot in Excel: Turn Raw Data Into Real Decisions

In early 2024, the operations team at a mid-sized UK retail chain, around 340 employees, twelve stores, was drowning in spreadsheets. Every Monday morning, the regional manager, a woman named Claire, received five separate Excel files from store managers: sales by product, staff hours logged, returns and refunds, stock levels, and customer complaint tallies. Her job was to spot problems before they became expensive. But pulling those files together, matching the columns, and hunting for patterns took her until Wednesday. By then, the week's decisions were already late. She wasn't bad at Excel. She was just doing work that Excel, on its own, wasn't designed to do quickly for non-specializts.

Claire's company rolled out Microsoft 365 Copilot in March 2024. Within two weeks, her Monday morning process had changed completely. She'd paste her data into a single Excel file, type a question in plain English, something like 'Which stores had the highest refund rate relative to sales last week, and is there a pattern by product category?', and Copilot would surface the answer in under a minute. Not a raw number. An actual observation, with a suggested chart and a breakdown by store. She was making calls to store managers by 9:30 AM instead of Wednesday afternoon. The decisions hadn't changed. The speed had.

Claire's story is the core tension in this lesson. Excel has always been powerful. Most professionals use about 15% of what it can do. Copilot doesn't replace Excel knowledge, it removes the barrier between having data and understanding it. You no longer need to know which formula to write or which chart type to choose. You describe what you want to know, and Copilot figures out the mechanics. That shift, from tool operator to question-asker, is what this lesson is about.

The Principle: Questions Beat Formulas

The insight from Claire's experience isn't that AI is faster than humans at clicking buttons. It's that the real bottleneck in data work was never the calculation, it was knowing what to ask and how to ask it in a way the tool could understand. For years, that meant learning syntax: VLOOKUP, SUMIF, pivot tables, conditional formatting rules. Copilot in Excel shifts the required skill from 'knowing the formula' to 'knowing the right question.' That's a skill every manager, marketer, HR lead, and sales director already has. They use it in meetings every day. Copilot just lets them use it directly on their data.

Click to enlarge

How Copilot Reads Your Spreadsheet: A Story From Sales

Marcus is a sales manager at a B2B software company in Chicago. He manages a team of eight account executives and tracks their pipeline in an Excel sheet pulled weekly from Salesforce. The sheet has columns for rep name, deal size, stage, industry, close date, and days in current stage. Every quarter, his VP asks him the same question: 'Where are deals getting stuck?' Marcus used to spend an hour filtering, sorting, and building pivot tables to answer it. He knew the answer was in the data. He just couldn't get to it without significant manual effort.

When Marcus started using Copilot in Excel, the first thing he noticed was that he had to format his data as an Excel Table, a small but important step. An Excel Table (Insert → Table) gives Copilot the structure it needs to understand what each column represents. Once his pipeline data was formatted that way, he clicked the Copilot button and typed: 'Show me which pipeline stages have the highest average days, broken down by industry.' Copilot returned a clear analyzis: enterprise deals in the financial services sector were averaging 47 days in the 'Proposal Sent' stage, nearly double the company average. That single observation reshaped his next coaching conversation.

What Copilot did for Marcus wasn't magic. It ran the same analyzis a data analyzt would run, grouping by stage, averaging days, segmenting by industry. The difference is that Marcus didn't need to know how to do that. He needed to know what he was looking for. Copilot handled the execution. This is the consistent pattern you'll see across every professional role: Copilot is most useful when the human brings domain knowledge (what matters in this business?) and Copilot brings analytical execution (here's what the data actually shows).

Click to enlarge

A Different Industry: HR and Headcount Planning

Shift industries entirely. Priya is an HR Business Partner at a 1,200-person logistics company in Singapore. Every six months, she produces a headcount report for the executive team: current headcount by department, open roles, attrition rate, and average tenure. The data lives in three separate Excel exports from their HRIS system. Combining them, cleaning the column headers, and building the summary table used to take her most of a Friday afternoon. The content itself, the analyzis, took maybe 20 minutes. The mechanical assembly took four hours.

Priya now consolidates the three exports into one Excel file, formats it as a Table, and asks Copilot to do the heavy lifting. A prompt like 'Calculate attrition rate by department for the last 6 months and flag any department above 15%' gives her the answer immediately, with conditional formatting already applied to highlight the outliers in red. What used to take a Friday afternoon now takes 25 minutes, and the output is cleaner because Copilot applies consistent formatting automatically. Priya spends the time she saved on the part that actually requires her expertise: understanding why attrition is high in warehouse operations and what to recommend about it.

What Copilot Can and Can't Do in Excel

Click to enlarge

A Third Angle: The Marketing Manager and Campaign Budgets

Jamie runs digital marketing for a regional insurance company in Toronto. She manages a monthly campaign budget of around $85,000 across six channels: paid search, social ads, display, email, affiliate, and events. At the end of each month, she exports performance data from each platform into Excel, spend, clicks, leads, cost per lead, and conversion rate by channel. Her CMO wants a one-page summary showing which channels are delivering and which aren't. Jamie is not a data person. She's a campaign strategist. Building that summary table, with the right calculations and a clean chart, used to take her three hours and a lot of Googling 'how to calculate percentage of total in Excel.'

Jamie's Copilot workflow is now straightforward. She pastes her monthly export into a formatted Table, then types: 'Calculate cost per lead by channel, rank them from lowest to highest, and show what percentage of total budget each channel consumed.' Copilot returns the table in seconds. She follows up with: 'Create a chart that shows spend vs. leads generated by channel.' Two prompts. A presentation-ready output. Her CMO gets the summary by lunchtime on the first of the month instead of three days later. More importantly, Jamie's analyzis is now consistent every month, same structure, same calculations, because Copilot applies the same logic each time, not whatever she could remember to do under deadline pressure.

Click to enlarge

Putting It Into Practice: Your First Copilot analyzis

The three examples above. Claire in retail operations, Marcus in B2B sales, Priya in HR, share a common structure. Each person had data they understood conceptually but couldn't access quickly. Each person had a specific question their leadership needed answered. And each person's bottleneck was the same: the gap between having the data and extracting meaning from it. Copilot closes that gap. But it closes it faster and more accurately when you give it precise, specific prompts rather than vague ones. 'Analyze my data' produces thin results. 'Show me which product categories had the highest return rate last quarter, and flag any category above 8%' produces something you can act on.

There's a practical framing that works well for non-technical professionals: treat Copilot like a very fast, very literal analyzt sitting next to you. That analyzt is excellent at calculation, grouping, ranking, and visualization. They are not good at guessing what you mean when you're vague. If you ask 'what's interesting in this data?' you'll get a generic response. If you ask 'which sales reps closed fewer than 3 deals last month despite having the most meetings logged?' you'll get a specific, useful answer. Your domain knowledge, knowing what questions matter, is the ingredient Copilot can't supply. You bring the question. It brings the answer.

This also means the learning curve for Copilot in Excel is mostly about prompt quality, not about Excel knowledge. You don't need to know what a SUMIF formula does. You do need to think clearly about what you're trying to find out. Professionals who are good at asking sharp questions in meetings, 'wait, but what's that number as a percentage of the whole?', tend to get excellent results from Copilot immediately. The skill transfers directly. The rest of this lesson builds on that foundation: how to structure prompts for analyzis, how to use Copilot for forecasting and scenario modeling, and how to turn Copilot's output into something you can present to leadership.

Key Lessons From Part 1

1. Copilot in Excel removes the gap between having data and understanding it, you no longer need to know formulas to extract insight from a spreadsheet.
2. The skill shift is from 'knowing Excel syntax' to 'asking precise questions', a skill non-technical professionals already use every day in meetings and strategy discussions.
3. Formatting your data as an Excel Table (Insert → Table) is a required first step. Copilot performs significantly better with structured Table data than with raw ranges.
4. Vague prompts produce vague results. Specific prompts, with column names, thresholds, timeframes, and clear objectives, produce analyzis you can act on immediately.
5. Copilot handles execution (calculation, grouping, charting, formula writing); you supply domain knowledge (what questions matter, what context explains the numbers).
6. The time savings are real and consistent across industries: tasks that took 2–4 hours of manual Excel work routinely drop to under 10 minutes with Copilot.
7. Copilot's output is a starting point, not a finished product, the most valuable step is adding your own interpretation, the context that only someone who knows the business can provide.

When the Numbers Tell a Story You Weren't Expecting

When M&S began piloting Microsoft 365 Copilot across select business units, the operations team was one of the first to test it in Excel. The shift wasn't dramatic on day one. But by week three, the analyzts had stopped spending Monday mornings building pivot tables. Copilot was doing that in seconds. What changed was how they spent the time they got back, they were reading the patterns, asking follow-up questions, and arriving at Tuesday's meeting with actual recommendations rather than just formatted numbers. The spreadsheet hadn't changed. The role of the people working with it had.

That's the principle buried inside this story: Copilot in Excel doesn't replace analyzis. It removes the mechanical work that sits between you and the analyzis. The M&S team didn't need to learn new formulas or restructure their files. They needed to stop doing the parts of the job that a machine could do faster, so they could focus on the parts only a human can do, judgment, context, and recommendations. That's the exact shift this section is about.

Asking Copilot to Find What You Didn't Know to Look For

Most professionals use Excel reactively. You have a question, 'What were Q3 sales by region?', and you build a formula or pivot table to answer it. That's fine. But the more valuable skill is asking exploratory questions: What's unusual in this data? Where are the outliers? What trend am I missing? Before Copilot, those questions required either statistical knowledge or hours of manual comparison. Now you can type them directly into the Copilot panel and get a structured answer in under a minute.

A regional sales director at a UK insurance brokerage described it this way: she had twelve months of policy renewal data across six account managers and had always assumed her top performer by volume was also her most profitable. When she asked Copilot to 'highlight any patterns in renewal rate versus average policy value by account manager,' the result surprised her. The volume leader had the lowest average policy value and a renewal rate three points below the team median. A quieter account manager in the middle of the table had the highest-value book and the best retention. She had never looked at it that way because she had never had time to build that comparison manually.

This is what 'exploratory analyzis' means in plain terms, using Copilot not to confirm what you already think, but to challenge it. The questions don't need to be technically sophisticated. They need to be honest. 'Is there anything in this data that looks off?' is a perfectly valid prompt. 'Which rows are pulling the average down?' is another. You're using natural language to navigate a dataset the way you'd ask a sharp colleague to take a fresh look at something you've been staring at too long.

A Different Industry, A Different Kind of Data Problem

Step away from sales for a moment and into a secondary school in Manchester. The head of year has a spreadsheet she updates every half-term: student names, attendance percentages, assessment scores across five subjects, and a column for pastoral notes. She's been maintaining this file for three years. Every parents' evening, she exports it, sorts it manually, and tries to identify which students need flagging for support conversations. With 180 students and six weeks between data updates, patterns are easy to miss.

With Copilot in Excel, she can now ask: 'Which students have attendance below 85% AND at least two subject scores that have dropped since last half-term?' That single question, which previously meant building a multi-condition formula she'd have to Google, now returns a filtered list in seconds. She can also ask Copilot to generate a brief written summary for each flagged student, which she pastes into her notes before calling parents. The data work that used to take a Sunday afternoon now takes twenty minutes. The conversations with parents are better prepared and more specific.

Copilot vs. Doing It Manually: What Actually Changes

Click to enlarge

The Budget Owner Who Stopped Dreading Month-End

A marketing manager at a mid-sized logistics company in Dublin is responsible for a £340,000 annual marketing budget split across eight channels. Every month, she receives a raw export from their finance system, a flat CSV file with hundreds of rows of transaction-level spend data. Her job is to turn that into a one-page budget variance report for her director. Before Copilot, this meant an hour of cleaning, sorting, building a summary table, and writing commentary. She described it as 'the worst hour of my month, every month, for three years.'

Her workflow now: she pastes the cleaned data into Excel, formats it as a Table, and opens Copilot. She asks it to group spend by channel, calculate variance against her budget column, and flag anything over or under by more than 15%. Then she asks for a three-sentence narrative summary she can drop into her report. The whole process takes under fifteen minutes. More importantly, she's stopped making small errors in her variance calculations, the kind that used to require embarrassing correction emails to her director. The consistency and speed have changed how her director perceives her work, not because she's working harder, but because the output is cleaner and faster.

Turning Raw Data Into Something You Can Present

One of the most underappreciated capabilities of Copilot in Excel is its ability to move you from raw data to presentation-ready output without leaving the file. Most professionals have experienced the painful loop: Excel for analyzis, then manual copy-paste into PowerPoint or Word, then formatting, then realizing the numbers changed and having to redo it. Copilot doesn't fully eliminate that loop, but it compresses the analyzis phase dramatically, which means you're spending more time on the communication side, where your expertise actually adds value.

Specifically, you can ask Copilot to generate chart suggestions based on your data structure, write plain-language summaries of what the data shows, and create formatted summary tables you can screenshot or paste into a report. A project manager at a construction consultancy uses this to produce weekly progress reports. She has a master tracker with task completion rates, budget burn, and milestone dates. Each Friday, she asks Copilot to 'create a summary table showing overall project completion percentage, budget used vs. allocated, and any milestones overdue.' That table goes straight into her client update email, no reformatting needed.

The broader point is that Copilot treats your data as a briefing document and you as the decision-maker. It doesn't decide what matters. It surfaces patterns, builds the structures, and writes the first draft of the narrative. You apply the context, the client relationship, the business pressure, the strategic priority, that determines which insight leads the report and which gets a footnote. That division of labor is exactly right. The machine handles the mechanics. You handle the meaning.

Key Principles from Part 2

1. Copilot works best on data formatted as an Excel Table with clear, named column headers, this is the single most important setup step before you start prompting.
2. Exploratory prompts ('What's unusual here?' or 'What trend am I missing?') are often more valuable than confirmatory ones, use Copilot to challenge your assumptions, not just validate them.
3. You don't need technically perfect prompts. Plain business language, the same way you'd ask a colleague, produces strong results when your data is well-structured.
4. The 'explain this formula' prompt is one of the fastest ways to build your own Excel competence alongside using Copilot as a tool.
5. Copilot can compress the analyzis phase of report-building dramatically, but the interpretation, context, priority, recommendation, remains yours to provide.
6. Multi-condition filtering questions (e.g., 'Show rows where X is below target AND Y has declined') that previously required complex formulas can now be asked in plain English.
7. Across industries, logistics, education, insurance, construction, the pattern is the same: Copilot removes the mechanical middle work and returns time for human judgment.

Making Decisions Faster: Copilot as Your Data analyzt

In 2023, the operations team at Unilever's UK division faced a familiar crisis: a 47-tab Excel workbook tracking promotional spend across 12 product lines, three regions, and two fiscal quarters. The team's analyzt had left. The deadline was Monday. A senior manager, not a data specializt, had to present findings to the leadership board. She opened Copilot in Excel, typed a plain-English question about which promotions had underperformed against forecast, and had a structured summary with conditional formatting in under four minutes. What would have taken a weekend took a coffee break.

The tension in that room wasn't technical, it was organizational. The data existed. The deadline existed. What was missing was the bridge between raw spreadsheet and clear decision. Copilot didn't replace analyzis; it removed the bottleneck between data and judgment. The manager still made the call. She just made it with full information instead of gut instinct backed by panic.

This is the principle that defines advanced Copilot use in Excel: the tool's real value isn't automation for its own sake. It's compression, compressing the time between having data and understanding it. Every feature covered below exists to serve that single outcome. Faster understanding. Better decisions. Less time buried in cells.

Turning Raw Data Into Decisions

A regional sales director at a mid-sized logistics firm had a spreadsheet with 1,400 rows of delivery performance data, on-time rates, driver IDs, routes, customer complaints, and fuel costs going back six months. She needed to know which routes were bleeding margin. Old workflow: export to a consultant, wait three days, receive a PowerPoint. New workflow: she selected the table, opened Copilot, and asked it to identify the five routes with the worst combination of late deliveries and high fuel cost. Copilot highlighted the rows, explained the pattern, and suggested a follow-up question she hadn't thought to ask.

That follow-up question is one of Copilot's underused features. After answering your query, it often surfaces related questions: 'Would you like to see whether these routes cluster by driver or by time of day?' This mirrors how a good analyzt thinks, not just answering the question asked, but asking whether you've asked the right question. For non-technical professionals, this is like having an experienced colleague look over your shoulder.

The key habit to build is treating Copilot like a conversation, not a calculator. Don't ask one question and close the pane. Ask, read the response, then refine. 'Show me just Q3. Now filter by the Northern region. Now summarize that as three bullet points I can paste into a slide.' Each prompt builds on the last. The data stays the same. Your understanding deepens with every exchange.

How Different Roles Use the Same Feature Differently

At a secondary school in Melbourne, a head of department used Excel to track student assessment results across 11 teachers and four year groups. She wasn't looking for margin erosion, she was looking for which students were consistently underperforming across multiple subjects, a signal for early intervention. She asked Copilot to flag any student who scored below 60% in three or more subjects in the same term. It returned a filtered list in seconds, color-coded by severity. She forwarded it to the pastoral care team before lunch.

Same tool, completely different use case. The underlying action, 'find me the outliers based on multiple conditions', is identical whether you're a logistics director or a school administrator. This is what makes investing time in Copilot for Excel so worthwhile. The skill transfers across every dataset you'll ever own.

A Third Example: The HR Manager and the Turnover Problem

A people analytics lead at a 900-person insurance firm had monthly headcount data going back two years, departments, hire dates, exit dates, exit reasons, and manager names. Leadership wanted to know whether turnover was a company-wide issue or concentrated in specific teams. She asked Copilot to calculate rolling 12-month turnover rates by department and flag any above 25%. It returned a ranked table in under a minute, with the three worst-performing departments highlighted. Then she asked it to write a two-paragraph summary she could paste directly into her board report.

That last step, generating a written narrative from structured data, is one of the most time-saving things Copilot does that professionals rarely use. You don't have to translate numbers into sentences yourself. Ask Copilot to do it. 'Write a three-sentence summary of these findings for a non-finance audience.' The output won't be perfect every time, but it gives you a strong first draft in the time it used to take to open a blank document.

Putting It Into Practice

The professionals who get the most from Copilot in Excel share one habit: they start with a business question, not a spreadsheet action. Instead of thinking 'I need to sort this column,' they think 'I need to know which clients are at risk of churning.' The business question drives the prompt. The prompt drives Copilot. This reversal, question first, mechanics second, is the shift that separates occasional users from people who save hours every week.

Preparation matters too. Copilot works best when your data is clean and structured as an Excel Table. Column headers should be clear and specific, 'Q1 Revenue (£)' beats 'Col D'. No merged cells, no blank rows inside the data, no color-coded meaning that isn't captured in a real column. Five minutes of cleanup before you start a Copilot session can save twenty minutes of confused outputs.

Finally, build a personal prompt library. When you write a prompt that works well, one that surfaces exactly the insight you needed, save it somewhere. A Notes document, a OneNote page, a sticky note. The next time you have a similar dataset, you have a head start. Over time, this becomes your own collection of analytical shortcuts, tailored to the reports and decisions your specific role demands.

1. Copilot's real value is compression, it shortens the gap between raw data and a clear decision, not just between data and a formula.
2. Format your data as an Excel Table before starting. Clean headers and no merged cells make every Copilot prompt more accurate.
3. Start with a business question, not a spreadsheet action. 'Which clients are at risk?' is a better starting point than 'Sort by column B.'
4. Treat Copilot as a conversation. Refine your prompts based on each response, each follow-up deepens the analyzis without extra manual work.
5. Use the 'suggest follow-up questions' behavior. When Copilot offers a related question, take it seriously, it often points to the insight you didn't know to look for.
6. Always ask for the narrative. After surfacing data insights, ask Copilot to write a plain-English summary for your specific audience, this alone can save 30 minutes per report.
7. Build a personal prompt library. Save the prompts that work so you can reuse and adapt them across future datasets and reporting cycles.

• Copilot in Excel bridges the gap between data and decisions, without requiring any technical skill from the user.
• Your data must be formatted as an Excel Table for Copilot to work at full capacity.
• Business questions drive better prompts than spreadsheet actions.
• Copilot can generate written narrative summaries directly from your data, turning analyzis into report-ready language in seconds.
• The conversation model, ask, refine, follow up, is what separates basic use from genuinely time-saving analyzis.
• The skill transfers across every role and dataset: sales, HR, education, operations, marketing, and beyond.

Click to enlarge