Want to share your content on python-bloggers? click here.
Lately I’ve been digging into how Python in Excel’s AI-powered analysis might disrupt traditional static dashboards. It offers a flexible, lightweight way to explore data—letting you ask questions, adjust them as the data shifts, and uncover what’s worth focusing on. Traditional dashboards, with their fixed visuals, can feel stiff and limited by what’s pre-selected to show.
That said, dashboards still have value. They’re ideal for fast, clear insights—like tracking KPIs or giving execs and teams a simple, consistent view of key metrics. They work best when you need straightforward, repeatable monitoring without the complexity of constant tweaking.
The cool part? Python in Excel can still help you build dashboards. With a bit of creativity and effort, it’s a great tool for processing data and setting up visuals. In this post, I’ll show you how using the MPG dataset. Download copy below to follow along.
If you’re new to Copilot’s Advanced Analysis features, take a look at this post to learn how to get up and running:
Let’s dive right in and have Copilot whip up a dashboard for us. To keep things flexible, I’ll ask Copilot to create each plot individually rather than combining them into one fixed image. This way, we retain full control over how to piece the dashboard together once all the component plots are ready—otherwise, Copilot might mash everything into a single static output that’s difficult to tweak.
I’ll also provide Copilot with some basic context: a rundown of my data, my goals, my audience, and what I want this dashboard to achieve and look like. That said, I’ve learned the hard way that overloading Copilot with too many details can make it stumble or even crash, so I’ll keep it concise.
Create a dashboard in Python, adding each plot separately to the sheet.
The goal of the dashboard is to assist automotive engineers and data analysts at a car manufacturing company in exploring fuel efficiency trends and vehicle performance metrics. The dashboard is for internal use by these technical professionals who aim to optimize future car designs.
Incorporate a variety of univariate, bivariate, and multivariate visualizations to showcase different perspectives on the data, such as distributions, relationships between variables, and trends over multiple dimensions.
A quick heads-up: since this process is probabilistic, your results might look totally different from mine—maybe better, maybe worse. If you’re not thrilled with what you get, feel free to roll the dice again with Copilot. You can tweak things too; for instance, if a plot doesn’t hit the mark or you want a specific relationship highlighted, just ask for it.
For instance, in one of my attempts, Copilot recommended adding a scatterplot to display the relationship between weight and mileage, alongside a pairplot—which, if you’re familiar with pairplots, already includes that scatterplot as part of its grid!
So, I asked Copilot to rethink its choices, pick one of the two, and deliver a total of four visuals for the dashboard. (If you’re ever unsure how to kick off a dashboard layout, a simple 2×2 grid or a setup with two smaller visuals up top and a wider one below are solid, time-tested options!)
Honestly, this ability to have Copilot toss out a handful of random ideas to kick things off is a game-changer for dashboard creation. We’ve all faced that anxiety-inducing blank-page moment, where the uncertainty of what to even put down stalls us out completely. This approach blasts right through that.
Once you get what seems like a reasonable, coherent bunch of visualizations (you can always ask for more, tweak them, or create extras!), you can even get some simple help from Copilot on how to arrange the dashboard.
Could you suggest a layout and title for this dashboard?
And Copilot gives us a few insights:
This is one spot where maybe checking with another LLM model could give you richer, more in-depth help and insights—the Copilot in Excel chatbox is pretty limited. Plus, there’s always the human side, the knowledge and experience, that goes into making a really solid dashboard. At all these steps, you’ve got to keep in mind this is Copilot, not Autopilot!
Moving on, let’s ask Copilot for some big-picture ideas about making this dashboard interactive. With the huge caveat that Python in Excel plots are pretty much just static images, so there’s not a ton of interactivity we can squeeze out of them. But below, I’ll show you one trick we can pull off—adding some checkboxes to filter the dashboard.
Can you recommend effective user interaction features for this dashboard and suggest optimal placements for them within the layout?
Nice job pulling together a bunch of raw materials and ideas with Copilot’s help for this dashboard! Now it’s on you to put it all together in Excel.
What I’d suggest is copying and pasting the cells that created the images in the Analysis worksheet into a new worksheet. From there, you can switch these cells to show the Excel value as the image instead of the Python object type with the keyboard shortcut Ctrl + Alt + Shift + M.
Since these snap right into single cells, it makes it a lot easier to resize the plot into something that’s clear and readable:
It does quite a while for Python in Excel to recalculate, so I’d recommend tidying up any cells you don’t actually need recalculating twice. In particular, you can delete much of your Copilot-generated output after the import and any subsequent data setup steps:
Do some resizing, toss in a title, and you should end up with something like this—nice!
But why call it quits there? With a bit of the elbow grease shown in this blog post about making checkbox-driven Python in Excel visualizations, we could add some basic interactivity to this dashboard—for example, letting the user zoom in on specific origin(s).
This is actually going to take quite a few steps, and maybe I’ll break it down further in an upcoming blog post or webinar.
Basically, you’ll need to redo all the source datasets for the plots in your dashboard with one that’s dynamically filtered by the checkboxes you added to the dashboard. You’ll also need to tweak your grid a bit so you’ve got some cells free to drop the checkboxes into. For that, I’d suggest making floating/linked references of the cell plots—they’re more flexible but a little trickier to keep in line on the grid.
It takes an unfortunately long while to set this up, and even once you’ve set it up the recalculations can be so slow that you’re not totally sure if the load has finished yet! That’s why it’s a good idea to toss in a visual cue—something that makes it super obvious when the data shifts. A solid way to do that is tying color to origin in one of the vizzes, so it’s crystal clear whether the data has or hasn’t updated. My pick for that is the scatterplot.
In the end, though, you should wind up with something like this—pretty cool! It’d be even cooler if the images refreshed faster and you could interact with them directly via tooltips, cross-filtering and so forth, but one step at a time…
Conclusion
In conclusion, utilizing Python in Excel for dashboard creation presents promising possibilities. This method leverages generative AI and Copilot capabilities, allowing users to access Python’s robust data manipulation tools within Excel’s familiar interface. This approach democratizes dashboard creation, making it accessible to those not versed in complex platforms. However, there are limitations to note. Currently, the visualizations lack interactive features like tooltips and dynamic updates—elements that users expect from modern dashboard tools such as Tableau or Power BI, resulting in less engaging dashboards for real-time data exploration.
Dashboard design purists, who see their craft as an art perfected through iterative design and meticulous user experience refinement, might view this approach with skepticism. They could argue that relying on AI-assisted methods like Python in Excel circumvents the nuanced, human-centered design process essential for elevating a dashboard from functional to exceptional. This method might encourage a more formulaic approach that misses nuances like visual hierarchy or audience needs, potentially resulting in dashboards that are technically sound but lack the polish of a hand-crafted solution.
Yet, the integration of AI and Copilot in Excel represents a significant shift towards more pragmatic solutions. Not every dashboard needs to be a masterpiece; sometimes, a quick, functional prototype is all that’s required to test a hypothesis or share preliminary findings. Python in Excel empowers users who may lack the time, resources, or expertise to master specialized tools, offering a low-barrier entry point to data visualization. As the AI integration evolves, we can expect enhancements in interactivity and customization that might bridge the current gap between this approach and more traditional methods.
What questions do you have about building dashboards with Python, Copilot and Excel specifically or this power trio more generally? Let me know in the comments.
The post Python in Excel: How to create a dashboard with Copilot first appeared on Stringfest Analytics.
Want to share your content on python-bloggers? click here.