Creating dynamic charts in Google Sheets with drop down menus

In this post I’ll show you how to create dynamic charts in Google Sheets, with drop down menus (data validation).

I get lots of questions on how to add interactivity to charts in Google Sheets. It’s a great question that’s worthy of a detailed explanation.

Dynamic charts can really enhance reports and dashboards, allowing for more information to be conveyed in the same amount of screen space. This article will show you how to use the data validation method to make a Google Sheets drop down menu to control a dynamic chart.

Step-by-step guide to creating dynamic charts in Google Sheets

This article walks through the steps to create dynamic charts in Google Sheets, with drop down menus so the user can select a parameter, e.g. a sales channel or name, and see the chart update.

It’s a relatively simple technique but surprisingly powerful.

Grab the data and solution file for this tutorial:
Click here to get your own copy >>

The basic dataset

In this example I’ve created a small table showing annual mileages driven by various users:

Raw data table for dynamic charts in Google Sheets

Creating the drop-down menu

Let’s create a list of choices to present to the user that will control the chart. Here, the user will choose a driver from the list of names and the chart will then only show that driver’s data.

The data validation drop-down menu exists in its own cell, so put it next to the raw data table. I’ve chosen cell E2.

So click cell E2 with your cursor, then head to Data > Validation menu option:

data validation menu

Make sure you have E2 selected as the Cell Range. Then select the range of names A3:A7 as the Criteria, as shown in this image:

data validation selection menu

You can leave the other settings as they are.

Click save. This will add a small grey triangle to the right side of input your cell, E2. Click on it and you’ll see a user input menu for names:

Google Sheets drop down menu

Now you have the Google Sheets drop down menu set up, you’re half way there.

Using VLOOKUP to dynamically retrieve data

You want to link your table of data to this Google Sheets drop down menu, so you can chart the data corresponding to the name we’ve selected.

Create a table using VLOOKUPs to pull in the data from the raw data table, using the value in the Google Sheets drop down menu as the search criteria.

Put these VLOOKUP formulas into cells F2 and G2 respectively:

=vlookup($E2,$A$3:$C$7,2,false)

=vlookup($E2,$A$3:$C$7,3,false)

Add headings to this interactive table: 2013 in F1 and 2014 in G1.

Grab the data and solution file for this tutorial:
Click here to get your own copy >>

Create the dynamic chart

Finally, create a chart from this small dynamic table of data. Highlight the data, then click Insert > Chart menu:

Insert chart menu

Select a column chart and ensure that Column E and row 1 are marked as headers and labels:

chart menu choices

Click insert.

Test your chart.

It should now be dynamic so that it changes whenever you select a new name from the Google Sheets drop-down menu:

dynamic charts in Google Sheets

Great job!

You’ve now created your first of many dynamic charts in Google Sheets!

Now go forth and make beautiful, dynamic dashboards.

Another view:

Here’s another example showing the steps of this technique side-by-side:

dynamic charts in Google Sheets

Further Reading

This is one of 10 techniques that can be used to build dashboards in Google Sheets – check out the other nine here.

Create an in-cell dropdown list – documentation from Google

21 thoughts on “Creating dynamic charts in Google Sheets with drop down menus”

  1. Hi Ben,

    Your articles are great, thank you for all the google sheets help. I’m trying to create my own dynamic dashboard, but am having a lot of trouble because of the complexity of my data. My data is set up like this:
    Team, Recruiter, Week, Action ,Target, Actual

    How can I create a dashboard to sort by team, and show charts that show the data by week?

    Any help you can provide would be much appreciated!

  2. Hey Ben, thanks for the great tutorials, I’m actually trying to setup functionality for a number of dynamic chart, but thought i would start with the easiest first

    I would like to setup a drop down list (using data validation) to toggle between different sparkline charts in the same column.

    Can this be done?
    Cheers

  3. Ben – Great stuff here. Let’s say you wanted this to be a stacked bar chart so the default would show “all drivers”. How would you add a filter so that the stacked bar chart would show all drivers when “all” was selected, but just a specific driver when that specific driver was selected?

    Do you have an example of how this would work somewhere?

        1. Hey David,

          I was too quick with my reply! I understand what you mean now. I’ve added an extra sheet to my doc with an example, using IF formulas to handle the logic. I think this achieves the output you were after. Unfortunately the legend shows up with all the names, not just the one selected.

          Cheers,
          Ben

  4. After creating the drop down menu, and moving onto the next steps, how can the rest be done, if you are taking information from a chart on sheet one and having the charts pop up/drop down in sheet 2?

    1. Hey Michelle,

      You should just be able to link to the drop cell in sheet 2 by referencing that cell, same as if it was a number in a formula, e.g. say your drop down was in cell A1 then use: Sheet2!A1

      Ben

  5. Hello,

    Two questions:

    1) Is it possible to set this up where you have lots of charts/visuals that all filter via one that drop-down menu? Meaning, if I choose Sam, can I see a pie chart with some data for Sam and a bar graph with different data and a table with still more data for Sam?

    2) Do I have to stick with a drop-down menu? Could I use a fill-in box and have people enter a name? I’d rather not show all the names in the database and instead just allow people to look up themselves.

    Thanks!

    Jill

    1. Hey Jill,

      1) Yes, you can just use the same drop-down cell reference in all your various lookup tables (which drive your charts).

      2) No, you could remove the drop-down and have them type a name into that cell, which your lookup formulas look to. Bear in mind though, that the name they type would have to match exactly with the name in the data, same spelling, surname or not etc., so it might be a frustrating experience for users unless it’s super clear what format to input their name.

      Cheers,
      Ben

  6. Is it possible to set the default drop-down value, in my case the date, to the last row that has data besides the date?

    1. If I follow your question, then I think what you’d have to do is create a separate list of the dates that have values, using say the filter function. Then highlight this new list in your data validation.

      Cheers,
      Ben

  7. Hi Ben,

    Thanks for the tutorial.

    How can I add drop-down menus to the chart itself? I want to publish it and embed it on my site so users can select values from the drop-down menu without seeing the sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *