How to make an interactive dashboard with Excel
Recently, a client ask me if it is possible to do the same thing as Tableau/Qlik with excel. My answer is "not exactly". I mean if excel can do exactly the same thing, no one would ever spend 2000USD for a tableau license.
However, I was wondering what can excel do? Therefore I did a test. It is not a comprehensive test but I guess most of the functions has been used. It is quite handy and useful for analysts who are familiar with excel. Actually it just take a few more steps from pivot table to create an "interactive" dashboard.
You can select the filter on the right hand side. The layout is flexible and you will be able to identify related/unrelated data like Qlik (of course Qlik would be more powerful to associate data from multiple tables). Here I only have one table so it is just a simple filter based on the pivot table.
Since I am using Chinese version of excel 2010 (quite old but these functions still work!), I recommend you to explore if there is anything new available on latest version of excel. A pivot chart is important because that's the way to link the chart to a pivot table instead of the original data.
After creating the chart, you will have to add the filter. And by adding the filter, you will be able to connect the filter to multiple charts based on the same set of original data. That's the foundation of "interactive" and therefore data changed when you changed the selection.
To me, it is not a very sophisticated tool for enterprise level and obviously excel cannot consume large set of data (multi-million rows for example). Plus, the data preparation takes even more time as you can imagine the data set must fit an excel format.
I am happy to see that excel still works to this level.
Further information
1. There are plenty of resources online. Go to youtube and you will be able to learn step by step
2. It is not Comparable to Tableau/Qlik. That make sense because otherwise there will be no PowerBI from Microsoft. But it is a good start for visualization after all.
3. Excel is a software that basically acquired by all levels of organisations, so it would be useful for people to at least know it works to certain extent.