M365Con 2027 – free online conference · 11–22 Jan 2027 · 120 sessions→ m365con.net

2026-11-16

Microsoft Copilot in Excel - Simply Explained

Follow the showNew episodes on Microsoft 365, Copilot and more, free in your podcast app.Apple PodcastsSpotify
Microsoft Copilot in Excel - Simply Explained
Copilot in Excel is not the magic button everyone promised. It's the AI assistant built right into Excel, and it writes formulas, builds charts, and tidies messy data from a sentence you type. In this episode of Simply Explained you will learn: - What Copilot in Excel really is - The three ways to…

Show notes

Copilot in Excel is not the magic button everyone promised. It's the AI assistant built right into Excel, and it writes formulas, builds charts, and tidies messy data from a sentence you type. In this episode of Simply Explained you will learn:

  • What Copilot in Excel really is
  • The three ways to talk to Copilot, and when to use each one
  • What it can do on a real spreadsheet
  • Where its answers come from and why your data stays safe
  • Do you already have it, and what it costs
  • The mistakes that make it look broken
  • The honest limits you need to know
  • How it fits into Microsoft 365 and Azure, and what to do next

Simply Explained by M365.FM breaks down Microsoft 365, Azure and modern IT topics in clear, jargon-free language. Hosted by Mirko Peters. Become a supporter of this podcast: https://www.spreaker.com/podcast/m365-fm-microsoft-365-azure-cloud-power-platform-ai--6704921/support. This episode includes AI-generated content.

Host: Mirko Peters

Guests

Transcript

📄 Transcript (TXT)

Read the full transcript

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. What Copilot in Excel really is copilot in Excel is not the magic button everyone promised. It's the AI assistant built right into Excel and it writes formulas, builds charts, and tidies messy data from a sentence you type you might expect that makes your spreadsheet work automatic, but clean data and a clear question still decide whether it helps you or wastes your afternoon. Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. By the end, you will know what Copilot in Excel does well, where it stops, and the small handful of mistakes that make it look broken. Here's the plain version. Copilot in Excel is an assistant that lives inside the Excel app you already use. You describe what you want. In normal words, it works out the steps and it makes the changes using Excel's own tools, which means tables, charts, and formulas. Nothing gets locked away because every formula it writes stays editable, and you can undo anything it does. When you type something like. Summarize the sales table by region. copilot does more than reply in a chat box. It builds the summary on a new sheet inside the workbook while you watch. That's the part that surprises people the first time it's built into Excel itself, and the changes land in the file you already had open. Microsoft pushed this a lot further in April of twenty twenty six, when what it calls agent mode became the standard experience for paying copilot customers. Instead of answering one question at a time. It now plans a task, carries it out step by step, checks its own result, and corrects the mistakes it spots along the way. Watching it work feels less like searching and more like handing a job to someone. Honestly, it's a very fast assistant that never gets tired and never wanders off. It drafts the work and you decide what to keep. That single idea explains almost everything that follows, including the parts that will frustrate you. One more thing worth saying early this isn't a separate app, and there's nothing to install. If you can describe the job in a sentence, you can ask for it. And that's the entire interface. It also sits in the same Excel you already open every day, so there's no new window to learn and no new file type to worry about or to tackle the three ways to talk to copilot and when to use each one. Copilot in Excel gives you three different ways to work, and choosing the wrong one is the first thing that trips people up the name. Sound technical, but the idea behind them is simple. You can let it change your workbook. You can ask it to show you a plan first, or you can talk to it without it touching anything at all. The first one is edit mode and it's what you get by default. This is the powerful one. Copilot changes the workbook directly so it can reshape a messy list, merge two sheets, or build a report with several moving parts. You watch each change appear in front of you, and you can stop it at any moment. If it heads somewhere you don't like. The second is plan mode, which is the careful one. Instead of acting straight away, copilot writes out the steps it intends to take and then it waits. You read the plan, you approve it, and only then does it begin. For anything that touches real numbers, reach for this mode first, because you get to catch a wrong assumption before it turns into a wrong spreadsheet. The third is chat mode, and this one is the safe one. Copilot reads your data and answers your questions, but it leaves the workbook exactly as it found it. Ask it to explain a formula. A colleague left behind, or to tell you which product line dropped last quarter. And it will answer without changing a single cell. The rule I would give anyone starting out is short use chat to understand use. Plan to be careful and use edit when you're ready to get the job done. If you remember nothing else from this section, remember that order. One practical note and it's worth knowing before you go hunting for buttons that aren't there, you get the full set of modes on windows, on a Mac, and in the browser on a phone. The experience is thinner, with chat on Android and iPhone and Chat plus plan on an iPad. So if you want the real thing, start on a desktop or in the web app, because that's also where the newest features land first. If you're ever unsure which mode you're in, look at the toggle at the top of the copilot pane. It tells you plainly whether editing is switched on, and switching it off is the quickest way to keep copilot harmless. While you experiment with what it can do on a real spreadsheet. Enough theory. Picture a sales export you have just downloaded full of product names, regions, dates, and amounts and you have been asked for a summary by tomorrow morning. That's the exact kind of job copilot was built for. So let me walk through what it does with it. First comes the formula you can't remember. You describe the calculation in plain words, and copilot writes the formula for you across one column or across several sheets. If someone left behind a formula that looks like alphabet soup, you can ask what it does and you get a straight answer instead of a search result and a headache. Next comes the summary. You ask for the totals by region, and copilot builds a pivot table on a fresh sheet. A pivot table is Excel's own way of squashing a long list into a small, readable grid, and it's always been one of the earliest things to set up by hand. Copilot handles the setup from your sentence. Then comes the chart. Ask for a column chart of those totals, and it appears still linked to the data underneath. If the numbers change, the chart follows because it's a real Excel chart and not a picture. The pivot table behaves the same way, keeping its connection to the source rows. Then there's the cleanup, which is often where the real time disappears. Copilot can find duplicate entries, spot dates that were typed in three different formats, and flag columns where a number has turned into text. Microsoft added a dedicated housekeeping feature for exactly this in October of twenty twenty six, and it's one of the most useful things in the whole package. There's also the newer work and you should know it exists. You can now ask copilot to build a dashboard that stays connected to your data. So the report refreshes itself when the numbers move. You can record the steps you do by hand and turn them into a reusable task. You run again next month and you can pull data in from another workbook or from the web with the sources cited for you. All of that adds up to the same promise, which is that copilot removes the blank page problem and hands you a solid first draft. It isn't the finished product, and it isn't pretending to be. The best way to use it is to let it do the boring eighty percent, and then bring your own judgment to the rest. There is one thing that decides whether any of this works for you, and it has nothing to do with copilot itself. Hold that thought, because we come back to it in a few minutes where its answers come from and why your data stays safe. There's a worry that stops a lot of people from trying any of this. They picture their company's numbers going into an AI tool, and they want to know where those numbers end up and who gets to see them. The answer starts with how copilot is built, and it's less scary than it sounds. There are two halves working together. The first half is a large language model, and that's the piece that understands your sentence and writes fluent replies. The second half is Microsoft Graph, which is the secure doorway to your own organization's data. Things like your files, your emails, and your spreadsheets. The model supplies the language and graph supplies your context, so the answer is grounded in your real work instead of a generic guess pulled off the internet. That connection is where the safety comes from. Copilot only reaches data that the signed in person already had permission to see. It doesn't open doors that were locked for you if you couldn't open a file yesterday. Copilot can't open it for you today, which is a simple idea and a reassuring one. It also means the tool is only ever as tidy as your permissions are, which is a good reason to keep them clean. Then there's the training question, the one people ask most. Microsoft states plainly that your prompts, your responses, and the data accessed through Microsoft Graph aren't used to train the foundation models. Your spreadsheet isn't being fed back into the machine to make it smarter for somebody else. There's also an audit trail, and it does more work than you might expect. Prompts and responses are logged, and they can fall under the retention and audit policies your organization already applies to email and files. That's the reason a cautious IT team can say yes to this at all. And it's why the tool shows up in workplaces rather than just in demos. The one caveat worth noting is about the web. When copilot searches the internet, that search is handled separately and leaves the Microsoft three hundred sixty five boundary, so the strictest rules don't cover it. For everyday spreadsheet work, the short version holds up. It sees what you can see, and it doesn't train on your data. Do you already have it? And what it costs? Now for the bit that catches almost everyone off guard, you might already have copilot in Excel and simply never noticed, because a lot of Microsoft three. Sixty five business plans now include a standard level of access at no extra charge. The button is sitting there and nobody told you. Microsoft splits it into two levels. The standard level comes with eligible plans and covers the everyday work. The paid level sold as an add on, adds priority access and a few extra capabilities. You don't have to guess which one you have, because Excel labels it for you in the corner of the copilot pane, with one label for the included version and another for the paid one. If you're paying for it, here are the numbers as they stood in October of twenty twenty six, the small business add on lists at twenty one dollars per user, per month on an annual commitment, and Microsoft was running it at eighteen dollars until the end of the year. The enterprise add on lists at thirty dollars per user per month, and that sits on top of a qualifying base plan rather than replacing it. Prices vary by country, and they don't include tax, so treat these as a shape rather than a quote. One detail people miss is that some of the advanced AI features are billed by usage instead of a flat fee. Microsoft calls those units copilot credits, and you only pay when you use the heavier experiences. For a beginner poking around in Excel, that's unlikely to matter, but it's useful to know that the bill isn't always one clean number, and there's a free layer that almost nobody knows about. In September of twenty twenty five, Microsoft added a lighter copilot chat to Excel, word, PowerPoint, outlook, and OneNote. For anyone with a Microsoft three hundred sixty five subscription with no copilot license required. So if you spot a copilot button, that doesn't automatically mean somebody bought the expensive version. It might just be the free chat that showed up on its own. My advice for a beginner is to start with whatever you already have. Try it on one small, tidy spreadsheet. See what it saves you and let the paid tiers earn their place before you spend a cent. The mistakes that make it look broken. Now to the part that will save you the most frustration when copilot gives you a bad answer. The problem is almost never copilot. Most of the time it's the workbook. And once you see why the whole tool stops feeling mysterious and starts feeling useful, the first mistake is messy data. If your amount column holds numbers in some rows and text in others, or if your dates are written three different ways, copilot will hand you a wrong total. It's doing its best with what you gave it, and it can't fix a column that disagrees with itself. Garbage in. Garbage out was true long before AI showed up. The second mistake is skipping the table. Excel has a proper table format, and copilot works far better inside it because a table tells the tool where the headers are and where the data stops. Turning a plane range into a table takes a single keyboard shortcut, and it's the biggest single upgrade you can make to your results. The third mistake is the vague prompt. Ask for the top products and you will get something, but ask for the top two products by total sales revenue and you get what you wanted. Name the column, name the measure and say what the output should look like. Copilot isn't a mind reader, and it never claimed to be. The fourth mistake is the most dangerous, and it's simply trusting the answer. Read the formula before you use it. Check the total against the number you worked out yourself. Treat copilot like a keen new colleague who is fast, confident, and occasionally wrong because that's exactly what it is. Then there are the smaller traps that catch people again and again. Column headers that are unclear or duplicated will confuse it. So rename something like amount to total sales amount. Asking for five things in a single prompt will leave you with a muddle. So ask for one thing at a time and forgetting that a filter is switched on or that some rows are hidden, will make the totals look wrong when they're perfectly correct. There are also two set up gotchas that look like bugs, but are really just settings. Copilot editing only works when the calculation option is set to automatic, and your file needs to be in a modern format saved to OneDrive or SharePoint. If the button seems dead, check those two things before you blame the robot. Put it all together and the rule is short clean data in clear question in one task at a time, and check the work on the way out. Do those four things and copilot goes from a party trick to something you rely on. The honest limits you need to know. I promised honesty, so here is the uncomfortable part. Microsoft's own guidance says that copilot can misread information or produce inaccurate results, and it advises against relying on it for decisions in finance, legal or medical areas. That warning comes from the company that sells it, which tells you how seriously to take it. This isn't caution for the sake of caution. In January of twenty twenty six, a financial modeling firm gave Excel's agent mode and a rival AI assistant the same demanding job, which was to build a three statement financial model with a valuation attached. Agent mode handled the discounting correctly, but its depreciation schedule was inconsistent, sometimes calculated from revenue and sometimes from the asset balance. The rival made its own mistakes, and the conclusion was blunt. Both tools sped up the early work, and neither was safe to run unsupervised. There's a second limit that matters to anyone who produces reports. Copilot generates a fresh answer each time you ask, so the same prompt on the same data can come back slightly different. A report your manager depends on should give the same number every month, which means the final version still belongs in a properly built process with checks in it, not in a prompt you're hoping behaves. A third thing to know is what happens when you save copilot's changes become part of the file so everyone with access sees them, including anyone editing alongside you at that moment. Undo and version history are your way back, and it's worth using both before you share anything important. And a fourth, which is really a boundary instead of a fault, copilot works inside one workbook plus the files you pointed at. It doesn't replace a proper import from your accounting system or your data warehouse with the checks that catch missing and duplicate records. It's a drafting tool and a very good one, but it isn't your finance department. None of that makes it useless, and I want to be clear about that. It means you should know where the edge is before you lean on it. Now you do. How it fits into Microsoft three hundred sixty five and Azure and what to do next. One last piece and then you're done. Copilot in Excel is one surface of a much bigger assistant, the same one that also runs in teams, word, PowerPoint, and outlook. It's managed through the same copilot settings as all of those, not through some separate switch hidden in the admin center, which is why your IT team already has a view of it above it. There's a whole ladder and you can climb it later. Copilot studio lets people build their own assistants for specific jobs. Copilot shows up across power BI and Microsoft Fabric for the people who live and breathe data. And underneath all of it sits Azure, where Microsoft hosts and serves the models that make the whole thing work. You don't need to understand any of that to use Copilot in Excel, but it helps to know the tool you're learning is part of something much larger. Let me keep the recap short. Copilot in Excel takes a plain language request and drafts the spreadsheet work for you. You may already have it. It needs clean data and clear questions. It can be wrong and it should always be checked. That is the whole picture, and it's a much friendlier one than the jargon makes it sound. Before you go, I have one question for you. Which spreadsheet chore would you hand over first? The monthly totals, the clean up or the chart you keep putting off? Whatever you picked. That's your first prompt and you can try it today. If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.