Another way we can help our Excel dashboard users is to give them the ability to select between two or more different sets of data or trend lines. We can do that with a drop down list control. The drop down gives the user the choice of choosing a straight-line trend or a growth trend.
If you’d like to follow along or experiment with this Excel dashboard tutorial, then download this workbook. Dynamic Trend Charts – Advanced
The TREND array function creates the straight line trend in in D31:O31. The GROWTH array function creates the exponential trend in D30:O30. An INDEX function controlled by the drop down list is used to select which of these two trend lines appear in the range D24:O24.
Enter the TREND array function in D31:O31 and the GROWTH array function in D30:O30 as described in the article Using the TREND function to Create Trend Lines on Excel Charts or Dashboards.
Enter the titles Exponential in C30 and Linear in C31. These titles will be used to fill the drop down list.
The drop down control is selected from the Developer tab, Control group, Insert Form Controls shown in the figure below. Click on the Combo Box (I know I call it a drop-down), then drag across the area on the worksheet where you want the drop down control.
If you do not have a Developer tab on your ribbon this article will show you how to add it.
Once you have the drop-down control on your worksheet you will need to modify it. Right-click on the drop-down control and select Format Control. In the Format Control box you can select the input range for your list, the titles in C30:C31, the cell link where the selection will appear, D34, and how many drop down lines you want, 2. Then click Ok. Take a look at the figure at the beginning of this article to see where these are.
Now when you select the first choice, Exponential, from the drop down list, the number 1 should appear in D34. Select Linear, the second choice and 2 should appear in D34.
Now what you need is a formula that will use the number in D34 to choose between the two rows of trend data. The row you choose will be the row that gets plotted in the chart as the trend line.
1. Enter Trend in cell C24. This is the title for the line of trend line data.
2. In cell D24 enter the formula
This formula selects between the cells in the range D30:D31 depending upon the value in $D$34. If D34 is 1, then D30 is chosen. If D34 is 2, then D31 is chosen.
3. Copy the formula in D24 across the range D24:O24.
4. Copy and paste range D24:O24 into the chart to make the trend line appear.
Now for the fun part. If you have never done this before it’s a little exciting to see something like this work. And you created it.
When you select between the two choices in the drop down list the data in D24:O24 changes. That changes which trend shows in the chart.
You can download the Excel workbook containing these dynamic trend line charts here. Dynamic Trend Charts – Advanced
Here’s two experiments you should try in this worksheet that will be very useful for you,
- Use the INDEX function and the value in D34 to change the title that appears in C24.
- Create multiple rows of data that could appear in C23:O23 and make a drop down list and INDEX formula that enables the user to select between data sets.
Learn how to extend a TREND line to create a forecast.
Learn how to show or hide a TREND line under the check box control.
Learn how to edit a TREND or array function.