Excel Tricks: Switch Charts with a Simple Dropdown Menu

If you've got a table to show different views of a data series, you might want to show it one at a time. For example you may want to compare the sales, costs and profits of different items, but not all at the same time. Instead of creating three separate charts, you have a drop down for selection and it will show the respective view of your data. Dive into our latest tutorial where we unveil the technique to elevate your data presentation skills. From the very start, you'll learn how to streamline your Excel dashboard by integrating a dropdown menu that effortlessly toggles between different charts. This not only enhances your reports' interactivity but also saves valuable space and time. Perfect for beginners and advanced users alike, this video will guide you through each step with clarity and precision. Join us as we unlock the full potential of Excel, making your data visualization more dynamic and user-friendly.
🔔 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...
~-~~-~~~-~~-~

[@DougHExcel]

If this video didn't fit, there are some other Dropdown List video at kzbin.info/aero/PL-n8f1cY_Qw9-ZSEpRN3OLmzYp4w54vht

[@DougHExcel]

...

[@christinastewart13]

WOW!! Great video and so helpful! Thank you!

[@anonimous1739]

This is a really good video. I use to work with charts and liked the fact to create not only boring but dynamic and awesome charts :) Thanks for sharing your knowledge Doug. You're making our lives easier.

[@christieindah2517]

Great tutorial. I need this for my report. Thanks Dough.

[@ashleye7692]

Thank you! This is exactly what I needed!!! You did a GREAT job explaining the process and what/why you were clicking.

[@DougHExcel]

Hi Ashley E, thanks for the kind words!

[@meowphillic]

Oh wow. This really helps me. Thank you so much!

[@henri_sn]

Absolutely amazing explanation and step by step tutorial. Thank you so much Doug, this has been most useful. I've already used it in some of my work, can't wait to show my managers. Keep up the good work!!!

[@DougHExcel]

Hi Henrique Noronha, glad you liked it, thanks for commenting!

[@whyyyyy3]

Great learning, this what I am looking for. Thanks

[@flashbangonentry8320]

Thank you for such a clear explanation. You're a great communicator. Will definitely be one of my main go too's for anything excel related in the future. Keep the videos coming!

[@DougHExcel]

Hi Bradley Hunter, glad you liked it, thanks for commenting!

[@rkumar4747]

This was so much help. Thanks a lot.

[@rajnajat]

Excellent help ! Very down to earth and clear cut ! Thanks a lot

[@DougHExcel]

Hi Raj Najat...you're welcome, glad you liked!

[@lezliewood7982]

I think this is brilliant. I needed a dropdown list with the months Jan-Dec to populate a monthly challenge chart from a different workbook. I followed your video step by step and voila, it worked. Thank you for making a very comprehensive video for anyone (i'm somewhat a newb to Excel) to follow. One thing that you don't mention that took me a while to figure out is when I locked the sheet, I was getting an error in my dropdown that the cell was "locked" even though it wasn't. I unlocked all the reference worksheets and unlocked the combo box but was still getting the error when I protected the worksheet. So I thought it would be noteworthy to say that the reference cell (the one that gets covered up with the dropdown box (1, 2, 3, 4... etc) must also be unlocked for dropdown to work in a locked worksheet. Thanks again, great videos.

[@delicatevenusflytrap9091]

This info was so useful to me, can't thank you enough! Used it to create 15 charts with data at work and the drop downs I know will be a hit with the executives. You have a great way of explaining/teaching, thorough without too much detail that you get lost.

[@DougHExcel]

Glad it helped!

[@abiolao5371]

This is so helpful. Thank you so much! love from Nigeria

[@DougHExcel]

Thanks Oluwagbemiga Abiola, glad it helped!

[@shahzadhabib6929]

Thank you for making a very comprehensive video.

[@DougHExcel]

You're welcome!

[@ncnc2896]

simply simple and great ! thks

[@eprovo]

Great video, very helpful and straightforward

[@DougHExcel]

Hi Ernest Provo, thanks for the comment!

[@rjbanavar]

Dough You are amazing. Thanks

[@DougHExcel]

You are very welcome

[@Dittrb]

Thanks a lot! Just what I needed :)

[@DougHExcel]

You're Welcome!

[@heshammigri7381]

that was really helpful, many thanks .

[@DougHExcel]

You're welcome!

[@Mgsgenesis]

This was amazingly helpful, thankyou

[@DougHExcel]

You're so welcome!

[@ManuelJimenez-bm1wi]

tried and worked, thank you

[@enimobobek]

Really useful, thanks!

[@DougHExcel]

You’re welcome!

[@marcellagiovanni9160]

This helps me a lot thank you very much!

[@DougHExcel]

Glad you liked it!

[@pelesipelesi8167]

Thanks - much appreciated bro.

[@DougHExcel]

Any time!

[@walkinviolation]

Great Tutorial :D

[@JanBolhuis]

Great video and explanation. Thank you.

[@DougHExcel]

Glad u liked!

[@alphakidstv19]

Video quality is not good....could read what you are typing it...

[@ALPHERATZ3650]

Great; Amazing thank you very much for post this video

[@DougHExcel]

Hi José Prieto...you're welcome, glad you liked!

[@salifdiallo9945]

Merci Doug Très utile

[@elnurshabanov1608]

Great & Simple !

[@DougHExcel]

Thanks

[@aravindbalasubramanian3964]

Thank you very much ! I had to do it for a dashboard and was breaking my head over how to do it !

[@DougHExcel]

Hi Aravind Balasubramanian, thanks for the comment!

[@anuzid]

thank you so much for the detailed explanation. very valuable technique!!!

[@DougHExcel]

Hi Anuradha Urhekar, glad you liked it, thanks for commenting!

[@anuzid]

I was able to use these features to create a project mgmt dashboard and show several different metrics and while saving valuable space. Like Henrique I can't wait to show my mgrs. Thanks again!

[@DougHExcel]

Cool!

[@dennisonyango2084]

Beautiful!!

[@abhaybhutkar2612]

Thanks a lot, mate! Saved me :)

[@DougHExcel]

You're welcome!

[@lalala622]

This Awesome !!!!! Thank you so much

[@DougHExcel]

You are so welcome!

very useful! thanks!

[@DougHExcel]

Happy you found this useful!

[@jennifervargas8429]

THANK YOU SO MUCH!

[@DougHExcel]

You're welcome!

[@selmalarbi8084]

Thanks a lot!

[@DougHExcel]

You're welcome!

[@hahahacho9474]

Brilliant!!!

[@DougHExcel]

Thanks for the comment!

[@MegaLastiko]

Thanks a Lot Doug. I have tried to change the font of the menu but I cannot do it. It seems that I am supposed to use a combo box with Active X but I am not really sure. Have you had this issue before? Thank you :)

[@kylejacobs1007]

This is neat and I think the camera feature is cool but I would just an index match function on a separate sheet. Link the column name to a data validation list on the first page and then use index/match to pull in the appropriate data. That way you dont have to spend all the extra time fitting the graph into the range, creating multiple graphs, etc.. Plus what happens if you wanted to add more detail you have to go through all those steps again. Index and match is much more dynamic. You can also have dynamic named ranges so that if you add more data fields items 8- whatever you have that automated too.

[@DougHExcel]

Agreed, I'd prefer a lookup to do this. But if you're sharing and wanted to "hide" the formula innards from others or just wanted to have this data visualized the camera tool is nifty.

[@jay-rl8092]

do u have a video on how to do it?

[@Masterkruizer]

Thank you very much

[@DougHExcel]

You're welcome!

[@hualuzheng2877]

Hi Doug, your videos are of great help! Thank you very much for posting. I have a question related to this vedio: other than choosing from one drop down box, how can I create interactive charts pulling from 2 or more drop down menus? I greatly appreciate your answer!!

[@hualuzheng2877]

***** Thank you very much Doug!!

[@parthoadhikary5293]

Nice video

[@DougHExcel]

Thanks!

[@dk71arya]

Hello, I got a question. Is it possible to select drop down with Alt Key plus Right Arrow to see next chart or Alt Key plus Left Arrow to see previous chart. Thanks

[@hhexeh]

Thanks for the lovely walk-through Doug! I've used it for my pivot charts with 2nd option of picture link pasting. It worked perfectly on my computer but not on anyone else's computers. The image was zoomed and combo box selections didn't work. Do you know what the issue may be? Perhaps, something needs to be enabled in Excel on other computers? All use Excel 2016.

[@DougHExcel]

they might check the trust center options in Excel support.office.com/en-us/article/view-my-options-and-settings-in-the-microsoft-office-trust-center-d672876e-20d3-4ad3-a178-343d044e05c8

[@hhexeh]

Thanks so much for your reply and suggestion Doug! :) I'll check it.

[@hhexeh]

Thanks again for your suggestion Doug! We've managed to get the issue resolved. The computer had Windows 10 OS , we've restarted Office settings and also went into windows display settings and there was a setting to disable auto scaling for specific programs including Excel. Once we removed Excel from the list, the chart and combo box started working fine.

[@ramkrishna-ms2xr]

Hi Doug, Video is good...But if i change the data in table, charts its not changing

[@raymondryanmeneses3001]

"Reference isnt valid". How to fix this?

[@niklasb.6288]

I got the exact same problem. I tried 100 things and nothing worked. I hope there is someone how could help us

[@DougHExcel]

named reference ranges could be pointing somewhere else...try from scratch may help

[@pankulbaijal7542]

Very nice, Can we use the same procedure to showcase the quality target and what we get person wise for 3 work types.. Thanks for sharing. Happy Learning.

[@DougHExcel]

Hi Pankul Baijal, thanks for the comment! Maybe one of these video can give an idea kzbin.infosearch?query=target

[@pankulbaijal7542]

@@DougHExcel thanks a lot for sharing but I have checked that it is not like helping me out. Actually my question is that if a process 3 work types and want to make same drop down chart changing name wise then can me make that, if one task type has its own quality target and other has different, so it is possible. That's my question. Hope will understand clearly. Waiting for your revert. Thanks

[@SEM9090]

excellent

[@SEM9090]

+Doug H I need help with making more than one chart linked to one drop down menu. Is it possible? e.g. I have expenses & revenues for the same list of businesses. I was to put up two charts (expenses and revenues) against the drop down menu that lists my businesses.

[@thetrimtab]

Hi Mr Doug H Why do I get 0 or #VALUE! instead after selecting the cells name below the chart in the snapshot box? the camera function would return similar values of the ref cell if I input numbers but not when I input chartname like s, e although excel recognized that I have charts under those names.

[@DougHExcel]

PrincessSynd, that is very interesting...thanks for letting me know.

[@ahmadhuseynli2073]

what is the function of "Sales2, expenses2, profits2", it was typed in the list, but I could not catch what was the point ? how is it connected with the table ?

[@xiangwen-shawnwang4431]

Hello,Doug H, it is really great. but the the it is very blurry if the form choose is very big, like 500+ cells. do you have any ideal to solve the blur issue for the camera image? thank you in advance.

[@DougHExcel]

Oh that's a lot. Maybe not suitable for the linked image (camera) feature.

[@samkragtwijk9657]

Great tutorial! I was wondering, is it possible to do the same with an ActiveX combo box? I have been trying it, but it does not seem to work. I prefer the ActiveX combo box, since I can change the design of the box itself.

[@DougHExcel]

I believe so, but haven't tried it 😬

[@philiplousuner6373]

How can i change the font size in the dropdown box?

[@memerys]

So ive used this video to make charts like this and worked great.. What im wondering is could change charts with two drop down lists? How i mean is.. I want one list to list week numbers and change the charts depending on the week, And i want the other list to change which shift pattern.. So i can say look at day shift week 1, but can change shift and week.. Hope you can help thanks

[@DougHExcel]

Have you thought about slicers? kzbin.info/www/bejne/aonHq4aJpLhgmK8

[@erikjohnson5204]

Hey Doug This is a great excel video and I added it to my favorites. However, I was unable to get the camera window to show the graph for the selection. For example if input "44" in the field it will show up in the window, but if I put the range of cells for the graphs it shows up "#VALUE! Any tips?

[@erikjohnson5204]

***** I was just using that as an example that entering numbers into the box and having them appear in the window works, but when I try and use the 'graph range of the cells' an error message shows up (

[@katielondon3607]

Thanks. I want to Use a Dropdown to Switch between Charts, but I don't have "File" command on my menu bar. Can you help me find it? I have Microsoft for Mac version 15.28 which only shows Home Insert Page Layout, etc.

[@DougHExcel]

Hi Katie London, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!

[@Eckert68]

Doug your videos are of great help. Thanks for posting. I do have a question. When I follow your video everything works well. But if I make changes in the worksheet by adding new charts (etc...I don't change rows or columns) OR if I save and exit excel and then open it up again my worksheet that populates the charts only shows the cells in which the chart sits in and not the chart itself (on your video...s, e, p)..... I then have to go in to the chart worksheet and refresh each chart and then it works....but I have to do it all the time.....I locked each chart within the cells (alt button).....so I am confused why it keeps doing this...do you have an answer? thanks again...

[@Eckert68]

***** I checked and all good. Even unchecked and checked again for auto calculations. For some reason when I exit and go back in it still at times does not populate chart but shows cells behind chart......I have to go through each individual chart (36) to re-save name of cell block holding charts...then it works again.

[@soumiamoujane3695]

My chart is not corospnding to my drop-down list.

[@oBerriesx]

Can you do this with more than 3 columns? I am trying to do this with dropdown charts for an entire volleyball team but when I define the range of cells and it switched over it distorts the image into a much larger chart with only the top part of my data.

[@DougHExcel]

enlarging the the dimension may help but the distortion on the final eventual switched chart is inevitable if the difference in source-destination images are large

[@jfish620]

do the graphs have to already be created in other places, or can this create a graph based on what is selected from dropdown?

[@DougHExcel]

These are created graphs, the dropdowns just reference them.

[@jay-rl8092]

on the define name part where i select the graph and change it to "selection2", im getting an error after that..it says "reference not valid"

[@niklasb.6288]

I got the exact same problem.

[@DougHExcel]

might need to check the referenced range

[@lolyourmom26]

Doesn't work anymore, at least for me. "Reference isn't valid" when referring to selection2.

[@anilkesava809]

Very nice, but I am not getting selection2 graphs -- i.e after getting =s graph -- I am not able to get =selection2 graphs-- automatically changing according to dropdown -- when I am trying =selection2 -- it was showing error. Please help me regarding this.

[@DougHExcel]

You may need to check your named ranges and the camera again.

[@rithvikkambli3398]

Can we do this for multiple charts with a single drop down list

[@DougHExcel]

yes

[@alexrosen8762]

Wow!!!

[@DougHExcel]

:-D

[@darshanapraneeth8762]

nice

[@DougHExcel]

Hi darshana praneeth, thanks for the comment!

[@huntyBot]

Is it possible to get the same result but using a scroll bar?

[@DougHExcel]

this might give insight >> kzbin.infosearch?query=scroll+table

[@shibujoseph8498]

Sir, i wan to know more in form controls and VBA, Please help me

[@DougHExcel]

sorry I'm not good with VBA :-(

[@spydymn5114]

i get an value error at first chart using name manager when click on name of range doesnt import picture just get value error - any ideas please

[@DougHExcel]

maybe re-check the selection of the range/name

[@the_solorider7090]

Which software do you use to record your screen

[@DougHExcel]

Camtasia

[@mayaweatherspoon1743]

Hi Doug. This is a really great tutorial. Would you happen to have anything that would show me how to do something similar using a pivot table and drop down lists? I created a drop down list and I want to link it to my pivot table so when I change the drop down it will show the data related to that category in my pivot table. Of course I can just add the field to the pivot table, but the descriptions are long, so it widens my columns and my spreadsheet doesn't look nearly as nice :-)

[@mayaweatherspoon1743]

That would be great, thanks!

[@clariceannjaducana6481]

+Maya Weatherspoon I have the same problem with you, how did you do it anyway?

[@marypietsch7150]

This wasn't working for me, I kept getting the "reference isn't valid" that others have mentioned. I noticed the same question first was written two years ago, then same question from others came along, but never a response. For those getting this same problem, I found that if I manually typed in the names I had used for the charts, it worked.

[@DougHExcel]

Hi Mary Pietsch, thanks for adding to the thread!

[@skotten91]

Thank you for the video, it solved my problem but it also created a new one. After using the solution with the camera tool, comments in other cells wont show. If i highlight a cell with a comment, the comment show for about 0,1 seconds and then disappears. If i change settings to "Always show comments", it always show comments, but i dont want it to show all the time (only when i hover the cells). The same problem with drop down lists. The arrow that is supposed to be showing when i highlight the cell also shows for about 0.1 seconds and then disappers. If i remove the picture from the camera tool, everything works fine again. It's almost like the picture is getting highlighted constantly. Do you know any solution for this?

[@skotten91]

+Doug H This code solved the problem: Private FRg As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Update 20140909 On Error Resume Next If (FRg Is Nothing) = False Then FRg.Comment.Visible = False End If Target.Comment.Visible = True Set FRg = Target End Sub This codes makes the comment to appear when clicking the cell, instead of when you are hovering it. When clicking it, the comments now stays until clicking som where else. Good enough for me. Thanks for the help!

[@catherineampoan8469]

cant even name the cell of the chart whenever i did, it keeps on indicating 0 in another worksheet Any ideas?????

[@DougHExcel]

Hi Catherine Ampoan, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!

[@mithranlifes1083]

Once re-open the excel file all the past special images are cropped or compress any one pls help me

[@Linda2011Donahue]

did you ever figure this out? I am having the same issue'

[@mithranlifes1083]

@@Linda2011Donahue yes I have created VBA for avoid this error.. like refresh ...r u freelance???

[@Linda2011Donahue]

I have no idea how to do that. Think i can google it?

[@mithranlifes1083]

@@Linda2011Donahue I have not get it ...if you can pls let me know if any better options

[@DougHExcel]

may have to do with objects resizing if columns or rows adjust dimensions. Right click image and under size & properties select radio button "Don't move or size with cells"

[@prasenjitmukherjee5010]

How to convert this in Power Point Sir?

[@DougHExcel]

To retain functionality you may need to embedded it as an object into PPT

[@joymondal3199]

naic

[@charlknighton5909]

Just add single quotes before and after your sheet name - Example - CHOOSE('Graphs'!$D$2;Jan_2016;Feb_2016)

[@DougHExcel]

Thanks Charl Knighton!

[@charlknighton5909]

No Problem - Glad I could help.

[@kreedenole]

Great suggestion. Sorted out the problem I was having with this excellent technique. Thanks

[@beelite6112]

i CAN'T FIND THE CAMARA

[@DougHExcel]

try to add it to the quick access toolbar QAT kzbin.info/www/bejne/pKm4hZ2qe9h3g80

[@wasimbader9170]

very nice but " the excel kid " has done the same or may be its you. now i will watch again as you are too fast for me.

[@alphakidstv19]

Not a good quality videp

[@DougHExcel]

Dylan Mathis, that is very interesting...thanks for letting me know.

[@alphakidstv19]

This is absolutely useful video sir...only thing the clarity of video is not good enough... Especially find name unable to address unless you say it from the background...

[@mshsaroarhossen8837]

Nice video

[@DougHExcel]

Thanks!