Create a Dynamic Chart with Named Ranges, INDEX and MATCH

Подписаться 78 тыс.

Просмотров 76 тыс.

50% 1

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.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 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.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Опубликовано:

13 фев 2016

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 77

@PotatoPatch64 6 лет назад

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 6 лет назад

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

@dominicnzl 6 лет назад

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 6 лет назад

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

@cal_hennesey6343 8 лет назад

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

@DougHExcel 3 года назад

Hi Cal_Hennesey, thanks for the comment!

@nazarenkodenys 4 года назад

Thank you, Doug!

@DougHExcel 4 года назад

You’re welcome!

@ragsanoor 8 лет назад

thank you very much..quite useful!

@CatherinePan 6 лет назад

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 6 лет назад

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

@GIRISHKUMAR-tz7qb 7 лет назад

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

@DougHExcel 7 лет назад

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

@fredfred2776 8 лет назад

Way to go Doug !

@excelisfun 8 лет назад

Great trick!

@StratplayerUK 7 лет назад

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 7 лет назад

So glad it helped you! Thanks for the comment!

@laddaongard5945 7 лет назад

thank you very much..quite useful! :)

@DougHExcel 7 лет назад

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

@JansonSmith 7 лет назад

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 3 года назад

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

@VishalSharma-mn1kf 3 года назад

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

@DougHExcel 3 года назад

Glad it was helpful!

@arne.munther 7 лет назад

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

@sangamwadhwa4051 7 лет назад

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

@DougHExcel 3 года назад

Hi Arne Munther, thanks for the comment!

@CZ-vv9ft 6 лет назад

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 6 лет назад

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

@dennisryan3815 7 лет назад

great tutorial

@DougHExcel 7 лет назад

Thanks Dennis Ryan!

@connorbeck2391 6 лет назад

Youre a g. Thanks

@DougHExcel 6 лет назад

You're Welcome!

@kirankapruwan8892 4 года назад

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 4 года назад

Hi Kiran Kapruwan, thanks for the comment!

@MrSahilspm 3 года назад

Excellent

@DougHExcel 3 года назад

Thank you so much 😀

@creading4480 8 лет назад

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 8 лет назад

Sorry....I don't have a playlist for my charting videos, it's separated by Excel versions. ru-vid.complaylists

@jonathanbryant1191 6 лет назад

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 3 года назад

sorry don't know VBA well

@aravindm4919 3 года назад

Wow !

@DougHExcel 3 года назад

Hi Aravind M, thanks for the comment!

@hrushikeshbehera2483 4 года назад

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 3 года назад

that would have to be refreshed automagically. If it is somethin where you are manually updating on a recurring basis, incorporate the table feature ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Knk_MD_eOpc.html

@patrickcarney3110 6 лет назад

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 6 лет назад

you might want to see these vids for some insight - > ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-lCsvSdk4Tro.html or use pivot table with the fields that allow filter options so you don't have to write these functions ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ZQxgYtl33xY.html

@akina.9589 2 года назад

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 2 года назад

For interactivity with date, maybe consider using the timeline slicer. This video might give some idea ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kgRRCYNCKis.html

@yoliedepaz3340 5 лет назад

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 5 лет назад

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 5 лет назад

Thanks, Doug!

@laurafosci 7 лет назад

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 7 лет назад

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!

@prayagpandya7069 3 года назад

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 3 года назад

may want to incorporate the usage of the table feature. For some insight see ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Knk_MD_eOpc.html

@macroshiv 6 лет назад

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 6 лет назад

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

@Vision267 4 года назад

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

@DougHExcel 3 года назад

have the data use the table feature would help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Knk_MD_eOpc.html

@x24191 2 года назад

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 2 года назад

might want to think of MSFT Power BI. Some interesting charts/graph ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cHJdLv1nt-A.html

@sarojraj1 6 лет назад

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 6 лет назад

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4XdzSSoS-bM.html

@kilroyscarnivalfl 3 года назад

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 3 года назад

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

@kilroyscarnivalfl 3 года назад

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.

@ThalesTheDuck 7 лет назад

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

@DougHExcel 3 года назад

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

@DrCuntFlaps 2 года назад

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

@DougHExcel 2 года назад

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

@nadermounir8228 5 лет назад

it doesnt work for me and is giving me an error

@DougHExcel 3 года назад

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

@himanshuarora7275 7 лет назад

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 6 лет назад

I am having the same issue. Whats the solution?

@DougHExcel 3 года назад

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

@joellanier9135 3 года назад

Why is RU-vid 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 3 года назад

Hi Joel Lanier, thanks for the comment