In this tutorial, we will show you exactly how to make a dynamic chart in a Google Sheet. Simply follow the steps below.
You can make a dynamic chart on your Google Sheet by using data validation and VLOOKUP. Here’s how:
Open Google Sheets and click on the Google Sheet with your data. Here, we use “Quarterly Sales (2021 - 2023).”
Make sure your data is organized in rows and columns. This is important for making an accurate chart that updates automatically when you change the data.
Click on an empty cell next to your data table. The cell will have a drop-down menu. In this example, we use cell F3, and the menu will have the choices for “Quarter.”
Click on “Data” at the top and select “Data Validation.” This will open the “Data validation rules” box on the right.
In the “Data validation rules” box, click on “Add rule.”
Go to “Criteria.” Click on the bar below and select “Dropdown (from a range).”
Click on the “Select data range” bar.
Next, highlight the data in the first column of your data table. Then, click “OK.”
Click “Done.” You’ll see a drop-down menu in your selected cell.
Here, we use our sample data to show you how to use VLOOKUP. We click on our drop-down menu and select “Jan - Mar.” Then, in cell G3, type “=VLOOKUP(F3,A3:D6,2,false)”, and press “Enter” to pull 2021 data.
The process is the same for the other cells. However, we use “=VLOOKUP(F3,A3:D6,3,false)” in cell H3 and “=VLOOKUP(F3,A3:D6,4,false)” in cell I3. “2, 3, 4” are the columns where the data is found. These formulas will fetch the data for the selected quarter.
Copy the headings of your data table and paste them above the cell with the dropdown. This helps you see how your table updates when you select an option from the drop-down menu. See our example below.
Highlight your new table with the user-selected data. Click on “Insert” at the top and select “Chart.”
In the “Chart editor” box, go to “Setup,” “Chart type,” and select from the drop-down menu. Here, we choose a Column chart.
In “Setup,” find and check the boxes that say, “Use row 2 as headers” and “Use column F as labels.”
Click the drop-down menu in F3 and select different quarters. Your chart should update automatically to show the sales for the selected quarter in 2021, 2022, and 2023.
We hope you now have a better understanding of how to make dynamic charts in Google Sheets. If you enjoyed this article, you might also enjoy our articles on how to insert GIFs in Google Sheets and how to create a Google Sheets burndown chart.
