When we are presenting data in charts it always comes to mind how this chart looks for this particular region or for that particular period or for that particular division, etc. In short we try to perform WHAT IF analysis using the charts. But, it so happens that people feel it is always time consuming and they have to extra efforts to create those charts all over.
“Creating interactive charts or dashboards in excel is really easy.”
Trust me when I say above statement. There are multiple ways through which you can have interactive charts / dashboards in excel. Old method was using Form Controls in Excel 2007 and earlier legacy version. Starting Excel 2010 you have got slicers on Pivot Tables which can be used to create interactive / dynamic charts & dashboards. Once you have understood the approach you may think that why you had not used this approach earlier.
So let’s get started…
First step in creating any charts or dashboard to have an aggregated summary of values by various dimensions by which you want to create charts. This aggregated summary can be created in excel using various aggregation formulas / pivot tables. I will not explain those aggregation methods in this post. For this post I am assuming you already have an aggregated summary of monthly sales by four different regions as follows:
Now after few blank rows below above data in yours excel sheet you can start insert following two blank rows:
Now next create a pivot table using ONLY region column from first summary table above and put that region column in “Row Area” of Pivot table. Your pivot table should like this:
Once you have this pivot table created, click any cell inside this pivot table and then you should have two additional tab in your excel ribbon. Click Pivot Table Tools > Analyze > Insert Slicer button. You would see slicer window. Click Regions and ok. Immediately Slicers will be inserted as follows:Link first cell below “Region” in second table to first cell of pivot table. Then using VLOOKUP get the details in another column of that table. The formulas will look like this:
Now you have just created the chart and make some cosmetic arrangements and here you have final chart ready.
Have a look at below 5 minute video for more detailed explanation of how to create such dynamic and interactive charts using slicers.