I examined 3 methods to construct an Excel dashboard with AI, and the best methodology received

I’ve been constructing Excel dashboards for years, and my methodology has modified nearly as a lot as Excel itself. One day I’ll construct one with formulation, the subsequent I’ll use solely refined PivotTables, and more and more, I’ll ask AI to do the heavy lifting. But not too long ago, I’ve discovered myself getting right into a little bit of an AI immediate entice. Is it higher to present it extra data and course? Should I get right into a back-and-forth dialog? Or ought to I simply maintain issues easy?

When it involves Copilot, I believe I’ve lastly landed on the strategy that works greatest for me: give it the aim, then get out of the way in which.

I let Copilot analyze my knowledge first

Its findings began shaping the dashboard

I began with what appeared like essentially the most logical strategy: let Copilot determine what was fascinating earlier than constructing something. I gave Copilot in Excel a workbook containing 5,000 movie-viewing information and requested it to investigate the information, establish fascinating patterns, after which construct a dashboard round its findings.

It discovered lots to work with. I had watched 878 distinctive motion pictures throughout these 5,000 information, with 82.4% of the viewing information being repeat watches. Viewing quantity peaked in 2023, Netflix’s share of my viewing modified over time, and completion price had nearly no relationship to my scores.

Copilot in Excel then turned these findings right into a dashboard with six abstract playing cards, six slicers, and 4 charts. And to be truthful, it labored. It regarded good, it was interactive, and it was simple sufficient to comply with.

The drawback was that the dashboard had began to replicate what Copilot discovered most fascinating on this explicit dataset—which was precisely what I’d requested it to do. Repeat viewing, for instance, grew to become a serious a part of the dashboard, with a number of slicers and charts devoted to new-versus-repeat viewing and rewatching.

The repeat-viewing evaluation was fascinating. My drawback was what had been neglected to make room for it. Other metrics which may have been extra helpful to me had successfully been pushed apart as a result of Copilot determined repeat viewing was the story value investigating.

That’s after I began to query whether or not I wished my dashboard to be formed by no matter occurred to face out most strongly when Copilot first checked out my knowledge.

I requested Copilot’s chatbot to plan the dashboard

The consequence was extra complete, however extra sophisticated

For my second try, I took a unique route. Instead of asking Copilot in Excel to resolve what mattered, I requested the web-based Copilot chatbot to investigate the information and suggest a dashboard.

Its suggestions had been far more detailed. They lined viewing patterns, platforms, genres, scores, completion, seasonal traits, a number of slicers, heat maps, scorecards, and extra calculations. So I fed these suggestions into Copilot in Excel and requested it to construct the dashboard.

Once once more, it did what I requested. This dashboard was extra analytical than the primary, with eight charts, two tabular visualizations, 5 slicers, 5 abstract playing cards, and a current-filter insights space.

But it was an excessive amount of. To see all of that directly, even on my bigger second monitor, I needed to zoom out up to now that the textual content grew to become troublesome to learn. The dashboard not gave me the short snapshot I wished after I opened the workbook. There was merely an excessive amount of to soak up.

Then I thought of upkeep. I at all times take a look at an AI-made dashboard earlier than I belief it, and this one had numerous charts, calculations, filters, and different elements to verify. The extra elaborate the dashboard grew to become, the extra work there was to ensure the whole lot really labored as supposed.

I additionally realized that among the issues the dashboard was serving to me examine might have been answered extra shortly with a PivotTable or by sorting my supply desk.

A dashboard is meant to make working with knowledge simpler. I did not wish to create one other venture for myself.

I stripped the immediate again to the fundamentals

Less course gave Copilot extra freedom

For my third take a look at, I went in the other way. Unlike my first take a look at, I did not ask Copilot in Excel to investigate the information and construct the dashboard round no matter it discovered. I merely gave it the essential details about my 5,000 movie-viewing information and requested it to construct a helpful interactive dashboard.

That strategy additionally lined up with one thing I’d discovered after I not too long ago checked out OpenAI’s updated prompting guidance: give AI a transparent aim and let it work out the route somewhat than prescribing each step. Since Microsoft Copilot can use OpenAI models, I used to be curious to see whether or not that precept would apply right here.

The consequence was a lot easier, however it nonetheless gave me lots to work with. I had 5 abstract playing cards exhibiting the important thing numbers, 4 charts masking viewing quantity by yr, style, platform, and ranking distribution, and two slicers that linked into the whole lot.

Copilot additionally added a helpful abstract line on the prime, highlighting the height yr, most-watched style, and main platform. The supporting PivotTables had been neatly positioned on a separate worksheet, in order that they had been obtainable after I wanted them with out taking on the dashboard.

Best of all, it was compact sufficient to suit comfortably on my display screen. And as a result of it is not constructed round anybody discovering in my present knowledge, I should not must rethink the dashboard as my viewing historical past grows. It was additionally the quickest of the three to create.

I ended up with the dashboard I really wished

Less element turned out to be extra helpful

The first dashboard was formed round Copilot’s evaluation, and the second was formed round an excellent longer record of suggestions. The third merely requested Copilot to show my knowledge right into a helpful dashboard.

I might open the worksheet and instantly get a way of what was taking place. The 5 playing cards gave me the important thing numbers, the 4 charts confirmed the principle patterns, and the 2 slicers let me discover the information with out filling the display screen with controls.

If one thing went incorrect, there have been additionally fewer charts, calculations, and different elements to research. And as a result of the dashboard wasn’t constructed round one explicit discovery in my present knowledge, I can maintain including information with out essentially having to rethink the entire thing.

Ultimately, I realized that giving Copilot extra data does not essentially end in a extra helpful dashboard.

A transparent aim was all Copilot wanted

I not too long ago modified my thoughts about Copilot in Excel. I wasn’t satisfied it was value protecting, however the extra I take advantage of it, the extra I perceive the way it works, and it is really extra succesful than I first realized. Now, this experiment has given me one other helpful lesson: I do not essentially get higher outcomes by giving Copilot extra directions when I’m constructing an Excel dashboard.

In this case, the least prescriptive strategy produced the dashboard I really wished. Sometimes, giving Copilot a transparent aim, stepping again, and letting it work out the main points is all of the course it wants.