Download the eBook
Table of Contents
Switching Charts with A Drop-Down List 3
1. Creating a data preparation table 3
Creating a drop list for the different months 3
Creating row labels for the 2 preparation tables 4
Method 1: INDEX and MATCH Functions 5
Formatting the Column Chart 10
Adding Data Labels to the columns 12
Formatting the Value and Category Axes: 13
Creating a Dynamic Chart Title: 14
3. Moving the Chart to the Dashboard 15
Create the drop list on the “Dashboard” sheet (“Chart”) 15
Linking the new drop list to the copied chart 17
4. Bringing the drop list inside the Chart title. 19
Switching Charts with A Drop-Down List
A chart is a graphic representation of numeric values and allows us to get insights about our data at a glance.
If you are creating multiple charts to look at the underlying data from different perspectives, you may run short of space. May be also you want to squeeze multiple charts as part of a Dashboards so, you need a better use of the real estate of your Dashboard. In these situations, switching charts using a Drop-Down List will be an optimal solution.
Download the Exercise File
In our example we have a list that shows the number of cars sold for each car type, for each month in the year 2019.
We would like to be able to compare the sales of different car types for any specific month by looking at a chart.
Creating a data preparation table
The purpose of the preparation table is to extract the data corresponding to any specific month from the source list. Switching the month from a drop list refreshes the extracted values. This data preparation table can be created either using classic functions or using dynamic array functions. We’ll explore both options.
Creating a drop list for the different months
Because I intend to show you both options, I will start by creating 2 identical drop-down lists. Select Cell E10, press CTRL and click on cell I10. With both cells selected, click on the Data Tab of the Ribbon ►then click on Data Validation.
Alternatively, you can use the shortcut ALT, D, L
The Data Validation Dialog box opens. Click on the down pointing arrow for “Allow” and select “List”. Put the blinking cursor in the “Source” box and then click and drag to select the different month in the source list header row B2:M2 ► Then Hit “OK”.
Test by making a selection from each drop list, say “February”.
Note that in the video Tutorial I used columns G & H for the second data preparation table, so mind the difference in references from this article.
Creating row labels for the 2 preparation tables
Select and copy the labels from the source list from A3 to A7 ►Then paste twice: in D11 and in H11
Method 1: INDEX and MATCH Functions
Select cell E11 and let’s create an INDEX function that returns the values for the selected month in E10 (currently February). The INDEX function has 3 arguments:
- Array: These are all the values in the source list from B3 to M7. But, because we intend to copy down for the other car types, we must lock this range by hitting F4 key ► it becomes an absolute range.
=INDEX(array,row_num,column_num)
Type a comma then,
- Row number (for the INDEX function): returns the row number corresponding to each car type (its ordinal position in the source list. We use a MATCH function for this argument.
The Match function has 3 arguments:
=MATCH(lookup_value,lookup_array,match_type)
The Lookup Value is cell D11
The Lookup array is the range where we look for the matching value ► the range from A3 to A7 ► and we lock it by hitting F4 key since we’ll be using the same range all the way down.
The Match type is 0 as we need an exact match. Close the bracket for the MATCH function.
Type a comma then,
- Column number (for the INDEX function): returns the Column number corresponding to the selected month (its ordinal position in the source list. We use another MATCH function for this argument.
As we mentioned, the Match function has 3 arguments:
=MATCH(lookup_value,lookup_array,match_type)
The Lookup Value is cell E10 (our drop list)
The Lookup array is the range where we look for the matching value ► the range from B2 to M2 ► and we lock it by hitting F4 key since we’ll be using the same range all the way down.
The Match type is 0 as we need an exact match. Close the bracket for the MATCH function.
Close the bracket for the INDEX function.
Our function should look like this:
- Hit Enter ► then hover over the lower right corner of cell E11 and drag to copy down to cell E15.
Method 2: XLOOKUP function
The XLOOKUP function is a Dynamic Array function available in Office 365 and was introduced on the 28th. Of August 2019. You create the function in one single cell and it spills to the adjacent range (assuming you have No obstacle). Accordingly, it does not require locking cell references as in classic functions.
We’ll be creating 2 nested XLOOKUP functions: the first one extracts a column of numbers corresponding to the selected month. While the second one extracts the specific number corresponding to the car type in the row header.
First XLOOKUP function: Select cell I11 and type: =XLOOKUP ► then hit TAB.
The XLOOKUP function has 6 arguments (only the first 3 are mandatory), and looks like this:
=XLOOKUP(lookup_value,lookup_array,return_array, if_not_found,match_mode,search_mode)
The Lookup_Value is the selected month in cell I10
The Lookup_array (where we look for this specific month) is the range from B2 to M2
The return_array (from which we need a return value) is the range from B3 to M7.
We do not need to lock any ranges in a Dynamic Array function unless we nest multiple XLOOKUP. Close the bracket ►then hit Enter ► The function spills down returning all the numbers for the selected month (February in our example).
Although the result looks correct, because the row labels (Car types) are in the same order as the source list, however, if you sort the row labels the Source the XLOOKUP result doesn’t change, and that’s why we need to use a second XLOOKUP function in which we’ll nest the first one as a third argument.
Note in case of nested XLOOKUP functions, we have to lock ranges as the function will not spill automatically.
Therefore, put the function in cell I11 in the edit mode by hitting F2 key and cut it to the clipboard by hitting CTRL + X (without the =equal sign) ► then delete the function from cell I11.
Second XLOOKUP function: Select cell I11 and type: =XLOOKUP ► then hit TAB.
The Lookup_Value is the Car type in cell H11
The Lookup_array (where we look for this Car type) is the range from A3 to A7 ► Lock by hitting F4 key.
The return_array (from which we need a return value) is the previous XLOOKUP function stored in the Office clipboard. ► Lock All arguments by hitting F4 key.
Close the bracket for the wrapping XLOOKUP function. It should look like this:
When you hit enter you get the correct result. Test by switching months from the drop list. If the order of labels (Car Types) in the source list and the Data preparation table is different, you always get the correct result.
We created the data preparation table in 2 different ways. Actually, I only need one. So, I’ll be using the first one.
Creating a Column Chart
Select a single cell in the first data preparation table. Click on the Insert Tab of the Ribbon and select Column chart from the Charts group.
Alternatively hit the shortcut ALT + F1
A column chart is created. Let’s improve its appearance.
Formatting the Column Chart
Remove the Horizontal Grid Lines: Click on one of the fine horizontal lines ► All of them are selected ► Hit Delete.
Reducing gaps between columns: Click on any one of the blue columns ► All of them are selected ► Hit the shortcut CTRL + 1 to open the Format Data Series pane ► drag the Gap width slider to the left
Change the color for each column: At the top of the Format Data Series pane there are 3 icons, click on the Fill & Line icon (the left most icon) ► Click on the right-pointing triangle of the Fill option to expand it ►Check the box for “Vary Color by point” ► Close the “Format Data Series” pane.
Embossing the columns: With the columns still selected, click on the Format Tab of the Ribbon ► Click on Shape Effects ► Click on the right pointing arrow for “Bevel” ► Select the leftmost option in the second row (Round)
Note that bevelling is different than a 3-D chart or a perspective which might not be a best practice.
Adding Data Labels to the columns
With the columns still selected, click on the green plus sign in the top right corner of the chart (Chart Element button) ► Click on the right pointing arrow for Data Labels ► and select Outside end.
The data labels are added to the chart. Click on any one of the labels ►All the labels are selected ► click on the Home Tab of the Ribbon ► Change the Font size to 10 pt and Bold them.
Formatting the Value and Category Axes:
Click on the Horizontal (Category Axis) ► click on the Home Tab of the Ribbon ► Change the Font size to 10 pt and Bold the categories.
Click on the Vertical (Value Axis) ► click on the Home Tab of the Ribbon ► Change the Font size to 10 pt and Bold the values.
Embossing the Chart:
Click on the outer border of the chart ► click on the Format Tab of the Ribbon ► Click on Shape Effects ► Click on the right pointing arrow for “Bevel” ► Select the leftmost option in the second row (Round)
Your chart is now formatted and should look like this:
Creating a Dynamic Chart Title:
We need to prepare for creating a dynamic title, by selecting any cell in the worksheet (Say D17) and let’s build the title by joining some text to the month coming from the drop list using the joining operator of Excel, the ampersand (Shift+ 7). So, in cell D17 type:
=”Car Sales For The Month Of “&E10
Note that text is in double quotes and there is a space before the closing quotation.
To add this dynamic title to the chart, click on the Chart Title to select it ► Hit the F2 key (important) ►You should see the blinking cursor automatically in the Formula bar ► Type an equal sign and click on D17 ► Hit Enter.
The dynamic title is created. Test it by selecting different options from the drop list, and now our chart is ready to be moved to the Dashboard, and should look like this:
Moving the Chart to the Dashboard
Select and copy the Chart (on the Source worksheet) CTRL + C then go to the “Chart” worksheet and Paste it.
Every time I need to select a different month, I have to go to the “Source” worksheet and change my selection from the drop list in cell E10, which is definitely not practical. So, I need to recreate the drop list on the dashboard sheet “Chart”.
Create the drop list on the “Dashboard” sheet (“Chart”)
Select cell E3 (on the “Chart” sheet) and create the Data Validation drop list using the months on the “Source” sheet, in the range B2:M2, as we did before.
Note that the name of the worksheet precedes the cell references (Two-dimensional cell reference), since we are selecting from a different sheet.
The Drop list is created but it does not change anything in the chart.
Linking the new drop list to the copied chart
To link it to the chart ► go to the “Source” worksheet ► Select Cell E10 ► On the right side of the Home Tab ►Click on “Clear” then “Clear All”
Note you can also use the Data Validation dialog box to do that by clicking on “Clear All”
Don’t worry if the Data preparation table and the chart are temporarily messed up. We’ll fix that in the next step.
With cell E10 still selected, type an equal sign ►Click on the “Chart” worksheet and in the formula bar type: E3
Now if you test by selecting a different month from the new drop list in cell E3, the chart updates.
Are you ready for the Magic?
Bringing the drop list inside the Chart title.
My drop list in cell E3 is fully synchronized with my chart, but I want to take it to the next level. My goal is to position the chart on top of the drop list and have the drop list work as normal from within the overlapping chart.
Move the chart and position it in a way that the down pointing arrow of the drop list appears to the right side of the chart title, as if it is part of the title. You cannot see the down pointing arrow of the drop list, unless cell E3 is selected ► I can select it by typing the cell reference in the name box followed by hitting enter.
So what we need to do is to automate the process of showing the down pointing arrow of the drop list without having to type in the name box every time.
We’ll do that in 2 steps:
- Naming Cell E3
- Recording a Macro
Naming cell E3:
Select cell E3 (by typing its reference in the name box followed by hitting Enter) then ► On the Formula Tab ►Click on Define Name
Alternatively, you can type the name directly in the name box or use the shortcut CTRL + ALT + F3
In the “New Name” dialog box, make sure that the “Refers to” box shows cell E3, give a name to the cell (I named mine “ChartDL”) ► then hit OK ► the name now appears in the name box.
Recording a Macro
We’ll be recording a very simple macro, that automates the task of selecting cell E3, and accordingly revealing the down pointing arrow of the drop list.
If you do not have the Developer Tab displayed on the ribbon, click on the File Tab ►Click Options ► Select Customize Ribbon ► Check the box for “Developer” in the right pane ► Hit OK
To the left side of the developer tab there is a group named “Code”.
Make Sure that “Use Relative Reference” is NOT highlighted.
Click to select any cell in the worksheet other than cell E3 (important)
Click on Record Macro.
In the Record Macro dialog box ► Name your macro (Say ”Switching”) ►Hit OK.
You are now in the process of recording (anything you do with your keyboard or mouse is being recorded). Click on the down pointing arrow of the name box and select “ChartDL” ► Click on Stop recording on the ribbon.
Note: You can skip the step for naming E3 by typing this cell reference directly in the name box while recording the macro.
Note I am using Office 365 for this tutorial. The “Stop Recording” button has blue square icon on the installed version of Office.
When this macro runs, it selects cell E3 and accordingly it displays the down arrow of the drop list.
The last step is to attach the macro to the chart. Right click on the Chart border ► Select “Assign Macro” ► The “Assign Macro” dialog box opens ► Select “Switching” ► Hit OK
From this moment, hovering with your mouse over the chart ► the mouse pointer appears as a pointing finger ► If you click, you trigger the macro ► the macro shows the drop list ► if you click on the drop list, now the drop list appears as an integrated functionality in the chart title… It looks magical
