Creating Interactive Charts in Excel Using Dropdown Lists
Charts are very central when it comes to communicating information. Whether you’re doing it in MS Excel, Power BI or Tableau, the principles are the same. Interactive charts are a common feature in all business intelligence tools. Excel offers an analyst numerous options for making dynamic and interactive charts that inspire action. We shall look at one of these options – creating interactive charts in Excel using dropdown lists.
We have summarized our sales for 4 outlets as shown in the screenshot.
Click here to download the Excel file that goes with this article.
We want to present the trend for each outlet at a go.
To do this we need to:
- be able to select which outlet to show -this is where a drop-down list applies
- return a subset of data for the 12 months for the selected outlet
- plot the chart based on this subset of data
If you prefer to watch the video here it is:
We therefore prepare a location as shown below to host our selected outlet and corresponding data.
Creating a drop-down list in Excel
Follow these steps:
- Ensure you have selected C12 as the active cell
- Go to Data menu >>>Data Validation
- From the pop-up window, choose “List” from the “Allow” box. This will open a box labelled “Source:” where you can specify the source for your data
- Select the list of outlets in cells C4:C7 as shown below and press OK.
- Select “Two Rivers” from the list now created in cell C12
Generating data for the interactive chart
Now that we know which outlet, the next step is to use a formula to extract the data to be plotted.
In this example, we shall use VLOOKUP and combine it with the MATCH function to dynamically generate the column numbers required by the VLOOKUP.
We enter the formula as follows:
When we auto-fill the formula shown above to the columns on the right, you can see we get a constant value since we have hard-coded the column index number (2 in the formula), and hence the values are always the second column from the table array.
To make this formula dynamic, we shall replace the 2 with this formula:
=MATCH (D$11, $C$3:$O$3, 0)
This formula will give us the column numbers for subsequent months.
Our dynamic formula will therefore be:
Please be keen on the use of the dollar sign. The formula needs to be locked/referenced properly.
Creating the interactive chart
We have the data now being updated dynamically as one selects a given outlet from the drop down we created above.
- Select range of cells from C11:O12 (see screenshot above)
- From Insert menu >>>Charts >>>Line with Markers. You should get the following chart
- Format and customize the chart to your preferred look and feel
Other options to create interactive charts in Excel
The premise of dynamic or interactive charts in Excel is to make sure the data from which the chart is plotted is also dynamic.
In the example we have looked at, we have used Excel’s drop-down lists to achieve this.
You could also leverage on the following for interactivity:
- Slicers combined with Excel tables or Pivot tables
- Combo Box form controls from the developer menu
Let me know in the comments below what other ways you create dynamic charts in Excel.
Over to you!
Please share this article in your social networks or email to a friend.
See you soon.