How to make sliding control linkage Excel data chart

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.