Use a drop-down list in Excel2010 to control the display of different series of charts

Source: Internet
Author: User

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.

Insert Chart

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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.