Copilot in Excel in Excel seamlessly manages formulas, streamlines repetitive tasks, and delivers fast insights right inside your spreadsheets (or is designed to at some point at least, lol!). However, there may be times when you need to take that Excel data beyond the workbook and share it with colleagues or customers, without requiring them to dive into a spreadsheet themselves.
That’s where combining your Excel data with a chatbot via Copilot Studio comes in handy. In this blog post we’ll look at creating a simple chatbot using data from an Excel workbook in Copilot Studio and explore the pros and cons of this feature. You can follow along using the exercise file provided below:
This workbook includes three tables featuring customer and order information. In this exercise, we’ll build a simple chatbot to assist with tasks like customer service, reporting, and logistics-related questions. (If you’re starting to feel uneasy about using Excel this way—treating it like a database for critical business data and processes—you’re not wrong! We’ll dive into that later. Still, for those of us living in the real world, there are practical scenarios where turning Excel into a chatbot makes sense.)
To begin, navigate to copilotstudio.microsoft.com and set up a new agent from scratch. Feel free to bypass the starter configuration screen and jump directly to Create.
First, let’s give the chatbot a name. Click Edit under Details and name it something like Excel Chat.
Next, enable the button found beneath the Orchestration settings. This activates the agent’s ability to utilize generative AI, dynamically evaluating and creating customized responses to users and events by considering context and intent. With this functionality, the agent can move beyond fixed, pre-set replies, picking up on subtle language cues and adjusting its tone and content on the fly, similar to how a human would converse.
Finally, navigate to the Knowledge section and disable the option that permits the AI to draw upon its own general knowledge:
If you’re still unsure about what each of these features does or doesn’t do in relation to generative AI and how they apply to different use cases, take a look at this blog post:
The next step is to incorporate the workbook into this agent’s knowledge base as a data source. To do so, click on “Add Knowledge,” then use the file upload option located at the bottom to upload the file to the agent:
As far as I’m aware, there’s no way to update the data here without re-uploading the file, which isn’t ideal—ideally, that will improve in the future.
This limitation is partly why other options exist that allow you to sync “live” with data sources like websites and SharePoint directories. Another alternative is Dataverse, which is the closest equivalent to Excel workbooks since it handles structured data. Dataverse enables you to store data that stays connected and feeds directly into the chatbot, though it comes with the trade-off of moving away from Excel and requiring more technical know-how. If you’re an Excel user curious to learn more about Dataverse and what you should understand, check out this blog post:
Great! With the basic rules and knowledge sources set up here (there’s a LOT more we could customize) let’s go ahead and test our chatbot over in the righthand popup!
Since we’ve only got one workbook as a knowledge source and shut off access to other knowledge sources, we can afford to be pretty vague with what workbook we mean in our prompts. If we were dealing with a more complex agent we’d need to be very specific about where to find the file. But here we can just freeform ask questions about the workbook, such as how many worksheets are in it?
It might take a few seconds to process, especially initially, but you’ll soon see a response detailing the three worksheets, including their names.
You’ll also notice the Activity Map. In Copilot Studio, the Activity Map offers a visual breakdown of an agent’s actions, decisions, and conversation flow throughout a session. It’s a handy tool for spotting issues and fine-tuning performance, as it tracks the sequence of inputs, outputs, and any errors, shedding light on how the agent handles queries and completes tasks.
This could be quite useful if you’re curious about the workbook’s structure and design (and it’s worth noting this capability exists if you’d rather keep it under wraps, haha!). But let’s shift gears to more practical, hands-on scenarios.
Picture the possibilities this unlocks for customer service applications, for example. A representative could simply ask questions in natural language and get instant answers. Pretty game-changing!
When I bring up this example or similar workshops, I often get the same FAQ: Should we even be using a tool like this for data retrieval and analysis when Copilot can get things wrong?
Sure it can. Nobody’s denying that. But humans mess up sometimes too, and we don’t ditch all their work because of it. It’s all about weighing risk versus reward. If the query’s fairly straightforward and a mistake won’t spell disaster, I say go for it. Double-checking is always smart in any system, but let’s be real. Sometimes it’s just too time-intensive. And yes, hallucinations are a known quirk of generative AI. My take is we adapt and roll with it, not scrap the whole thing over a few hiccups.
Now, onto some customer service questions we could toss at our agent:
- “Has Maria Lopez received her order yet?”
- “List all orders currently tagged as ‘Processing’ or ‘Cancelled’.”
Our agent can handle some basic, high-level sales and product insights too. For instance, if you want a quick snapshot of sales by shipping status, it’s got you covered:
For more complex data analysis tasks like weaving in conditional logic, crafting visualizations, or applying conditional formatting Copilot in Excel is likely the better bet. But if your aim is to get quick, at-a-glance insights right at your fingertips, especially for a less technical crowd, this setup works just fine. Along those lines, here are some other questions you might throw at your agent:
- “What’s the average Total Amount per order?”
- “How many Bamboo Pen Sets have we sold?”
A couple of caveats to keep in mind about limitations. First, yes, hallucinations strike again. You might’ve noticed this workbook is split into three distinct tables. Your agent’s probably going to stumble when you ask something that requires pulling and merging data across multiple sources.
In situations like this, anyone with a basic gut feel for the data will sense something’s off: this is showing ALL products sold in the dataset, not just those tied to this one customer. (You won’t always get this obvious a tip-off with hallucinations, of course!) What I’ve noticed is that if you spell out the exact join keys in your prompt, it’s usually enough to snap your agent out of it and pull the correct data.
One more thing to note: this workbook is a read-only knowledge source, so users can’t directly input values or add records (though that kind of functionality could be a game-changer for plenty of business operations scenarios). To make that happen, you’d need to set up an action in Copilot Studio to update the source data. This would best be done via the Dataverse as this structured data. For a crash course on the basics of Actions in Copilot Studio, check out this post:
In summary, turning your Excel workbook into a chatbot or virtual agent in Copilot Studio is a mixed, possibly incomplete bag. It’s definitely good for natural language querying on basic high-level questions that either Copilot in Excel struggles with or for users who don’t feel too confident in Excel.
That said, the inability to update and keep the data fresh is a huge downside, and like Copilot in Excel, it’s got a catch-22. If you’re smart enough to specify join keys in your prompt, you may as well be looking up the data in the workbook yourself!
That said, I do see some interesting paths forward for including Copilot Studio in the Excel user’s stack, like using natural language queries to engineer more complex workflows that use Excel in some part of the flow and build more like app- and data product-like solutions in an AI-driven environment. If you’ve ever felt the pain of working with a several disjointed systems to do your work and keep it synced in Excel, this might spell a promising path forward.
What questions do you have about virtual agents from Excel workbooks in Copilot Studio or Copilot Studio more generally? Let me know in the comments.
Leave a Reply