When you have more than one series in your Excel chart data, you typically need to make these series appear in the chart for comparison. Sometimes, however, you may not need to have each series appear in the diagram at the same time, you can add a drop-down list to select a series, and the series is displayed separately in the chart for viewing.
If the following table is for a company's annual sales data, which is located in the A1:d13 area, you need to select a product family through the Drop-down list, so that the selected series is displayed separately in the chart.
Add and set combo box controls on a worksheet
1. You can add a drop-down list to a worksheet in a variety of ways, such as through data validation, adding ActiveX controls, adding form controls, and more. For example, to add a form control, select the Developer tab in the Ribbon, and in the Controls group, click insert → form control → combo box (form control).
2, if the Developer tab does not appear in the Ribbon, you can set it in the Options dialog box to display it, referring to other articles of this site.
3, when the mouse pointer into a fine cross, hold down the left mouse button in the worksheet drag, draw a combo box control. Because when you click the control, you need to display the product A, in the Drop-down list of the combo box. Product B Series names to choose from, so you need to enter or paste the series names into a column before you set them, in this case, paste the series names in the B1:d1 area into the I1:i3 area by pasting and transpose.
4. Right-click the combo box control and choose Set Control Format in the shortcut menu that pops up. pops up the Format Object dialog box, selects the Control tab, sets the data source range to $I $ $I $, and sets cell link to J1 cell so that when you select a series name in the combo box , the corresponding number is displayed in the J1 cell.
Add secondary columns and set formulas
Columns F and g are secondary columns. Copy the month data from the A1:a13 area to column F and enter the formula in cell G1:
=offset ($A $1,row (A1)-1, $J $)
Then drag the fill handle, fill the formula to cell G13, select a series in the combo box, such as product A, and the G column displays the series's data, as shown in the figure.
Select a cell in the F1:g13 area and select insert → line chart → line chart in the Ribbon to insert the chart. When you select a series in a combo box, the chart displays the series.