How Excel makes a dynamic chart with controls

How Excel makes a dynamic chart with controls

First look at the effect:

5-day PM2.5 data is always displayed in the chart, and when you resize the scroll bar, the chart automatically updates the results.

The first is the data source, the PM2.5 data for a certain period of time.

Step One

Inserts a scroll bar control first.

Right-click the control to format the control.

Minimum selection 1

The maximum value can be determined by the data situation.

Cell links Select a blank cell, such as D6.

Step Two

Press CTRL+F3 to open the name manager and create a new two custom names:

Date

=offset (sheet1! $A $1,sheet1! $D $6,,5)

Data

=offset (sheet1! $B $1,sheet1! $D $6,,5)

The offset function's row offsets are specified by the D6 cell, and the D6 cell is controlled by the scroll bar control.

This will change the offset function's row offsets every time you click the scroll bar.

Step Three

Select some of the data, and then insert a line chart by following the steps below.

Right-click the chart, select the data, and modify the data source.

Modify the axis labels and data series to the name you just customized.

Finally, a simple beautification of the chart.

Adjusts the height of the chart and the height of the cell so that the chart has the same vertical scale and cell row height, and then sets a different background to the cell.

Insert a picture footage, press CTRL + C to copy, click the chart data point, and press CTRL + V to paste.

Right-click the data series, add data labels, OK.