Tue. May 21st, 2024


If you want to impress your friends and colleagues, you can make an Excel chart change based on a drop-down box you have added to your sheet. There are different ways you can achieve this, but we prefer the following method because it’s easier to locate any issues if something goes wrong.



We’re going to use a table of data containing five soccer players’ names and their game rating for five games as we talk you through the process.


Step 1: Create a Drop-down List

The first step is to create the drop-down list that will make your chart dynamic. Start by typing an action word in the cell next to where your drop-down will go. In our example, we’ll put the drop-down in cell B9, so we’ll type our action word in cell A9. Then, click the cell where your drop-down will go, head to the Data tab on the ribbon, and click “Data Validation” in the Data Tools group.

An Excel worksheet. 'Choose' is typed into cell A9, cell B9 is selected, and the 'Data Validation' function is highlighted.


In the Data Validation window, choose “List” under Allow. Then, click in the “Source” field box and highlight the column headings in your table (the data that will be shown in the drop-down list you’re creating). In our example, we want to choose the game numbers, as in the chart we are going to create later, we want to see the players’ ratings for each game. Then, click “OK.”

An Excel spreadsheet with the data validation window open. The Allow field contains the "List" option, and the Source field contains the column headers from the table.

You will now see a drop-down list showing the data you selected when you click the arrow. We’ve also formatted our Choose cell to make it stand out.

An Excel spreadsheet containing a table and a drop-down list with the options showing as the column headers in the table.


Step 2: Create a Data Retrieval Table

We now need to create a second table that is separate from our raw data but pulls information from it when we adjust the drop-down menu we have created. The chart we create later on will be created using the information in this data retrieval table.

From this point, we’ll call the main data table
Table 1
, and the data retrieval table
Table 2
.

Highlight the row titles in Table 1, press Ctrl+C, and use Ctrl+V to copy the list where you want Table 2 to be.

An Excel sheet with a table on the left, and the first column copied and pasted to the right.

Select and right-click the pasted data, hover over “Sort,” and click “Sort A To Z.”


An Excel spreadsheet with data in Table 2 selected, and the Sort A to Z option highlighted.

To continue creating Table 2, you need to give the next column a heading. The data that appears in Table 2 will be dictated by what you select in your drop-down box, so in the cell where you want the heading to go, type = and click the drop-down cell. In our case, we want our heading in cell I2, and in that cell, we will type = and click cell B9.

An Excel spreadsheet with a column in Table 2 referencing a drop-down cell.

Press Enter to see the column heading in Table 2 adopt the drop-down selection. Try changing the drop-down selection to see the column heading in Table 2 change.


We now need to extract the relevant data from Table 1 to Table 2 using INDEX and MATCH. This is the most complex part of the process, but let’s break it down so that you can work through the process more easily. Here’s the synax:

=INDEX(a,MATCH(b,c,0),MATCH(d,e,0))

where

  • a is all the data in Table 1 (excluding the row and column headings),
  • b is the first value we’re looking up in Table 2,
  • c is the range of values in the first column in Table 1,
  • d is the column heading in Table 2, and
  • e represents the data column headings in Table 1.

Make sure cell references
a
,
c
,
d
, and
e
are absolute references by pressing F4 on your keyboard after selecting each reference. Otherwise, your formula will not work when you complete the rest of the data in Table 2.

Let’s see this in action.

In cell I3, we want to tell Excel to look in Table 1 and pull a value to Table 2 through the INDEX function.

=INDEX


We now need to open the parenthesis and tell Excel where the data is in Table 1. To do this, highlight all the data in Table 1, but not the column and row headings. Press F4 to make this an absolute reference, and add a comma.

=INDEX($B$3:$F$7,

The next step is to tell Excel to match the data in Table 2 with what we have in Table 1. Type MATCH, open a new parenthesis, and click the first entry in the first column of Table 2. In our case, that’s Davies in cell H3. Add a comma.

=INDEX($B$3:$F$7,MATCH(H3,

Next, Excel needs to know what it will use as its reference to look up in Table 1. In our case, it’s the data from cell A3 to cell A7 (the names of the players), so highlight this range, press F4, and then add a comma. Then, type 0 (zero) to tell Excel that we’re looking for an exact match, and close the parentheses. Add another comma.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),

Now, we must repeat the MATCH process for the data that will change when we select a different option in the drop-down. In our case, that’s the game number, so after typing MATCH into our formula for a second time, we will click I2, press F4, and add a comma.


=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,

And again, we need to tell Excel where to find that data in Table 1. In our example, that’s cells B2 to F4. Don’t forget to press F4 after you’ve referenced these cells. Then, add a comma, a 0, and close the two parentheses together. Finally, press Enter.

=INDEX($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,$B$2:$F$2,0))

Now, click and drag the handle in the bottom-right corner of the cell where you’ve just typed your formula to automatically fill out the rest of the data in Table 2.

A table in Excel being AutoFilled.

Try changing your drop-down option to a different value and see the data change in Table 2, and check that it aligns with the information in Table 1. Remember that we sorted the first column in Table 2 alphabetically, so check carefully, as the first column in Table 1 will be in a different order!


An Excel sheet with a drop-down selection changed to 'Game 3,' and Table 2 showing the data for this selection.

Now that Table 2 successfully changes depending on our drop-down selection, we’re ready to create the chart.

Step 4: Insert and Format Your Chart

Highlight all the data in Table 2 (including the column and row headers), open the “Insert” tab, and choose a chart that works for you.

An Excel spreadsheet with Table 2 selected and the charts highlighted in the Insert tab.

We’ve gone with a simple 2D column chart.

An Excel chart with the data chosen by a drop-down box, which uses Table 2 to retrieve data from Table 1.


Format the chart to appear as you wish, and again, play with your drop-down to see Table 2—and, consequently, your chart—adjust to your selection. You’ll also see your chart title change.

Step 5: Add an Average Line to Your Chart

To add an average line to the chart, you need to add more data to Table 2. Add another column heading to Table 2, and call it Average.

An Excel spreadsheet with two tables and a chart. Table 2 has a new column, headed 'Average.'

Now, use the AVERAGE function to capture the average of the selected data. In our case, we’ve selected game 4, so we want to find the average rating among all five players for this game. To do this, type

​​​​​​​=AVERAGE

​​​​​​​in the first cell of this new column, and then select the data in the previous column. Press F4 to make this an absolute reference, and then close the parentheses and press Enter.


An Excel table with the average of the first data column being calculated in the second data column.

Then, use the AutoFill handle to click and drag the formula down to the bottom of the column.

We now want to add the average data to our chart.

Click anywhere towards the edge of your chart and click “Chart Design” on the ribbon. Then, head to the Data group and click “Select Data.”

An Excel chart selected with the 'Select Data' icon in the Chart Design group selected.

In the Select Data Source window, click “Add.”

Excel's Select Data Source window with 'Add' highlighted.


Then, in the Edit Series window, clear the Series Values field, and then select the data in the Average column of Table 2. Click “OK,” and you will see the average appear as an additional bar in your chart.

An Excel sheet showing the Edit Series dialog box, the data in 'Series Values' is taken from Table 2, and the chart shows the average value as a column.

To change this average data to a line over your existing bars, right-click one of the new bars and click “Change Series Chart Type.”

An Excel chart with an average column selected through a right-click, and 'Change Series Chart Type' selected.

Open the “All Charts” tab, click “Combo” in the menu on the left, and change the average data series to “Line.”


The Change Chart Type window in Excel, with the 'All Charts' tab opened, the 'Combo' menu selected, and Series 2 changed to 'Line.'

Click OK to see the outcome, and change the drop-down choice to see your chart change dynamically!

An Excel chart reflecting the drop-down choice and with an average line included as an additional data set.

You can then format your line so that it stands out in the way that you want it to.


Now that you’ve achieved that impressive, dynamic chart, check out some ways to make it stand out even more.



Source link

By John P.

Leave a Reply

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