Learn BI online

View Original

5 Reasons YOU SHOULDN'T USER EXCEL for Interactive Dashboards

If you’re looking to build an interactive dashboard using Excel…what on earth are you thinking?!

On YouTube, there are hundreds, if not thousands, of videos and tutorials on how to build awesome interactive dashboards with Excel and, every time I see one pop on my timeline, the BI analyst in me dies a little. Ok, I’m exaggerating.

Now don’t get me wrong. I love Excel. I use it all the time and think it is potentially the best piece of software ever written. But never have I used it for building interactive dashboards, something I have been doing since 2011.

So you might be saying, “well if you’ve never tried it how can you be so sure?” Well, there are so many reasons I could give but I’ll just give you my top 5. Starting with…

1. It’s The Wrong Tool

First off, it’s simply the wrong tool for building interactive dashboards. Dashboards are called dashboards for a reason. They get their name from traditional car dashboards whose job it is to help you monitor the performance of your car in real-time. Speed, RPM, amount of gas in the tank, among other things.

Business dashboards are there to help you monitor the performance of your business in real-time via KPIs. So essentially, the data needs to be constantly updated for you to see how things are evolving, spot trends etc.

And one other main purpose of a business dashboard is to bring different real-time or regularly updated data sources together in one place, to join, blend and analyse them together.

To do this in Excel is a real headache because it’s just not built to do this kind of thing.

Sure, you can visualise your data in Excel and you can build dashboards with it. But it was never built as a dashboarding tool. It’s spreadsheet software. Extremely powerful, best-in-class, spreadsheet software. But still spreadsheet software.

If you’d asked me what the best tool for building interactive dashboards was fifteen or twenty years ago, I might have said Excel. But we’ve come a long way since then.

We now have purpose-built BI tools, like Power BI, Tableau and Looker (formerly Data) Studio, that have been specifically created to help you build and share fully interactive and fully-shareable dashboards.

And all of these BI tools can work with Excel or at least CSV data as well as hundreds of other data sources.

Honestly, building interactive dashboards in Excel is, to use the old adage, like trying to crack a nut with a sledgehammer. It does the trick but the result is less than desirable.

2. No employer is looking for excel dashboarding skills

Talking of desirable things. As far as most potential employers are concerned, being able to build interactive dashboards in Excel isn’t one. Knowing how to use it to complete other tasks like cleaning, managing and manipulating data is but, if they’re looking to hire a BI or data analyst, they’ll be expecting you to know how to use the BI tools I just talked about. If your project portfolio contains just dashboards built using Excel, then you won’t be taken seriously. 

3. Separate pivot tables

Usually, when you’re building an interactive dashboard in Excel, you’re working with some level of raw data. This means that every data visualisation in a dashboard, the charts, graphs and tables is created using a pivot table. Which in and of itself is fine. Pretty much every single BI tool uses a pivot table model at its core as well. But the difference is that, in Excel, every single pivot table needs to remain in the same Excel file as the dashboard in separate worksheets.

This not only makes for a messy and complicated file but also increases the file size. 

4. Data included

Talking of increased file sizes, as I just explained, the data source itself used to feed the interactive dashboard needs to be included in the same file. And this data can contain thousands of rows.

Ideally, when you create a dashboard in Excel, what you’re really going to want to do is keep the data source as small as possible. What this means is that it’ll be aggregated down to a lower level of granularity than the raw data it comes from. This doesn’t sound like a problem in itself if it gives you the dashboard you need. But what happens if your reporting requirements evolve and you need more granularity? Then you’ll not only need to include more rows of data in the file - making it larger - but you’ll probably also need to re-configure all of your pivot tables.

When you’re working with a BI tool, this isn’t a problem. You can just connect to or load the full raw data set and not have to worry about starting from a pre-aggregated data source because the dashboard is only connected to the data source remotely and not included with it. 

5. Sharing is caring

All of this means that your beautiful interactive Excel dashboard isn’t very shareable. As I just explained, for a dashboard to be truly interactive, you need to have the data set included in the same file as the dashboard in a separate worksheet. Plus all of the pivot tables. To share the dashboard, you have to share the whole file which isn’t very practical.

And if you make any changes to the dashboard or data, you have to send out a whole new file.

Some of you might be shouting, “but you can embed it in a web page”. But this is so impractical, not to mention the data security implications. It involves saving the file to One Drive or Sharepoint and grabbing an embed code. But there are so many steps you need to follow, as well as owning a webpage to embed it in, that I simply wouldn’t go there. This video that explains this method of sharing is over 13 minutes long. Be my guest.

The whole point of a modern BI dashboard is that it’s easy to share with stakeholders usually via a simple URL. It doesn’t take up space on their drive and, if you make any updates to the dashboard, you can just publish a new version that everybody gets access to instantly without needing to receive a brand new file.

Conclusion

So what do you think? Am I being too harsh on Excel dashboards? Let me know your thoughts in the comments. You might mention cost as a factor, that you can’t afford a shiny BI tool to build your dashboard. Well, Looker (a.k.a. Data) Studio is free to use so you’ve got no excuse. In fact, if you’d like to start learning how to use Looker Studio, check out this 15 minutes getting started video or check out my full 5-hour course here.