How to make dynamic charts

In excel, one can create a GUI, where users can select values from the dropdown lists. Based on this selection the graphs below will be updated.

Such graphs & reports are very useful while making big dashboards in which the users requires different views of the report. For e.g. City wise report or Month wise report.

You can always make multiple graphs/reports for each values (for e.g. separate graphs for each location if you are making location wise report). However, doing this will take lot of time and efforts, especially when the list is too large.

In the example below, user can select values in cell “C1” and the graph below will get updated based on the selected value.

Steps to create dynamic chart

Step 1: Create base data

Create the base data  with values for all cuts (In city wise graph, enter data for all cities) in a separate sheet. (you can create this data in the same sheet also)

 

Step 2: Make graph

1.       Make the table for graph with dummy data

2.       Select the table and insert graph from Insert menu

Step 3: Insert dropdown list

Insert dropdown list in Cell “C3”. Refer http://excelhelpbyriteshpatel.blogspot.com/2011/02/how-to-add-drop-down-list-using-data.html

Step 4: Link Dropdown list with base data

Add vlookup formula in Graph Data table to link Cell “C3” (dropdown cell) with the base data. In our example the formula will be (note the change in the column number parameter)…

For Q1: =VLOOKUP($C$1,Data!$A:$E,2,0)

For Q2: =VLOOKUP($C$1,Data!$A:$E,3,0)

For Q3: =VLOOKUP($C$1,Data!$A:$E,4,0)

For Q4: =VLOOKUP($C$1,Data!$A:$E,5,0)