Updated: Sep 15
It always makes quite an impression to link a chart to a drop-down list in an Excel spreadsheet.
Imagine you are building a dashboard for reporting on your company's products sales performance. Wouldn't be great to just pick a product in a drop-down list and see the data for this product only being displayed on a chart?
But there is more to it than just showing off with a fancy workbook. Such a "drop-down chart" also enables you to use a single chart to display data from multiple series of data. This makes your spreadsheet lighter and also more efficient for the users. You can, for instance, send it to your Product Managers and each of them will be able to see the reporting for their respective product without having to search for one chart among many.
So how can you create such a chart linked to a drop-down list?
The basica idea to create such dynamic chart is actally not to link the drop-down list to the chart itself, but to its data source.
So let's get started. It's actually pretty easy.
1) First start with a set of data.
In this example the data is organized in columns so that we can create an Excel Table (see the benefits of using Tables here). But it could as well be organized in rows. Each column here contains the data for a different product.
2) Add a new column to your data set.
Let's call it "Chart Data source" here to make things crystal clear.
3) Choose a cell and create the drop-down list.
In our example, we take cell "J2" and create a drop-down list using the columns titles (i.e. product names) as source. If you are unfamiliar with creating drop-down lists in Excel, this post will tell you all there is to know.
4) In the new column, type the following formula:
=OFFSET([@Date];;MATCH($J$2;Table2[[#Headers];[Product A]:[Product C]];0))
or if you are not a fan of Tables, you will type instead:
4) Now just create a chart that takes the new column as data source.
That's it. Now you can play with your drop-down list in J2 and see how the chart updates.