How to Create a Dynamic Chart Range in Excel using Dropdown 

Get access to the complete Excel Dashboards course here 👉 www.xelplus.com/course/excel-dashboards/

This is amazing, just added this technique to a massive vibration data spreadsheet which previously took one months worth of data (about 8 columns and 5000+ rows) where the graphs had set ranges for the month, and allowed it to pick and chose between the start date and end of the data set and update all of the graphs accordingly, so I can show 1 week, 2 weeks, 4 days...without manipulating the graphs every single time to reference new rows where that data ends. Love it.

Hi Leila! I spent 2 whole days trying to figure this out and your video helped me so much! THANK YOU SO MUCH!!

Leila, I just discovered your channel, and these videos are rapidly changing the way I work! They are incredibly useful, and your demonstrations are easy to follow. Thank you!

Thanks for the instructions. I made this a truly dynamic chart by avoiding the user selected start-end dates. My dates are in column C. Special Note: Both of the Start-End Dates MUST be in the column data set or the function: Index(Match):Index(Match), will display "#N/A" End date: H3=LOOKUP(9.99E+307,C:C) Returns the next largest value if the 9.99E+307 is NOT found. Start-End Date delta: J2=30 (or any other integer value) Start Date: H2=LOOKUP((H3-J2),C:C)

WOW! that makes it so much easier - thanks for making it easy to understand, and I appreciate you explaining why you were doing what your were as you worked it out.

Thank you for this, many things here I had not tried before. I copied your method, and then changed to the table like you did. I then used ‘Structured Table names’ to create the references in Name Manager, which I think makes things a little easier to read. (Making the dynamic array now ‘Spills’, so it looks a bit different to the video, but works exactly as you show it)

I was struggling with a similar chart using OFFSET() as the named range rather than INDEX(), but couldn't figure out the 'sheet name' requirement in the chart series name. You saved my day !! Thank you!!

the things you teach and the way you present, amazing.

Excellent, loved the simplified way to teaching. Thanks a lot Leila.

Thank you SO much! This is exactly what I've been looking for, to a T -- how to chart [profits/losses/etc.] by dates using drop-down lists and a dynamic chart. This is great!

Thx this video helped me in creating a dynamic 12month range chart using offset..didnt know formulas can be inserted in simple chart data using name manager...

This is great.Just seen the power of the INDEX():INDEX() formula which I hardly knew of

This is exactly what I need and it is so well explained! Thank you.

Leila, you are really so amazing! Thanks so much for putting this video together. All of your videos are so practical and you are my life saver :)

I am glad that i finally arrive at a single channel on which i can get answers for most of my excel related queries. Can you help me with formula to find out monthly high price of a stock from given dataset ( daily Open, high, low and close) Automatically as month change to next one (I don't want to manually change the cell range for Max function as and when month changes).

Utterly brilliant. Great presentation. You have answered so many questions I have on using Excel.

WAW - I Like it so much. that's the first time I learn several new out of the box tool in Excel. thank you.

I love this tutorials from you, very common and it change my work with data. Great work

Thank you for this video. It explained perfectly what I was trying to wrap my head around.

Thanks so much , i am searching this for ling time for my automate excel project😊

You indeed have saved me several hours of work every week or so. Remain blessed :)

Thank you Leila for an easy explanation of dynamic ranges using INDEX. Very helpful.

found this to be very usefull, have been searching since 2weeks for this result.thanks leila

Old as this may be, your video is glorious. Thank you!!!!!!!!!!!!!!!!!!!! I subscribed

Very well explained with hands on example on the fly.

Thanks for the INDEX:INDEX fun : )

Outstanding ideas of Index():Index() Thank you so much, you have made my days better 😀🙏

Thanks Leila. This video was very helpful and provided me with just what I needed.

Thanks. that was complicated but a big help. Forget just one "$" sign and it gets stuck. I like the unique feature that you can select the "From"-"To" dates.

the video is 6 years old and still solved a problem for me. Using with the spill feature did not work for my problem. This solved it.

I usually do this task with offset but I had fun with INDEX:INDEX Thanks

This is exactly what i have been looking for & the way explain it WoW.........................!

Great video and not oversimplified.

Absolute mint ! You really explain things sooooo simply. Thanks a lot for your time and effort

Thank you very much. This is amazing video!

Tried it and it works really well! Thanks.

SIMPLY PERFECTION !! As always, Thank You !!

This saves my day...thank you.

Very good video. I wish you the best. Quite carefully prepared.

This is just great. You make it so simple. Thank you very much!!

Thank you for posting this. This is an excellent approach.

Amazing video, i like the way you explained... Thumbs up

Great video and simple to understand.

Very very smart way! Thanks a lot!

Pls make a detail video on how to plot Live data in graphical form.. for example- time vs change in A PRICE. THANK YOU

This video just solved all my problems

you are great Leila !thank you so much you saved my day

Is there an advantage to learning all ways to craft formulas and dynamic charts instead of pivot table charts?

Amazing, just what I needed! thank you

Hi Leila, Thank you so much for creating thid youtube channel and sharing your excel knowledge with all of us. This tutorial especially was soooo helpful ! Thanks again and keep posting videos :-) Cheers

Thank you very much for this tutorial its a big help

Pure GOLD! Thanks for the video, super use full!!!

Mind blowing. Thats, what I was searching. Thanks

Easy to understand. Thank you very much.

Thank you so much. Eagerly searching for this.

Thank you Leila. This was so useful!!!

Thanks a lot i think u r the best Excel trainer

Thank you, very much. That's the kind of solution I was looking for.

Your are a freaking wizard

Very good presentation thumbs up!!!!!!!!!! simple and practical

Great video, did exactly what I needed. Thank you

Very helpful, many thanks

I tried this and it worked, thank you! However, I developed a problem from when I created the drop down selection lists ("From" and "To"). The lists only go so far and not up to the last date in my dataset, which consists of 61,532 rows. Is there a limit on how much data the feature could process or display?

This was VERY helpful! My only issue is the values on my axis labels are not updating. Any idea why?

I wasted 1 hour because I did not listen about the F4 fixing before copying to name manager... thank for the video, every information was there, I was just hasty !

Thank you for this Solution!

This is amazing! Thank you so much for the tutorial

Clear and precise.

Hi leila, index():index(), amazing, very nice, i'm really excited, thanks. Please create new videos about dashboards.

Thank you so much Leila!! You’re a lifesaver!! ❤️❤️

Excellent video.

Thank you Madam, The content was well explained and easy to understand

Köszönjük!

It works!! Thank you so much!!

Its just wonderful.. but I have an issue using it as in the horizontal axis of the graph, along with the date I have to present actual or forecast. I mean can we concatenate any text data along with the date??

Exactly what I was looking for. Thank you!

Unless you already have a video covering this, could you create a follow-up video to this, one that explains how to have the Selected Data of a chart change based upon the values of two or more cells? That would be fantastic in the circumstance of my excel charts where I need the Month and the Data Type to determine the Chart output. Another chart I'd like to be able to do more: Dropdown fields for Month, Day, Time, and Data Type when selected all making themselves determining factors for the Chart data selection. Thanks for the consideration!

It is very useful. Tried to create a dynamic chart from multiple sheets/tab3 in a single excel file and the data wouldn't auto-populate unless the raw data was on the same sheet. How do I fix this? Thanks.

wonderful . thank you so so much. I've been wanting to do this for a long time!! thanks again.

Thanks madam for such a useful tutorial

thank you very much great video

Thank you SOOOOO much.

Dear Ms Leila Many thanks for your valuable teachings. Your videos help a lot with excel and they are very easy to follow. I have a question which I hope you could make a video tutorial on (if possible). I have a list of items throughout the duration of 52 weeks... these items show the number of data for each. Question: 1) how can I have a dynamic chart where I can display the items based on my selection of the week from a drop down list 2) only show the items that show values above zero. Note that my data is an array and not a single column. The main thing in all this is NOT using pivots, and how can I keep my sheet simple and clean by embedding the formulas within the chart itself I really would appreciate it if a video could be done since it would be a great reference for all of us...

This is a great video. One question though - I've used your method above but I have tried to select any date (i.e. not exact dates in the range). It works for the end date but I get an error when trying to amend the start date?

Thank you ma'am, it really helped 🙏

Just what I was looking for! Thanks...

Thank you so much Leila..

Currently working in Excel 2019 and every time I use names it just takes the cell reference for the name at the time of entry and does not retain it and therefore dynamically change. Most frustrating.

Awesome tips!! Thanks a lot.

Hi, Is there a Option hide specific data from the chart using this method? For example: choose the data between 2/24/16 and 2/27/16 and hide the data from 2/25/16

wonderful demonstration however my dropdown list of dates do not change automatically if i add in more data below the table. The data shows that it is inside the table (the bottom right mark is visible) however the new entries does not come in dropdown list

Very good thanks, nice and easy to follow and change around to fit what I need to do. Now to work out how to match the 'date' to multiple 'values' :)

minimal & powerful.I love it.

Leila, would you be able to update this dynamic x (date) axis video using the new Excel functions like Filter?

Hi Madam! I am frequent viewer of your excel technique videos and more over I am practicing each steps, However thank you very much and really interesting each your lesson. In this video, every thing is perfect unless I select a table after adding date and values, but when I am selecting the drop down dates, the line chart not working! What will be the reason? please advise

My Guru ❤

very useful and thx a lot.

Does this method work when the chart data is on different sheets? How about if you change the name of the workbook (rev 1, rev 2, etc.). Will the named range data update or will it create a link to the previous version?

thank you sooooooo much for index:index,