Creating dynamic charts in Google Sheets with data validation

I’ve had a few questions recently on how to add interactivity to charts in Google Sheets. 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 dynamic chart.

How-to video


This article walks through the steps to create a neat drop-down menu and interactive chart, 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.

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

Raw data table for dynamic chart

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:

data validation menu

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

You want to link your table of data to this 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 drop down menu as the search criteria.

Put these VLOOKUP formulas into cells F2 and G2 respectively:



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

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 data validation drop down menu:

dynamic chart

Awesome! Great job!

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

See this technique in action online in the following Google Sheets example (feel free to make your own copy).

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

One thought on “Creating dynamic charts in Google Sheets with data validation”

  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!

Leave a Reply

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