Create a Dynamic Chart with Named Ranges, INDEX and MATCH

This video show you how to create a dynamic range selection and create a dynamic chart based on a start and end date. There are different advanced concepts presented here like dropdowns with a data validation list, named ranges and using INDEX & MATCH functions to perform lookups. To see other videos on these topics see the URLs below:
Data Validation
• Excel tutorial: Create...
Named Ranges
• Identify Named Ranges ...
INDEX/MATCH
• Use the INDEX & MATCH ...
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@PotatoPatch64]

Thank you for this video Doug. It's exactly what I needed and it is working exactly as expected. Your narration and explanation is easy to follow but even then I made mistakes setting it up and had to step through your video slowly and implement it step-by-step. It put a smile on my face when I finally got it right and the graph changes dynamically according to the start/finish dates. I really appreciate this video, thank you.

[@DougHExcel]

Hi KiwiJohn, glad you liked it, thanks for commenting!

[@VishalSharma-mn1kf]

Nice video. Very informative and clearly explained 😊 Thank you 😊🙏🏿

[@DougHExcel]

Glad it was helpful!

[@ragsanoor]

thank you very much..quite useful!

[@arne.munther]

Great video. Maybe adding dynamic to the ranges, period, budget and actual: offset($B$8,,,Count($B:$B),1) for the period.

[@sangamwadhwa4051]

Arne Munther I second you. that would be really convenient.

[@DougHExcel]

Hi Arne Munther, thanks for the comment!

[@JansonSmith]

I'm having issues with this - help would be appreciated. Running Excel 2010 so not sure if that's an issue. I've created the range with the index formula in, which saves ok. However, when I edit the graph and change the axis to Sheet1!rangename it gives the "We found a problem with one or more formula references in this worksheet" error. On one occasion I got it to accept (don't believe I changed anything), but as soon as I pressed ok, I got the error again and the chart axis showed nothing. Is there another guide on this that you were referring to (maybe in text) so I can try and suss where I'm going wrong ?

[@DougHExcel]

Hi Janson Smith - Amazon FBA, try a post on the mrexcel.com forum!

[@antoniobacic5674]

Can you do this for vertical axis?

[@CatherinePan]

Thank you so much! I almost gave up but because of you I can present on the web trading meeting tom! Very informative and clear. I am a fan! Will definitely watch more of ur videos to learn Excel!

[@DougHExcel]

Hi Catherine Pan, glad you liked it, thanks for commenting!

[@joellanier9135]

Why is KZbin Jamming Political Ads down our throat? And if they are going to start doing that, then perhaps the Fair Doctrine needs to be applied, with Donald Trump getting equal air time.

[@DougHExcel]

Hi Joel Lanier, thanks for the comment

[@DrCuntFlaps]

How do you do this for a date rather than period? Excel keeps giving me an error

[@DougHExcel]

Check if your date is a text string or value. Also check if it’s the same between the lookup values since dates are displayed in forms like mm/dd/yy but excel sees them as decimal numbers

[@nazarenkodenys]

Thank you, Doug!

[@DougHExcel]

You’re welcome!

[@akina.9589]

Hey, great video. What if i wanted something more specific. Based on Month and Days. For ex. The ability to. Use the drop down to select the month. Then another drop down for the date

[@DougHExcel]

For interactivity with date, maybe consider using the timeline slicer. This video might give some idea kzbin.info/www/bejne/oZi1g3aPg6h-n9U

[@x24191]

Is there a way to create a dynamic chart without named ranges and vba? Where the data is pasted into the worksheet might vary from time to time, so I would like to try to account for the possibility that it may not always appear on the same line or column. Thanks!

[@DougHExcel]

might want to think of MSFT Power BI. Some interesting charts/graph kzbin.info/www/bejne/mXmtlX-sZtOnY6M

[@sarojraj1]

Hi, I have seen videos to populate pivot based on the value selection in the drop down using Index and Match function. My data is tricky not able to replicate the same issue. Below are the columns of info I have in the spread sheet:Dept Month Target Actual Difference Mktg Jan-18 100 80 20 Mktg Feb-18 120 118 2 Mktg Mar-18 90 94 4 TeleM Jan-18 200 210 10 TeleM Feb-18 150 148 2 TeleM Mar-18 110 103 7I want my chart to change dynamically. Could you please help how to write the Index Formula. I want to display the chart data by for selected period by dept. I will select the dept and then the range of the periods which will display the chart. Since I have multiple times dept and month are coming in the data spreadsheet it is not giving the right result.Could you please help?

[@DougHExcel]

Maybe consider using slicers. Create a pivot chart from the pivot table and then use slicers to pick and choose. See my slicer playlist for some insights kzbin.info/www/bejne/aonHq4aJpLhgmK8

[@laurafosci]

Doug H do you think the results from the formula index and match are confusing? The outcome from match is actually an index I.E. 5, 8 etc. This is called index in data science languages like Python or R however Excel call it with the function match. When I don't use these formulas for some time I always forget that the index is called with the match function and not with the index function

[@DougHExcel]

I guess it can be confusing when trying to use terminology across different applications! In R, I think it's called an data frame, but in SQL it's called a table...go figure!

[@jonathanbryant1191]

Great info! I can get this working just fine but I have trouble converting it to VBA code. Any tips on how to get VBA to reference the named ranges for y-values and x-values?

[@DougHExcel]

sorry don't know VBA well

[@creading4480]

Hello Doug - I'm enjoying and learning much from your videos. How is the best way for me to get an A to Z of your charting videos? From basic to most complex types of charts.

[@DougHExcel]

Sorry....I don't have a playlist for my charting videos, it's separated by Excel versions. kzbin.infoplaylists

[@macroshiv]

I followed the steps. On changing the chart range to the defined names, I get error pop up - 'a formula in this worksheet contains one or more invalid references. Any solution to that?

[@DougHExcel]

may need to check the names ranges to ensure it points to the required areas

[@hrushikeshbehera2483]

Excellent resource, how can I take the live changing data to next row and create a line chart? OR Is it possible to create live line chart with live data

[@DougHExcel]

that would have to be refreshed automagically. If it is somethin where you are manually updating on a recurring basis, incorporate the table feature kzbin.info/www/bejne/gZ_OkIB6lMqCpsU

[@GIRISHKUMAR-tz7qb]

great tutorial..very helpful and visually appealing. Thanks and hope to see many videos as such, especially on dashboard

[@DougHExcel]

Hi GIRISH KUMAR, glad you liked it, thanks for commenting!

[@dominicnzl]

The commentary with the small mistakes and all was very natural and helped me better understand what was going on. Many thanks for this video

[@DougHExcel]

Hi Dominic Ng, glad you liked it, thanks for commenting!

[@kirankapruwan8892]

This was much needed. Thanks a lot. It helped me a lot. I was stuck when I need to make chart according to selection. I was deleting the sheet name also. By the way we can use offset function to make it dynamic.

[@DougHExcel]

Hi Kiran Kapruwan, thanks for the comment!

[@patrickcarney3110]

How would you do this if you wanted to select a period and either budget or actual and have the corresponding number populate a cell?

[@DougHExcel]

you might want to see these vids for some insight - > kzbin.info/www/bejne/onTWp4aaoJmHqNE or use pivot table with the fields that allow filter options so you don't have to write these functions kzbin.info/www/bejne/kILbmIyqoZhmrrs

[@kilroyscarnivalfl]

I keep thinking I'm getting this, but at work we recently upgraded to Office 365. I think that is interfering with what I'm trying to do. For example, when I created the Index:Index formula, it actually spills the results in that cell and those below. And because I had already formatted my data as a table (much larger dataset, about 500 columns and eight columns of data, looking for a multi line chart) I think that's affecting some of what I am trying to do. Have you run this on 365?

[@DougHExcel]

If the table feature has been used, then formulas created will usually spill down to adjacent cells.

[@kilroyscarnivalfl]

Ah, I finally figured out what I was doing wrong. For my case, it worked better when I created the banned ranges with the score et to the Worksheet level rather than Workbook. Then when I used the names ranges in the chart, I didn’t get a formula error.

[@StratplayerUK]

Great video, it was just what I was looking for.I have to generate update to a report every week day and this looks so much less grief than mucking about changing the chart values! I have about 20 charts to update.

[@DougHExcel]

So glad it helped you! Thanks for the comment!

[@Vision267]

Is there a way to skip changing formulas per each new data entry

[@DougHExcel]

have the data use the table feature would help kzbin.info/www/bejne/gZ_OkIB6lMqCpsU

[@CZ-vv9ft]

Thanks so much for this video. Before I've tried to link the chart with the range but stuck at the edit data. Now I got it that I have to press F3 to change the range. ✌🏻thanks and btw really looooove your accent!

[@DougHExcel]

Hi C Z, glad you liked it, thanks for commenting!😄

[@excelisfun]

Great trick!

[@prayagpandya7069]

If I required to add new row each day then everytime I need to chage range of period or is there any other way??

[@DougHExcel]

may want to incorporate the usage of the table feature. For some insight see kzbin.info/www/bejne/gZ_OkIB6lMqCpsU

[@thales-maciel]

I Just spent a night trying to get this to work when the period dynamically increases.

[@DougHExcel]

Hi Thales Maciel, try a post on the mrexcel.com forum!

[@cal_hennesey6343]

Nice work, your help with these charts is making me look smart at work!

[@DougHExcel]

Hi Cal_Hennesey, thanks for the comment!

[@laddaongard5945]

thank you very much..quite useful! :)

[@DougHExcel]

Hi Ladda Ongard, glad you liked it, thanks for commenting!

[@MrSahilspm]

Excellent

[@DougHExcel]

Thank you so much 😀

[@fredfred2776]

Way to go Doug !

[@aravindm4919]

Wow !

[@DougHExcel]

Hi Aravind M, thanks for the comment!

[@yoliedepaz3340]

Hello Doug, what if the actual is only up to a certain month? Line drops to zero, how can this be avoided? Thank you, great and helpful videos!

[@DougHExcel]

seems like there is some value in the actual cell...a formula? a blank cell shouldn't have drawn the line out...try using the NA() function in the cell.

[@yoliedepaz3340]

Thanks, Doug!

[@nadermounir8228]

it doesnt work for me and is giving me an error

[@DougHExcel]

Nader Mounir, that is very interesting...thanks for letting me know.

[@connorbeck2391]

Youre a g. Thanks

[@DougHExcel]

You're Welcome!

[@dennisryan3815]

great tutorial

[@DougHExcel]

Thanks Dennis Ryan!

[@himanshuarora7275]

I found a mistake in this video. I executed the same for my case. It was all great, yet I prescribe you to test the outcome once again by keeping the END DATE consistent and changing the START DATE. You will see that the values are not changing in the chart when you do likewise! If you don't mind take a stab at amending it, and give the modifications that I must inculcate!

[@macroshiv]

I am having the same issue. Whats the solution?

[@DougHExcel]

himanshu arora, that is very interesting...thanks for letting me know.