How to make sliding control linkage Excel data chart

Source: Internet
Author: User

to use Excel chart to show the leadership of two new research products annual sales of the growing performance, if the annual sales statistics for the whole year December, can not give the leadership too many surprises; but if you show the January data first, with the scroll bar to the right, the monthly comparison chart will show up in front of you, Will certainly bring more excitement to the leader (Figure 1). Now, let's implement the above ideas in Excel 2016.

1. Prepare chart data

Chart making, data advance. Open the sheet named "New Product Sales Statistics", click Name Manager in the Formula tab, and create a new name, "CpA", citing the location "=offset" (New product sales statistics!). $B $2,0,0,1, new product sales statistics! $B $) "; new name" CpB ", where reference is" =offset (new product sales statistics! $B $3,0,0,1, new product sales statistics! $B $) "(Figure 2).

Small tip:

B8 is a temporary storage location for future scroll bar values.

2. Rolling Bar skillfully set

Select the Development Tools tab, click the Insert button, select form control → scroll bar, and draw a scroll bar on the worksheet. Right-click the scroll bar, select Format Control, and set the current value to 1 in the Control tab, the minimum value is 1, the maximum is 12, the step is 1, the page step is 1, and the cell link is B8 (Figure 3).

3. Apply Chart Data

Once the data is ready, it should be applied to the chart. First, select a blank cell outside the data region, then hold down the CTRL key to select A2:B2, a3:b3 cells, and then click "Recommended Charts" in the "Insert" tab, click "All charts → combinations" in the pop-up window, and select "Clustered column chart-line chart", where product B chart type selection "Line chart with data markers." When the chart is added, right click on the chart, click "Select Data", at the legend item on the left side of the pop-up window, select Product A, product B, click "Edit", and enter "= new product sales Statistics!CPA", "= new product sales Statistics!CPB" in the pop-up window's series value. Click on the right horizontal category axis tab Edit, select B1:M1 (Figure 4).

Finally, set the chart title. Right-click the chart, select Set chart area format, decorate and beautify the chart, drag the scroll bar to the appropriate position to align with the chart, resize it, and combine the scroll bar and chart.

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: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.