Categories
Planning

How to automatically generate charts in google sheets

How to automatically generate charts in google sheets

Google Sheets lets you generate charts automatically to create a visually appealing way to convey information. Using charts is a great way to help people retain data a lot easier than looking at a bunch of numbers.

Google Sheets gives you a wide variety of free graphs from which to choose. Whether you want to use a pie chart or something a little more complicated like a radar chart, you won’t be disappointed with the options available.

The first thing you need to do is fire up your Google Sheets homepage and open a new or existing spreadsheet.

How to automatically generate charts in google sheets

If you just opened a new spreadsheet, now would be a good time to insert some data into the cells. After that, click “Insert” and then “Chart.”

How to automatically generate charts in google sheets

From the drop-down list, click the type of chart you want to add. In this example, we are going to use a smooth line chart, but you can choose from 30 different types of charts.

How to automatically generate charts in google sheets

Now, you have to choose which cells you want to appear on the table. You can manually enter the range, or click and drag your pointer to select the desired data range. Click the grid icon to enable manual selection.

How to automatically generate charts in google sheets

After you’ve entered or selected the range of data, click “OK.”

How to automatically generate charts in google sheets

After, your chart populates with all the data included from the range of rows you selected.

How to automatically generate charts in google sheets

Note: Because of the way Google handles charts and tables across platforms, make sure all parts of the chart are visible if you plan on inserting them into a Google Docs or Slides project. Google saves each chart as an image, then inserts it into your document. If you try to resize it from Docs, your chart could end up looking blurred or deformed.

Any adjustments you make to your data are automatically updated and change the way your chart appears, without having to re-insert anything or refresh the page.

How to automatically generate charts in google sheets

Google Sheets also lets you change the type of chart without having to re-insert anything either. Just double click the chart to bring up the Chart Editor menu and then click “Setup.”

How to automatically generate charts in google sheets

From the “Chart Type” dropdown menu, select a different style and all your data is transformed into a new fully customizable chart style.

How to automatically generate charts in google sheets

When you click the “Customize” tab at the top of the chart editor, a list of fully customizable options reveals itself. Clicking any of the options lets you delve in and change colors, font, legends, and so much more.

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.

The basic dataset

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

How to automatically generate 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:

How to automatically generate charts in google sheets

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:

How to automatically generate charts in google sheets

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:

How to automatically generate charts in google sheets

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:

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

Create dynamic charts in Google Sheets

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

How to automatically generate charts in google sheets

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

How to automatically generate charts in google sheets

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:

How to automatically generate charts in google sheets

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:

How to automatically generate 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.

46 thoughts on “Creating Dynamic Charts In Google Sheets With Drop Down Menus”

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!

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

Hi Ben, I can’t believe I solved this! Moving onto some harder ones now. Stay Tuned!