This simple process will help you turn static Excel charts into something much more dynamic.
Excel makes it easy to create clear and concise charts from your raw data. However, the results can sometimes appear static and uninteresting. One way to tackle this problem is to add some design elements. 9 Tips for Formatting an Excel Chart in Microsoft Office. 9 Tips for Formatting an Excel Chart in Microsoft Office. First impressions are important. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know to make your charts cool and engaging in Excel 2016. Read More
A simple dropdown menu can transform a simple Excel chart into an illustrative visual that is capable of presenting several different sets of information. It's a great addition to any Excel dashboard, and it's very quick and easy to set up.
Here's how to use the INDEX formula and a basic dropdown menu to make your Excel charts a little more interactive.
First things first, we need to gather our data. I'm going to produce a graph 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 between them. When our table is complete, we can switch between C-3PO and Darth Vader, and our table will update with the correct data.
Next, copy and paste your title row below your data.
Now we need to jump three rows down and enter the word “Dataset” in one cell, and a placeholder number in the cell to its right. For now, they are placeholders, but they will support our dropdown menu soon.
With all of this in place, it's time to link these elements using an INDEX function. Enter the following formula two cells above Dataset :
= ÍNDICE ($ B $ 10: $ I $ 13, $ C $ 18,0)In case you are not very 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 for searching? information in your spreadsheet? Here is how INDEX and MATCH can provide a better solution. Read More The range $B$10:$I$13 refers to the entire set of data that we want the formula to have access to. $C$18 refers to the cell that determines what data to display (the number we put next to the cell labeled Dataset .) An array normally ends with a column reference, but we specify the exact cell, so we're just using a 0 .
The specifics of your formula will change depending on your data, so here's how the different components correspond to each other to help you tailor your own work:
Grab the corner of the cell that contains your formula and stretch it across the entire row.
The correct data should fill the cells, as seen above. Now, you can manually change the number in the cell to the right of Dataset to use the INDEX formula. For example, entering a 1 in the cell would present the data associated with C-3PO.
Now comes the easiest part. We'll select the data that our INDEX formula provides, 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 Anyone can create a simple pie chart. But can you format it perfectly? We will take you through the process, step by step. Read more.
To do so, go to Insert> Recommended graphics on the tape.
Feel free to use any type of table. 8 Types of Excel Tables and Charts and When to Use Them 8 Types of Excel Tables and Charts and When to Use Them Charts are easier to understand 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 which type. Read More I'm going to use a bar chart because it seems appropriate.
Once your graph is ready, test that everything works correctly by changing the number next to Dataset . Hopefully the chart will change based on the data set you select.
Now, let's make our pivot chart a little easier to use. Go to the Developer tab, then use the Insert dropdown in the Controls menu to select Combo box (form control) .
Create your dropdown menu where you want it to be in your spreadsheet. I'm putting mine right below my chart.
Right click on the object and select Shape Control . Select the names of your datasets in the Input Range field.
the Cell link the field must match the cell with the number that dictates which data set is selected.
Click OK and test your dropdown. You should be able to select your dataset by name and see the chart update automatically.
Now would be a good time to make cosmetic edits to your spreadsheet. How to Create Powerful Charts and Graphs in Microsoft Excel How to Create Powerful Charts and Graphs in Microsoft Excel A good chart can make the difference between getting your point across or putting everyone to sleep. We show you how to create powerful charts in Microsoft Excel that engage and inform your audience. Read More By adjusting the references in your formulas, you can present the charts on a sheet and keep your data in the background.
To do this, copy and paste your chart and dropdown into a new sheet, don't worry if the data becomes momentarily unpopulated. Right click on the drop down menu and select Format Control .
All we need to do is add Sheet1! In front of the cells we have previously selected. This will tell Excel to look for the cells on a different sheet than the one the box and dropdown are on. Click OK , and we should have a working pivot chart in a clean and tidy sheet.
When you're building an Excel dashboard, visualize your data and make your spreadsheets easy to use with an Excel Dashboard Visualize your data and make your spreadsheets easy to use with an Excel Dashboard Sometimes a simple spreadsheet format is not attractive enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More After all, the idea of this type of resource is to collect a lot of data in one place.
However, this can be counterproductive. With too much content fighting for your attention, the person looking at the dashboard can't take in all the information on offer.
That's where a pivot chart like this can come in handy. Allowing your audience to switch between different data sets at their own convenience will help them refine the information available.
Of course, there are times when it might make more sense to have two charts side by side, to allow for easy comparison. However, the technique explained in this article is great when you want to create a dashboard that covers several different topics and you need to make the best possible use of space.
Do you have any tips 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 complete guide to some of the new charts introduced in the latest version of Excel. Read more !