How To Create A Dynamic Chart With Multiple Drop Down Selection Lists

Dynamic Charts allow the user to control what they want to see simply by changing a value in a drop down list.

In this example the dataset displayed in the chart below can be changed by selecting different start & end dates in the 'To' & 'From' drop down boxes. This is perfect for use in dashboards or as a stand-alone chart.

In the example below I have used a simple bar chart but this can be easily changed to a line, pie or other chart type.

How to do it

Follow the simple steps below to create a workbook in the format that we need.

1. First prepare a workbook with two spreadsheets, one called ' Data ' & one called ' Chart '
2. In the ' Data ' sheet populate the data in columns A & B as below with the headers ' Date ' & ' Units ' in row 1. You can either manually enter this data or download the completed example spreadsheet on the link above and cut & paste it in. Our example has 209 rows of data.
3. The workbook should look like this:-







4. Now enter the text ' From : ' in cell E2 and enter ' To :' in cell E4. Then place a border around cells G2 & G4
5. After a little column width formatting the workbook should look like this:-







6. Change the format of cells G2 & G4 to 'short date'.
7. Next we will add drop down list boxes in cells G2 & G4.
• Select cell G2 and on the Excel main top menu click 'Data' then 'data Validation' and the window below will appear.







• Select 'List' from the 'Allow' drop down selection box.
• Click in the 'Source' box, and then click on the Data tab and highlight the date range in column A (not selecting the row header in cell A1).
• The Data Validation window will re-appear as below with the date range you selected now populated in the 'Source' box.







• Click OK.
• Copy the contents of cell G2 to cell G4 (using ctrl c & ctrl v).


8. Next on the main Excel menu bar, select 'Formulas' then 'name manager'. The following window will appear:-







9. Select New & the following window will appear:-







10. In the 'Name' box enter the name 'daterange' (no apostrophes) .


11. In the 'Refers to' box paste in the following formula:-



=INDEX(Data!$A$2:$A$209,MATCH(Chart!$G$2,Data!$A$2:$A$209,0)):INDEX(Data!$A$2:$A$209,MATCH(Chart!$G$4,Data!$A$2:$A$209,0))


and click OK.


12. Repeat steps 8 to 11 again but this type use the name 'valuerange' & the formula:-



=INDEX(Data!$B$2:$B$209,MATCH(Chart!$G$2,Data!$A$2:$A$209,0)):INDEX(Data!$B$2:$B$209,MATCH(Chart!$G$4,Data!$A$2:$A$209,0))


and click OK.


13. Close the name manager.


14. Now return to the Data tab on the spreadsheet, highlight the range A1:B209 & on the main Excel menu bar, select 'insert' & choose a 2D column or bar chart from the many choices of charts available. You could select what ever you like, but for the pueposes of this tutorial we will select that.


15. With the newly created chart selected, cut & paste it onto the 'Chart' Tab. You can play with the formatting of the chart later to get the type of disply you require.


16. Select the chart & right click it. Then click 'Select Data' & the following window will appear:-.







17. In the left hand 'legend Entries' pane click the Edit button & the following window will appear:-







18. Ensure that the series name is not blank, if it is enter "=Data!$B$1". Also replace whatever is in the Series values box with '=Data!valuerange' . Now click OK.


19. In the right hand 'Horizontal Axis labels' pane click the Edit button & the following window will appear:-







20. Change the contents of the Axis Label Range box to read "=Data!daterange" & click Ok.

All you need to do now is format your chart & axis labels etc to get it to look the way you want.

There is one more thing to consider though!

If your expecting to add more data to your data table over time, you may wish to convert it to a data table. By doing this, when new data is added, everything including the chart and the dropdown box contents updates automatically

To do this simply click anywhere in your data set on the data tab & press Ctrl & T & the following window will appear:-

Check the range is correct & Click OK and the data set is now a data table.

If you don't like the way the table has been formatted, simply go to the Excel main top menu & click 'Table Design' and select clear at the bottom.

That's it all done! congratulations.