How to Create a Dynamic Chart in Google Sheets

Setting up a dynamic chart in Google Sheets is useful when you want to create interactive charts to your spreadsheet.

You can add your own dynamic charts to give users of your spreadsheet control over what data they want to be visualized. This can be achieved through data validation and any Google Sheets chart.

Let’s begin with a quick use case where we might need to make a dynamic chart.

You are a manager of an online store and would like to see which categories a bulk of your revenue comes from. Since you handle many products, there are about 15 categories in your revenue sheet. 

You want to create visualizations of multiple combinations of categories, but you don’t want to make separate charts every time. To solve this, you can make a dynamic chart that makes it easy to pick and choose which categories are part of your dataset.

Similarly, you might have a data set of daily revenue that you want to filter out. Using a dynamic chart setup, we can create a spreadsheet that lets users pick an arbitrary start date and end date of a visualization. 

Now that we have a basic idea of the benefits of a dynamic chart let’s explore a real example of it on an actual sample spreadsheet.



A Real Example of Using Dynamic Charts in Google Sheets

Let’s look at a real example of a dynamic chart that you can set up in a Google Sheets spreadsheet.

The example seen below shows a pie chart that displays the contribution of a select number of categories. Data validation gives you the option to display as many or as few categories as you want. 

Dynamic chart example with pie chart


To get the values in Column B, we used a VLOOKUP formula that uses the category selected in column A as a search key.

In the example below, we have the same data, but we’ve opted for a column chart instead. Like the pie chart seen earlier, each chart is dynamic and updates when categories are added or removed.

dynamic chart with bar graph


Alternatively, we can create dynamic charts using a different approach. In the chart below, we can select which categories appear by simply ticking a checkbox on the table.

dynamic chart with checkbox


The trick to this particular chart is that the source data range is a second table. This additional table filters out rows without a ticked checkbox using the FILTER function.

You can make a new copy of the spreadsheet above using the link attached below. 

If you’re ready to try creating your own dynamic chart, head over to the next section to follow a step-by-step guide.



How to Create a Dynamic Chart in Google Sheets

This section will guide you through your first time creating a dynamic chart in Google Sheets. You’ll learn how to set up data validation and a VLOOKUP column to create the data source for our interactive chart.

Let’s follow these simple steps to start building our dynamic chart:

  1. First, make sure that you have a lookup table prepared. Ideally, the first column of the table should be the label we’ll be using later for our data validation.
    In our current example, we’ll be using the Category values as our search key.
    Set up lookup table for our dynamic chart in google sheets
  2. Next, let’s add an empty chart to our spreadsheet. Afterward, we’ll create a data range for our dynamic chart. In this example, we’ll be populating column A with our chosen categories.Add empty chart
  3. We should add data validation to the cells in column A to ensure that users do not add invalid categories.
    add data validation to your data source
  4. The criteria for the data validation should be a list from a range. The range must be the lookup table we set up earlier. Make sure that the dropdown list option is checked.
    set up data validation
  5. Your cells in the first column should now look something like this.
    dropdown menu for our dynamic chart
  6. Add a VLOOKUP formula to the second column. This will allow the column to auto-populate once a category is chosen.
    VLOOKUP to get data from lookup table
  7. Drag down the formula to create more cells for users to select.
    add more rows with data validation and vlookup formula
  8. Select the empty chart object we added earlier. In the Chart editor on the right, find the data range option. We should select A:B as our data range for our chart.
    select data range of our chart
  9. After selecting the data range, you can now format the chart however you like. In the example below, we’ve created a pie chart from our data.
    final result of our Dynamic Chart in Google Sheets
  10. Test out the sheet to see if the chart is truly dynamic. We’ve removed a few entries in the example below, and the chart updated accordingly.
    Dynamic Chart in Google Sheets with less data points


This guide should be all you need to start using dynamic charts in your spreadsheets. This step-by-step tutorial shows how easy it is to create visualizations that anyone using your sheet can modify.

Dynamic charts are just one of many ways you can visualize data in Google Sheets. With so many other Google Sheets features out there, you can surely find one that suits your data.

Are you interested in learning more about what Google Sheets can do? You can stay updated on new guides like this by subscribing to our Google Sheets newsletter

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Leave a Reply

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

You May Also Like