This simple process will help you turn static Excel charts into something much more dynamic.
Excel makes it easy to create clear, concise charts from your raw data. However, the results can sometimes feel static and uninteresting. One way to address this problem is to add some design flourishes, but you can also introduce an interactive component.
A simple drop-down menu can transform a simple Excel chart into an illustrative visual that’s capable of presenting several different sets of information. It’s a great addition to any Excel dashboard, and it’s very easy and quick to set up.
Here’s how to use the INDEX formula and a basic drop-down menu to make your Excel charts a bit more interactive.
Setting Up the Data
First things first, we need to assemble our data. I’m going to produce a chart that compares the screen time of various characters in the Star Wars saga, so mine looks like this:
You just need to follow a similar structure. Stack your data in rows with no space left in between. When our chart is complete, we’ll be able to switch between C-3PO and Darth Vader, and our chart will update with the correct data.
Next, copy and paste your title row beneath your data.
Now we need to jump three rows down and enter the word “Dataset” into one cell, and a placeholder number into the cell to its right. For now, they’re placeholders, but soon they’ll underpin our drop-down menu.
With all that in place, it’s time to link together these elements using an INDEX function. Enter the following formula two cells above Dataset:
In case you’re not too familiar with the INDEX function, we’ll break that down. The range $B$10:$I$13 refers to the full set of data that we want the formula to have access to. $C$18 refers to the cell that determines which data to display (the number that we placed next to the cell labeled Dataset.) An array typically ends with a column reference, but we specified the exact cell, so we’re just using a 0.
The specifics of your formula will change depending on your data, so here’s a look at how the various components correspond with each other to help you tailor your own work:
Grab the corner of the cell containing your formula and stretch it out across the whole row.
The correct data should populate the cells, as seen above. Now, you can manually change the number in the cell to the right of Dataset to utilize the INDEX formula. For instance, inputting a 1 into the cell would present the data associated with C-3PO.
Creating a Chart
Now comes the easy bit. We’re going to select the data that’s offered up by our INDEX formula — NOT the data that we entered manually — and turn it into a chart.
To do so, head to Insert > Recommended Charts in the Ribbon.
Feel free to use whatever type of chart suits your data best. I’m going to use a bar chart because it seems appropriate.
Once your chart is ready, test that everything is in working order by changing the number next to Dataset. Hopefully, the chart will change according to the dataset you select.
Adding a Drop-Down Menu
Now, we’re going to make our dynamic chart a little bit more user-friendly. Head to the Developer tab, then use the Insert drop-down in the Controls menu to select Combo Box (Form Control).
Create your drop-down menu wherever you want it to be on your spreadsheet. I’m putting mine just below my chart.
Right-click the object and select Form Control. Select the names of your datasets in the Input range field.
The Cell link field needs to match the cell with the number that dictates which dataset is selected.
Click OK and test out your drop-down menu. You should be able to select your dataset by name and see the chart update automatically.
Now would be a good time to make any aesthetic edits to your spreadsheet. You might want to tweak your chart’s appearance, or you might want to clean up some of your data to make the document a bit neater. By adjusting the references in your formulae, you can present the charts on one sheet, and keep your data in the background.
To do this, copy and paste your chart and your dropdown menu onto a new sheet — don’t worry if the data depopulates momentarily. Right-click the dropdown and select Format Control.
All we need to do is to add Sheet1! in front of the cells we previously selected. This will tell Excel to look for the cells on a different sheet to the one where the chart and drop-down menu are located. Click OK, and we should have a working dynamic chart on a neat and tidy sheet.
Focus Is Important!
When you’re putting together an Excel dashboard, it can be tempting to overload your audience with information. After all, the idea of this kind of resource is to collect lots of data in one place.
However, this can end up being counter-productive. With too much content fighting for their attention, the person looking at the dashboard might not take in all the information that’s being offered up.
That’s where a dynamic chart like this can come in useful. Allowing your audience to switch between different data sets at their own convenience will help them hone in on the information at hand.
Of course, there are times when it might make more sense to have two charts next to one another, to allow for an easy comparison. However, the technique explained in this article is great when you want to make a dashboard that covers several different topics, and you need to make the best possible use of space.
Do you have a tip for users looking to perfect their Excel charts? Or do you need help with the process explained in this guide? Either way, why not join the conversation in the comments section below?
Before you go, check out these new Excel charts!
Image Credit: Risto0 via Shutterstock.com