Google Sheets Dynamic Drop Down

How to Create a Dynamic Chart in Google Sheets

Finding a crucial data or metric for a report amid a sea of Raw data can be challenging, especially if your data is dispersed across several Spreadsheets and Tabs. Planning is necessary for effective project management, and Dynamic Dashboards may make this task simpler.

To make data-driven decisions, you can follow insights and receive a bird’s eye view of the data with the aid of a dashboard. An overview of your tasks and the ability to monitor the advancement of your Projects and Campaigns are provided through a Dashboard.

To provide users of your spreadsheet discretion over what data is presented, you can add your own dynamic charts. Any Google Sheets graphic and data validation can be used to accomplish this.

This section will walk you through building a dynamic chart for the first time in Google Sheets. In order to construct the data source for our interactive chart, you’ll discover how to configure data validation and a VLOOKUP column.

Creating The Drop-Down Menu

Let’s make a list of options the user can select from to control the chart. The chart will only display the data for the driver the user selects from the list of names in this case.

  1. Put the data validation drop-down menu next to the raw data table because it is in its own cell.
  2. Click the desired cell with your mouse, then select the Data > Validation menu option.
See also  How to Attach or Insert a PDF File to a Google Sheets Cell

Google Sheets Dynamic Drop Down 1

  1. Verify that the cell is chosen as the Cell Range. Then, as indicated in the following image, choose the name range A3:A11 as the criteria:

Google Sheets Dynamic Drop Down 2

  1. Select “Save.” This will increase the right side of your input cell, G2, by one small gray triangle. When you click it, a user input menu for names appears:

TQU5dJItaHkn3eupGYgjJmpCGoogle Sheets Dynamic Drop Down 3

You are halfway there now that the Google Sheets drop-down menu has been configured.

Dynamically Retrieving Data by Means of VLOOKUP

To chart the data matching to the name we’ve chosen, you should attach your table of data to this Google Sheets drop-down option. When it discovers a match, the remarkable VLOOKUP function looks for values and returns information. This works perfectly with a drop-down menu.

Using the value in the Google Sheets drop-down menu as the search criteria, create a table using VLOOKUPs to import the data from the raw data table.

Fill in cells H2, I2 and J2 using the following VLOOKUP formulas, respectively:

=vlookup($G2,$A$3:$B$11,2,false)

=vlookup($G2,$A$3:$C$11,3,false)

=vlookup($G2,$A$3:$D$11,4,false)

Google Sheets Dynamic Drop Down 4

Create Dynamic Charts in Google Sheets

The next step is to make your Google Sheets Dashboard appealing and readable by customizing it. Your Dashboard is enjoyable to use since you can alter the font or add colors to charts and tables. A graph’s background can be colored, and titles can be displayed in several font families.

  1. Select the data, then select a chart from the Insert menu.
See also  3 Best Ways To Convert PDF to Text in Google Docs

Google Sheets Dynamic Drop Down 5

  1. Make sure to choose a column chart and to select Column G and Row 1 as headers and labels:

Google Sheets Dynamic Drop Down 6

  1. Select insert and check your chart. Here’s mine below:

Google Sheets Dynamic Drop Down 7

In my example, I made a dynamic chart to compare each store’s October and November sales. Every time you select an option from the drop-down menu we generated, you can view all the data.

Google Sheets Dynamic Drop Down

Before developing a dynamic dashboard in Google Sheets, you should be aware of a few things. The end result will boost the productivity and organization of your workforce. Google Sheets’ cells and algorithms make it simple to organize data. With the help of this data, you can create a dynamic dashboard in Google Sheets that looks polished.

If Data Integration is a challenge for your firm, be sure to look into FileDrop. With FileDrop, you may upload files directly from your Google Drive and they will be included with the name in the spreadsheet cell.

Get Our Latest Articles

Subscribe to our newsletter below to get amazing new Google Sheets articles, but also tips and tricks.

On the next pageEnter your Google Sheets domain email address

Your upgrade will not work if you don't enter the correct email address.

On the next pageEnter your Google Sheets email

Your upgrade will not work if you don't enter the correct Google email address.

Promo - Get 40% OFF with our yearly plan, expiring soon!
This is default text for notification bar