Excel Powerhouse: Unleash Dynamic Charting Mastery! - Episode 1181
Microsoft Excel Tutorial: Create a dropdown that will display a different chart on your worksheet!
Welcome to the MrExcel netcast, where we bring you the best tips and tricks for Excel. I'm Bill Jelen, and today's question comes from Jonathan. He wants to know how to create a dynamic chart that changes based on a drop-down selection. Well, I have an interesting solution for you, Jonathan, so let's dive in.
First, I created all the different charts that I wanted to show and pasted them onto a separate sheet, out of sight. Each chart was placed in a specific location, with the first chart in cell B30 and the next chart in cell B50, and so on. It's important that all the charts are on the same sheet, but they don't have to be on the same sheet as the drop-down.
Next, I set up the drop-down list using Data Validation and the List setting. I created a list of valid values and next to it, I put the number of rows from row 30 where the chart starts. Then, using a VLOOKUP function, I linked the drop-down selection to the number of rows to offset. This value is stored in cell Y2.
Now, here comes the tricky part. We're going to use the OFFSET function in a named range, which I've named "MyChart". This function will dynamically update the chart based on the drop-down selection. Make sure to start the formula with an equal sign and to include the sheet name and exclamation point before the cell references. The OFFSET function will use the number of rows from the VLOOKUP to determine which chart to display.
To actually create the chart, we'll use the Camera tool to take a picture of the chart and paste it as a linked picture. This will allow the chart to update dynamically based on the drop-down selection. And there you have it, a dynamic chart that changes based on a drop-down selection.
Now, I have to admit, as I was creating this solution, I realized that if your data happens to be from a Pivot table, it would be much easier to use the drop-down from the Pivot table. However, if your data is not from a Pivot table, this solution is a great alternative. You can also use this method with Pivot tables, but it requires a bit more work.
I want to thank Jonathan for sending in this question and I hope this solution helps you and others who are looking for a dynamic charting solution. Don't forget to check out our sponsor, Easy-XL, for even more Excel tips and tricks. Thanks for watching and we'll see you next time for another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
#excel
#microsoft
#exceltutorial
#exceltips
#microsoftexcel
#exceltricks
#walkthrough
#charts
#excelcharts
#excelchartstutorial
#excelvba
This video answers these common search terms:
how to add dynamic chart title excel
how to make dynamic chart excel
how to make a dynamic chart in excel
how to create a dynamic line chart excel
how to create a dynamic chart with multiple drop down criteria in excel
how to create a dynamic chart excel
how to create a dynamic pie chart in excel
how to make excel chart dynamic
how to make interactive charts in excel
how to create interactive graphs in excel
how to make an interactive graph in excel
how to make an interactive bar chart in excel
how to make an interactive pie chart in excel
how to create an interactive pie chart excel
how to make date range in excel chart interactive
Table of Contents
(0:00) Drop-down on sheet to change chart
(0:20) Problem Statement: Interactive charts in Excel
(0:45) Set up charts every 20 rows
(1:10) Set up Data Validation list and lookup table
(1:35) VLOOKUP formula
(1:54) Using OFFSET in a named range in Excel
(3:00) Copy first chart Paste Linked Picture
(4:03) Change formula inside linked picture to named range
(4:56) Method 2: Use a pivot table
(5:41) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
About MrExcel: Bill Jelen is an Excel MVP and the author of 70+ books on Excel. He has trained thousands of professionals worldwide.