How to Create Interactive Excel Charts With the INDEX Formula
Pinterest Stumbleupon Whatsapp
Advertisement

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 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions matter. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know about making your charts attractive and engaging in Excel 2016. Read More , 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:

excel raw data

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.

excel title row

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.

excel placeholder

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:

=INDEX($B$10:$I$13,$C$18,0)

In case you’re not too familiar with the INDEX function Search Excel Spreadsheets Faster: Replace VLOOKUP With INDEX and MATCH Search Excel Spreadsheets Faster: Replace VLOOKUP With INDEX and MATCH Still using VLOOKUP to search for information in your spreadsheet? Here's how INDEX and MATCH can provide a better solution. Read More , 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:

excel working formula

Grab the corner of the cell containing your formula and stretch it out across the whole row.

excel data population

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.

excel c3po data

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 How to Create a Pie Chart in Microsoft Excel How to Create a Pie Chart in Microsoft Excel Everyone can create a simple pie chart. But can you format it to perfection? We'll take you through the process, one step at a time. Read More .

To do so, head to Insert > Recommended Charts in the Ribbon.

recommended charts

Feel free to use whatever type of chart 8 Types of Excel Charts & When You Should Use Them 8 Types of Excel Charts & When You Should Use Them Graphics are easier to grasp than text and numbers. Charts are a great way to visualize numbers. We show you how to create charts in Microsoft Excel and when to best use what kind. Read More 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.

excel changing graphs

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).

excel combo box

Create your drop-down menu wherever you want it to be on your spreadsheet. I’m putting mine just below my chart.

excel chart w dropdown

Right-click the object and select Form Control. Select the names of your datasets in the Input range field.

excel input range

The Cell link field needs to match the cell with the number that dictates which dataset is selected.

excel cell link

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.

excel dropdown works

Now would be a good time to make any aesthetic edits to your spreadsheet. You might want to tweak your chart’s appearance How to Create Powerful Graphs & Charts in Microsoft Excel How to Create Powerful Graphs & Charts in Microsoft Excel A good graph can make the difference between getting your point across or leaving everyone dozing off. We show you how to create powerful graphs in Microsoft Excel that will engage and inform your audience. Read More , 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.

excel 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.

excel neat and tidy

Focus Is Important!

When you’re putting together an Excel dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More , 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 6 New Excel Charts and How to Use Them 6 New Excel Charts and How to Use Them Need to get more out of your Excel charts? Here's a one-stop guide to some of the new charts introduced in the latest version of Excel. Read More !

Image Credit: Risto0 via Shutterstock.com

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. em
    November 9, 2017 at 4:01 am

    Very helpful thanks! I'm wondering the same thing as Matt.

  2. Matt Walsh
    July 21, 2017 at 1:49 am

    Very cool! Is there any way to build the drop down menu and dynamic chart into a PowerPoint presentation?