How do make Dynamic Excel Charts for your Dashboard

A dynamic chart is one that changes according to various filtering options. Most dashboards consist of a number of dynamic charts, which add depth to the range of data without over-cluttering the display. While many people might think that it is fairly complicated or difficult to set up a chart of this type in Excel, there are actually numerous simple approaches that can create effective and interesting displays.

One of the simpler methods is to make use of check-boxes. These can be set up so that selecting a particular check-box shows a particular data set, while selecting additional boxes will simultaneously display other data sets on the same chart as well. In order to set up a chart of this type you will need more than one data groupings, all related by a common variable. From there you will need to arrange them on a table, with corresponding data points. That is to say if you table has a Time column, for each time data point you will need a corresponding data point for each of your remaining variables.

If you have two data series to plot, you will need to set up two check-boxes. This is done by accessing the Developer tab, and then choosing check-boxes from the Insert tab. You must then set up your checkboxes so that they will display the correct information when required. Attach one check-box to a cell belonging to one of your data sets. For example if the cell is B25, type $B$25 into the ‘cell link’ box. Then you must create a new table that is set up to use the IF condition, using your original data but incorporating IF formulas. From there it is a simple matter to make a chart using this data.


Comments are closed.