What Is Dynamic Chart In Google Sheets?
Dynamic chart in Google sheets, as the name suggests, is a method used to present data in a dynamic way using dynamic chart. It is not an unbuilt chart type but uses column chart to create a dynamic chart.
Remember, dynamic chart is not different like doughnut chart or pie chart. It uses a combination of column chart that can be altered with dynamic data in Google sheets. For example, consider the below table showing subject, exam 1 and exam 2 details in columns A, B and C, respectively.
Use this Dynamic Chart In Google Sheets Template to follow along with the examples in this article.
Download Excel Template
Now, let us learn how to create dynamic chart in Google sheets. To begin with, we need to create the dynamic data by selecting an empty cell and clicking on the Dropdown(from the range) option under the Data validation rules tab. Then, by selecting the Insert tab and click on Chart.
Likewise, we can create dynamic chart in Google sheets with dynamic data.
In this article, let us learn how to create dynamic chart in Google sheets with the help of the following examples.
Key Takeaways
- Dynamic chart in Google sheets helps users present the data in a quick and better way.
- It is not a default chart type like pie chart or 3D chart. Users need to convert the data into dynamic data using data validation option in Google sheets.
- For creating dynamic chart with multiple options. We need to click on the Dropdown (from a range) option under the Data validation rules tab.
- This saves users time as we don’t have to create multiple charts in Google sheets.
How To Create A Dynamic Chart In Google Sheets?
We can create a dynamic chart in Google sheets with the following steps. They are:
Step 1: To begin with, we need to insert the data in the spreadsheets. Next, we need to select an empty cell and copy the headers from the data.
Step 2: Next, we need to enter the VLOOKUP formula with arguments in absolute references.
Step 3: Then, to create dynamic data, simply click on the empty cell and then click on the Data tab. Then, select the Data validation option.
Step 4: Now, we will be able to see the Data validation rules tab, at the right end of the screen. Here, check the cell reference and the location in the Apply to range option. Then, select the Dropdown (from a range) and enter the cell range in the absolute references.
Step 5: Press on Done. Now, we will be able to see the data is converted into Dynamic data.
Step 6: Next, click on the Insert tab and select the Chart option. We will be able to see the data represented in the column chart.
Users can also see the change in the dynamic chart when the value in the drop-down is changed.
Likewise, we can create dynamic chart in Google sheets.
Examples
Now, let us learn how to create dynamic chart in Google sheets with the below examples.
Example #1 – Dynamic Chart Using Google Sheets Table
For example, consider the below table showing name, projects in 2010 and project in 2020 details in columns A, B and C, respectively.
Now, let us learn how to create dynamic chart in Google sheets.
The steps are:
Step 1: To begin with, we need to insert the data in the spreadsheets. In this example, the data is available in the cell range A1:C7. Next, we need to select an empty cell and copy the headers from the data. In this example, the data is available in the cell range E1:G2.
Step 2: Next, we need to enter the VLOOKUP formula with arguments in absolute references. So, enter the formula =VLOOKUP($E$2,$A$2:$C$7,2,false) in cell F2. Press Enter key.
Similarly, enter the VLOOKUP formula =VLOOKUP($E$2,$A$2:$C$7,3,false) in cell G2 and press the Enter key.
Step 3: Then, to create dynamic data, simply click on the empty cell E2 and then click on the Data tab. Then, select the Data validation option.
Step 4: Now, we will be able to see the Data validation rules tab, at the right end of the screen. Here, check the cell reference and the location in the Apply to range option. Then, select the Dropdown (from a range) and enter the cell range in the absolute references.
Step 5: Press on Done. Now, we will be able to see the data is converted into Dynamic data.
Step 6: Next, click on the Insert tab and select the Chart option.
We will be able to see the data represented in the column chart, as shown in the below image.
Now, we can also see the change in the dynamic chart when the value in the drop-down is changed.
For example, let us change the value to Kelvin. We will be able to see the chart is altered in Google sheets, as shown in the below image.
Likewise, we can create dynamic chart in Google sheets.
Example #2 – Dynamic Chart Using Named Range & Offset
For example, consider the below table showing quarter, company A and company B details in columns A, B and C, respectively.
Now, let us learn how to create dynamic chart in Google sheets.
The steps are:
Step 1: To begin with, we need to insert the data in the spreadsheets. In this example, the data is available in the cell range A1:C6. Next, we need to select an empty cell and copy the headers from the data. In this example, the data is available in the cell range E1:G2.
Step 2: Next, we need to enter the VLOOKUP formula with arguments in absolute references. So, enter the formula =VLOOKUP($E$2,$A$2:$C$6,2,false) in cell F2. Press Enter key.
Similarly, enter the VLOOKUP formula =VLOOKUP($E$2,$A$2:$C$6,3,false) in cell G2 and press the Enter key.
Step 3: Then, to create dynamic data, simply click on the empty cell E2 and then click on the Data tab. Then, select the Data validation option.
Step 4: Now, we will be able to see the Data validation rules tab, at the right end of the screen. Here, check the cell reference and the location in the Apply to range option. Then, select the Dropdown (from a range) and enter the cell range in the absolute references.
Step 5: Press on Done. Now, we will be able to see the data is converted into Dynamic data.
Step 6: Next, click on the Insert tab and select the Chart option.
We will be able to see the data represented in the column chart, as shown in the below image.
Now, we can also see the change in the dynamic chart when the value in the drop-down is changed.
For example, let us change the value to Q3. We will be able to see the chart is altered in Google sheets, as shown in the below image.
Likewise, we can create dynamic chart in Google sheets.
Example #3
For example, consider the below table showing name, previous income and current income details in columns A, B and C, respectively.
Now, let us learn how to create dynamic chart in Google sheets.
The steps are:
Step 1: To begin with, we need to insert the data in the spreadsheets. In this example, the data is available in the cell range A1:C7. Next, we need to select an empty cell and copy the headers from the data. In this example, the data is available in the cell range E1:G2.
Step 2: Next, we need to enter the VLOOKUP formula with arguments in absolute references. So, enter the formula =VLOOKUP($E$2,$A$2:$C$7,2,false) in cell F2. Press Enter key.
Similarly, enter the VLOOKUP formula =VLOOKUP($E$2,$A$2:$C$7,3,false) in cell G2 and press the Enter key.
Step 3: Then, to create dynamic data, simply click on the empty cell E2 and then click on the Data tab. Then, select the Data validation option.
Step 4: Now, we will be able to see the Data validation rules tab, at the right end of the screen. Here, check the cell reference and the location in the Apply to range option. Then, select the Dropdown (from a range) and enter the cell range in the absolute references.
Step 5: Press on Done. Now, we will be able to see the data is converted into Dynamic data.
Step 6: Next, click on the Insert tab and select the Chart option.
We will be able to see the data represented in the column chart, as shown in the below image.
Now, we can also see the change in the dynamic chart when the value in the drop-down is changed.
For example, let us change the value to Darwin. We will be able to see the chart is altered in Google sheets, as shown in the below image.
Likewise, we can create dynamic chart in Google sheets.
Example #4
For example, consider the below table showing name, projects in 2010 and project in 2020 details in columns A, B and C, respectively.
Now, let us learn how to create dynamic chart in Google sheets.
The steps are:
Step 1: To begin with, we need to insert the data in the spreadsheets. In this example, the data is available in the cell range A1:C7. Next, we need to select an empty cell and copy the headers from the data. In this example, the data is available in the cell range E1:G2.
Step 2: Next, we need to enter the VLOOKUP formula with arguments in absolute references. So, enter the formula =VLOOKUP($E$2,$A$2:$C$7,2,false) in cell F2. Press Enter key.
Similarly, enter the VLOOKUP formula =VLOOKUP($E$2,$A$2:$C$7,3,false) in cell G2 and press the Enter key.
Step 3: Then, to create dynamic data, simply click on the empty cell E2 and then click on the Data tab. Then, select the Data validation option.
Step 4: Now, we will be able to see the Data validation rules tab, at the right end of the screen. Here, check the cell reference and the location in the Apply to range option. Then, select the Dropdown (from a range) and enter the cell range in the absolute references.
Step 5: Press on Done. Now, we will be able to see the data is converted into Dynamic data.
Step 6: Next, click on the Insert tab and select the Chart option.
We will be able to see the data represented in the column chart, as shown in the below image.
Now, we can also see the change in the dynamic chart when the value in the drop-down is changed.
For example, let us change the value to E. We will be able to see the chart is altered in Google sheets, as shown in the below image.
Likewise, we can create dynamic chart in Google sheets.
Important Things To Note
- Dynamic chart in Google sheets require the data to be converted into dynamic data.
- We can convert data into dynamic data by selecting Data tab, fill on the Data validation rules tab and click on the Done option.
- Similarly, we can also click on the Remove all option to delete the dynamic data.
Frequently Asked Questions (FAQs)
For example, consider the below table showing region, data 1 and data 2 details in columns A, B and C, respectively.
Now, let us learn how to create dynamic chart in Google sheets.
To begin with, we need to create the dynamic data by selecting an empty cell and clicking on the Dropdown(from the range) option under the Data validation rules tab. Then, by selecting the Insert tab and click on Chart.
We will be able to see the dynamic chart in Google sheets, as shown in the below image.
Likewise, we can create dynamic chart in Google sheets with dynamic data.
We can create dynamic chart in Google sheets, as the name suggests with the dynamic data only. Since this chart is created with the column chart framework, all we need to do is convert the normal data into dynamic data.
The steps to remove or delete the dynamic data are:
Step 1: First, click on the cell with the dynamic data and click on the Edit icon. Alternatively, we can also click on the Data tab and select Data Validation. The Data validation rules tab appears.
Step 2: In the Data validation rules tab, click on the Remove all option to delete all the rules or the delete icon to delete the specific data validation.
Likewise, we can remove or delete the data validation rule in Google sheets.
Use this Dynamic Chart In Google Sheets Template to follow along with the examples in this article.
Download Excel Template