The concept of an interactive chart is aim at making Excel dashboard more flexible for users to choose whatever they wanted to see by changing the selection in the drop down menus to drive automatic chart updates in their report. I will show the simplest way to build an interactive chart once.
This will allow user to create their own view without you having to necessarily create multiple permutations of the same report or customized it for each user. Take a look at the demo below to see how we can figure out the mechanism behind it.
Though there a lot of ways to accomplish this task but I will prefer to use combo chart in this post. More so, the charts for this demonstration will be created from below data set.
You can watch the video here
or continue with the following guidelines
Steps to create an interactive charts
- I Insert PivotTables from the data set above to build six different charts with uniform size. I set those charts size to 7” high and 4.4” wide. You can choose any dimension of your choice but must maintain size uniformity.
The charts include:
The Sales Per Product
Sales per Region
Each Sales by Representative
Transactions based on Payment Method
Unit Sold Per Product
Sales Per manufacturer
- List out the charts title and rename the cell range. For me, I listed them in cell range A2:A7 and renamed the cell to be ListOfCharts
- Resize those cells selected to hold the separate charts earlier built. In this case I adjust cells B10 to B15 to 277 heights and its widths to 60.
- Position each chart to fit into respected location within the workbook. Let’s assume your charts appear like what I have here
- Select the corresponding cell that hold the first chart which happened to be cell B10 in this case, then click the name box or use define name to rename it to any name of your choice. Here I will call it chart1. Then repeat the process for the next five charts. I have chart1, chart2, chart3, chart4, chart5, and chart6 in that order.
- Insert new sheet to this workbook and rename it “Dashbord”. Then go to developer menu and insert combo box form control and draw it on the sheet.
- Right click on the combo box and select format control from the popup option to invoke format control dialog box. Specify the input range as ListOfCharts we earlier discussed in step 2 and Cell link as any empty cell in Dashbord sheet such as cell H1. Renamed cell H1 to be cell_link
Now you can select any of the charts name on the list using combo box and the link cell will show the corresponding number as it appears below
- The next thing to do is to device a way of pulling the corresponding chart based on your selection. This can be accomplished with CHOOSE function in conjunction with define name. Let’s called the new name “Chart_Picks” in the define name of formulas menu and input the following formula in the refer field
=CHOOSE(cell_link, chart1, chart2, chart3, chart4, chart5, chart6)
- Go back to Chart sheet and select chart1 range then copy it by either press CTRL+C or click on copy in the clipboard group of Home menu.
- Move back to Dashboard sheet and paste it as linked picture (Right click > Paste Special > Linked Picture) to insert the linked picture of chart1
- Select the linked picture inserted and go to formula, then type =Chart_Picks and hit Enter key. Turn off the gridlines and reposition the items to have a nice look. Congratulations
Hope this post is helpful?
For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon. You can as well subscribe to our newsletter here