Hello all. Fancy dynamic graphs that changes based on selected inputs part of drop down list? Like the below:
Let’s look at the steps in creating this dynamic graphs. We would be performing the following steps to achieve this:
- Convert the data set to a table
- Create a simple graph based on the table
- Create a drop down and perform a Vlookup to on the drop down value
- Offset function to get the output dynamically based on the input selected. More on Offset function here.
- Change the data series, in the created graph, based on Vlookup and Offset values
1. To change the regular dataset into table: Select all the data -> Ctrl+T -> Select the dataset again -> Ok
2. Next select First data set (first row of data) -> Insert -> Clustered Column
Don’t worry about the axis / legend etc for now.
3. Next let’s create the drop down for our input. The input, in our example, would be Month. Either you can do a “data validation” and create a list with “month” column. If you think, the list will grow over time, create a name manager under formula tab. Name manager can be used as a substitute for a formula and can be more dynamic in nature. Let’s see how to do. More on Name manager / name range here.
Under the “Formulas” tab, click “Name Manager” -> New. Give a name, Scope would be “workbook” and under refers to, provide the range of the data you want to call (in our example, A2:A13). All steps below:
Next, to create a drop down list, under “Data” tab, select “Data Validation” -> Select “List” -> Source would be “Mylist” (name created under the name manager above).
This will provide the list that is dynamic. For example, if you need to add more values in the month column, the drop down will automatically add those values.
Next, let’s do a vlookup function, for the input section ie., Month. Simple to return “Attribute” in our main table
Here, the “Table2” is the same dataset (remember we converted the dataset into table!)
4. Next is the more complex section where we would use the “Offset” function to return values based on length of row / columns that we would like to offset with. The syntax would be
=Offset(reference, rows, columns, [height], [Width])
In our case, the reference would be the first value in the table (i.e., A2). Row should return the value that is being selected in the drop down. So we would use a simple match function for the rows. Columns should be the number of places to the right from where we need the data from – in our case 2 spaces from A2. [height] & [width] are optional.
Let’s do the match function. Matching the drop down value i.e, A1 in sheet2 with the values in A column of sheet1 would give the row number. Because we have a column heading, we would give a decrement value.
So the final offset formula would be:
I have given the height and width as 1 and 4 as we need to return 1 row and 4 columns from the offset value (as we have 4 values in the graph – Swift, Zen, Alto, Ritz). More on Offset function here.
For ease of using this dynamically, let’s create a name manager and add this formula, with a name as “Mydata”.
5. We need to change the data series of the created graph to add the dynamic data based on the values we select in the drop down. This is two part – one to get the title changed. Second part – to get the actual data changed.
For the ease of explaining, I pasted the graph to sheet 2, where we have drop down and Vlookup created. First part is super easy. Right click on the graph, choose select data -> under the “Legend Entries (Series)” click on “Edit”.
Under “Series Name” -> Select the range of the drop down and the vlookup (In our example, it would be sheet 2 $A$1 and $B$1. Don’t change the “Series values” for now. See below:
As and when you change the month, from the drop down, you would notice that the chart title would change to the appropriate month. But the data still remains static. That’s our next step.
Let’s change the data based on the input now. Again, right click on the graph area -> choose select data -> Under Legend Entries -> Edit -> under “Series Values” let’s replace the existing value with “Sheet1!Mydata”.
Now, both the Series name and the series value in the graph becomes dynamic. Now we have achieved our task. Change the value in the drop down and the graph shows the values based on the input.
I understand this was quite complex and an awful lot of formulas and logic to use – however let me summarize everything quickly (trust me just one liner.)
- Convert the data set in to Table
- Ctrl+T by selecting all dataset
- Select the first row in the table and insert a graph
- Choose appropriate graph. Here we chose clustered column
- Create a drop down and corresponding Vlookup
- Create a name manager value for the list
- To have the dynamic content, create a new name manager value – this time with offset function
- Formula: =OFFSET(Sheet1!$A$2,MATCH(Sheet2!$A$1,Sheet1!$A:$A,0)-2,2,1,4)
- Change the data input of the created graph
- Series name would be the dropdown and the vlookup value
- Series value would be the name manager created with Offset function
Actually this becomes simple with the above 5 steps.
I hope you like this tutorial and find it useful. Please provide us with your valuable feedback to make this content better. Thanks for reading.