How to Create a Dynamic Chart Range in Excel

When you create a chart in Excel and the source data changes, you need to update the chart’s data source to make sure it reflects the new data.
In case you work with charts that are frequently updated, it’s better to create a dynamic chart range.
A dynamic chart range is a data range that updates automatically when you change the data source.
This dynamic range is then used as the source data in a chart. As the data changes, the dynamic range updates instantly which leads to an update in the chart.
In this video, I will show you two ways to create a dynamic chart range in Excel - using Excel Table and Using Excel formulas.
In most of the cases, it is better to go with Excel Table as it is quite easy to create and manage.
In case you are using versions prior to Excel 2007 (which don't have the Excel Table feature), or can't use Excel Table, then you can create dynamic named ranges and use these to create dynamic charts.
Read more and download the example file: trumpexcel.com/dynamic-chart-...
Let's Connect:
Google+ ► plus.google.com/+Trumpexcel
Facebook ► / trumpexcel
Twitter ► / trumpexcel
Pinterest ► / trumpexcel
KZbin Channel ► / trumpexcel.com #Excel #ExcelTips #ExcelTutorial

[@sreejarathish]

You saved me from a bad situation.God bless you!!

[@jorisvanh.9274]

Thank you so much, it was really hard to find this solution online.

[@user-hk3oj5nk3t]

The only source that helped me... Thank you...

[@clutchingatlight]

Great video - clear step by step explanation that assumes no prior knowledge.

[@hellolikebefore]

I had been looking for this for years! Thank you!!!! Because the way I structured my table is different, I could not use the table format, so I appreciated your Named Formula solution! Thank you!

[@szabolcsagai]

Thanks, exactly the thing I was looking for

[@djtf99]

This may be a long time after upload but I have to let you know that this video helped me so much. Was really bugging me not being able to figure this out for a uni assignment that I stayed up to 5am even tho it wasn't due until 3 weeks later trying to figure it out. Thanks heaps.

[@davidstem1557]

Fantastic and simple tutorial. Thank you!

[@mymatemartin]

Very very clever use of formulas in the Name Range definition to create dynamic behaviour in the chart.

[@PatrickHund]

Super helpful, thank you very much for this! One thing to note: while method 1 is more convenient, method 2 is more powerful - I've used it to create charts where I can control the range of dates through a field in Excel

[@Ukjsydney]

Thanks Sumit - Very useful and well presented!

[@salmanzangeneh3378]

Great explanation dude! 👌🏼👌🏼

[@christopherlawes6081]

2nd method works best for me. Thank you so much. Such a good trick. You'd think Excel would behave like this by default.

[@moncayoda]

Thank you! Just what I needed!

[@L33tTurtle]

You are amazing, and did a great job explaining this concept. Also this video is very well done. Thank you

[@arthur333able]

Really appreciate the help!

[@ih9346]

exactly what I needed, you're the man

[@xaminmo]

Thank you! No one else was explaining Tables, and the named range examples I could find were only for adding rows. My dataset is columns, and I did not want to have to transform it every time it updated.

[@dinsay01]

Thank you Summit... it is very helpful.. In my case I need the second method...

[@viovuu5042]

Super this is what i was looking for, thanks for sharing

[@pierjade4792]

Good stuff, thanks for sharing

[@johnnykama8356]

Excellent video

[@enesyilmaz]

Thanks for sharing bro

[@osamaasif9601]

Thank you sir.

[@izzat_kiswani]

You are Good ! Even Perfect Thanks A lot ✔👍

[@dipankarbiswas033]

bro u r awesome .. thanks

[@syncuser1231]

@Trump Excel : Sumit, this is great. Thanks so much. And all the very best!

[@rrrprogram8667]

nice one...

[@christopherlawes6081]

Tip if using 2nd method: I found the countif cell blank counted my cells because they had a formula in so do the countif in another cell and refer to that cell in the offset formula

[@Video-Subscribers]

Could you please explain in detail

[@ricardoromo96]

What if my values and months data depend on a formula. Will display values, but in case of error, display a blank (""), the chart still plots the blanks as zeros.

[@shovanmania1]

In order to update table range on data deltion you need to press "ctr + (-)" delete key just clears the content

[@evani9292]

thanks sir

[@chincc5185]

Great!!

[@ranjan4495]

Thankyou sumit sir, your videos have been precise & helpful. I have 1 question, I replicated the sheet as of yours, for values offset it is coming 28(same code, only change of column name), but for month it is Feb. If possible help me to get corrected, why is it so in my case?

[@EdgarRoock]

9:53 Not working for me. Shrinking and expanding the data range afterwards does nothing to the chart although the named ranges have adjusted

[@miteshladani4040]

Very nice video... Sir can you make video if we have only one cell data changing and its record in chart

[@renuka2740]

can u link a scrollbar to the x axis to make the graph scrollable? if yes plz can u make the video for that

[@md.masumomarjashim]

This works for static data. What if you are extracting data from other sheets using INDEX & MATCH and need to use offset for those cells?

[@oOBlindyOo]

any way to do this same thing but when your chart includes duplicate character names. IE In months if you had two values for "march" I've used an index array formula but my charts plot all the empty data points as zero because the formula.

[@ahsanshamim8095]

good

[@ranjan4495]

sumit sir, if I have lakhs of data as in 1 min tick data of any stock & I want to see only latest 40 data sets, then how to do it?

[@rosacampos5033]

the problem I'm having is that I have is that the way i have structured my spreadsheet is that row 6 starts 10/17/2022. This spreadsheets is updated daily. I work in the oil and gas industry and I update production on a daily basis for different wells. My spreadsheet is continuous, so when one well ends the second well starts and so on. Every day I insert a row at the of the First well and add 10/18/2022 and update my graph for Well 1. Then I scroll down to the end of the second Well2 and add 10/18/2022. How can I add all data for 10/18/2022 at once for all Wells and get my graphs to update all at once. I know you have to do some type of linking. Help

[@23hookster]

when i enter offset formula the formula spills into the rows below - what am i doing wrong? Thanks

[@sagardhotre7222]

Sir if the data is getting updated by formula then Table method is not working. Pls help

[@yesuratnakumarirenee3644]

Good bro but excel 2007 videos prepair please

[@ShubhamPatil-dx2sq]

hii man , i am using the offset formula , the way you have taken B2:B100 i have taken B9:B9000 and when i press F9 it comes error of 8192 characters that i have crossed linit in formula, guide anyone

[@fmshyanguya5351]

I have daily water meter reading data from which I calculate daily water consumption. How do I automatically display on a chart Today's Date and the last thirty days? I would like every day to see on a chart a snapshot of only the last rolling 30-days. For example today is the 31st, I would like the chart to show today and past 30 days. Tomorrow will be the 1st and I would like tomorrow to automatically show the 1st and last thirty days from the 1st, etc