Is there any formula to get a dynamic range in Charts in Google Sheets? Nope! Then how to get my chart adjusted to my ever-changing data range automatically?
Added new rows or delete existing rows, your chart can grow and shrink without leaving any white space on the chart! Yes. It’s possible in Google Sheets without any extra effort.
It’s one of the features of Google Sheets charts. You just need to know how to make the use of it.
Most of the charts in Google Sheets support this. But I only tested this feature with Column Chart, Pie Chart, and Geo Chart.
What is Dynamic Range in Charts?
When we plot a chart in Google Sheets, we should specify a range.
Suppose I created a chart using the range A1:B2. Later I’ve added one more row to my data and now the range is A1:B3.
If the chart can automatically grow based on this new row, we can call this a dynamic range.
I want the chart to include this newly added row to the chart area automatically. In other words, I want the chart area dynamic.
How to Get Dynamic Range in Charts in Google Sheets
Dynamic ranges in charts are possible in Google Sheets. Even if your range is set to A1:B2, when you add a new row, Google Sheets will automatically identify the new row and add that row in the chart area.
If you don’t want to include this new row, you can just leave one blank row and start the entry.
But the above is not applicable to columns. If you add new values to the column (here in column C), it won’t get added to the chart.
To get a dynamic range in charts, what you want to do is select additional blank rows and columns.
Yup! Even if these selected rows and columns do not contain any value, it won’t affect the chart area. It won’t leave any blank space on the chart.
I am going to explore this feature with the help of a drop-down menu. In this Google Sheets tutorial, you can learn how to tame the Charts.
Here you can learn how to create a fully flexible chart in Google Sheets with dynamic ranges and drop-down filtering.
For example purpose, I have sourced some sample data from this wiki page to plot a dynamic chart with drop-down control.
How to Prepare a Drop-down Menu for Chart
Sample Data & Data Validation:
The below sample data shows the list of most 10 populous countries in the world (UN estimates) and their population in millions.
Now I am setting a drop-down menu in cell N1 to control my Chart.
Steps:
- Click cell N1.
- Go to the menu “Data” and select the Data validation menu option.
- Against “Criteria” select “List of items”.
- In the blank field against please insert “All,2000,2015,2030” without the double quotes at both ends.
Similar: How to Get an All Selection Option in a Drop-down
The Formula for the Drop-down Menu to Get a Dynamic Range in Charts in Google Sheets
In cell O1, use the following Filter formula.
=ArrayFormula(
if(
N1="All",
A1:M,
{{A1:M1};filter(A2:M,A2:A=N1)}
)
)Formula Explanation
If you select “All” in the drop-down menu in N1, the formula would return the data from the range A1:M as it is.
Here is that part of the formula:
if(N1="All",A1:MThe actual range is A1:K4. But I’ve included two additional columns (L and M) and infinitive rows in the range to make a dynamic range in Charts.
The second part is the Filter.
{{A1:M1};filter(A2:M,A2:A=N1)}If the value in cell N1 is not “All”, the Filter formula would filter the data in A1:M based on the year selected in cell N1.
The formula could actually be as simple as like this;
=filter(A2:M,A2:A=N1)But it has one problem! For example, if the value in cell N1 is 2015, this formula would only return the value from that row.
I want the field labels from the first row for the chart. So I’ve included that row using the Curly Braces.
Creating a Dynamic Range Column Chart – Settings
Now you can control the source data. You can filter the source data using the menu in N1. Select “All” to get all the data as it is, select 2015 to get the data for the year 2015, and so on.
Now I am creating a chart with this range in O1:Y4. We must select the range O1:AA as we are creating a dynamic chart, that can accommodate two additional columns and an unlimited number of rows.
Note: Only keep the rows that you want in your sheet. I am only keeping 15 rows so that the chart will be fast responsive. Otherwise, the formula in cell O1 may slightly delay the loading of our dynamic range chart.
Here are the final steps to get a dynamic range in Charts in Google Sheets.
- Select the range O1:AA.
- Go to the “Insert” menu and select “Chart”.
- I am creating a Column Chart. In the “Chart Editor” do the following settings.
Settings under the DATA tab:
- Select the Chart type: Column Chart.
- You will see three options selected (ticked) – “Switch rows/columns”, “Use column O as headers”, and “Use row 1 as labels”. Remove the tick mark against “Switch rows/columns”.
- Then the last two options will be changed to “Use row 1 as headers”, and “Use column O as labels” and both selected. That’s what we want.
Settings under the CUSTOMIZE tab:
Under “Horizontal Axis” check “Treat labels as text”
See how the chart adjusts as per the drop-down selection. No additional white space is appearing on the chart.
Conclusion
The above is a perfect example of the dynamic range in Charts. You can add unlimited additional rows and two additional columns to your existing data (A1: K4) to get a dynamic range in Charts in Google Sheets.
If you add more rows, I mean data for a few more years, don’t forget to include those years in the drop-down menu.
The chart will automatically shrink and expand to the area automatically (see “Sheet2” of my sample sheet below). Enjoy!
Thanks for this. I was looking for this. Especially the extra Sheet3.
Prashanth,
It was a great tutorial. I may have a bit more complex of a situation where I need my data and charts to be more dynamic than they are currently.
I track dates of training completion and graph them by the department. As employees come and go, I add and delete rows of data, and I need my charts to be dynamic to respond to more or fewer employees within that department.
I could use a little extra help beyond this tutorial.
I’d be happy to allow you access to my sheet. But not sure how to do that in this setting.
I also need to maintain confidentiality with the sheet. Would you be able to help me with my situation.?
Hi, Matt,
Feel free to share the URL of the sheet below in your reply. I won’t publish that URL.
You may replace personal info like real names, phone numbers, or email IDs with dummy info.
Let’s try.
Hi Prashanth,
Great article! I am having trouble adding the Series label in the Setup tab. In your sample (Sheet3), the label is dynamic and cannot be changed. Where is that set up? If I copy past my data to your sheet is works but when I do one from the scratch I can’t do it. Also, in my case, even I manually insert the country name as a label, it does not appear as the label on the chart.
Any idea?
Many thanks
Hi, Abbas Mousavi,
I may require access to your sheet. Can you share a mock-up sheet?
Thanks for the info Prashanth. Very clever!
I would like to be able to filter up to and including values. So for your example, if I selected 2015 for the filter, I would like the filter to select all rows up until that point (so 2000 and 2015); if I selected 2030 it would select 2000, 2015, 2030, etc.
Any ideas on if that’s possible?
Hi, Matt,
This is the formula you were talking about.
=ArrayFormula(if(N1="All",A1:M,{{A1:M1};filter(A2:M,A2:A=N1)}))In this replace
filter(A2:M,A2:A=N1)witharray_constrain(A2:M4, match(N1,A2:A4),13)This way you can filter up to the row containing the selected value in the drop-down instead of only the row containing the selected value.
This is really cool! Thanks for this, but what if I wanted to filter by countries using the same data layout?
Hi, Jeff,
There are two changes to switch the filtering from the “year” column to “country” row.
1. Change the Data validation list (cell N1). In the field instead of years, use the below values.
All,China,India,USA,Indonesia,Brazil,Pakistan,Nigeria,Bangaladesh,Russia,Mexico2. Then change the Filter formula in cell O1.
=if(N1="All",{A1:M},{{A1:A},filter(A1:M,A1:M1=N1)})If you check the post again, in the end, you will see I’ve added a link to my sample chart sheet. In that open the “Sheet3” for the chart as per your requirement.
Note: Inside the chart editor, under the “Setup”, enable “Switch rows/columns” to see how the chart adjusts.
Thank you so much, Prashanth! I tried to figure out it before asking, but this is very helpful. I appreciate your quick response and help!
This is great information – thanks! I’ve got a slightly different challenge, and was wondering if you might have some insight.
I have a tremendously long sheet (hourly data from several years), and I only want to chart the last n data points (maybe 500).
How would you suggest I go about accomplishing that?
Hi, Andy,
Here is one example.
Range: E1:F (E1:F1 contain field labels)
In this, I am filtering only the last 25% data. I mean the formula offsets the first 75% of rows.
=query(E1:F,"Select E,F where E is not null offset "&round(counta(E1:E)*75%)-1)To get the last 500 data points, as per my example, the formula will be;
=query(E1:F,"Select E,F where E is not null offset "&counta(E1:E)-501)Wow! This is very cool and useful! Thanks for sharing!