How Excel implements graph and data linkage dynamic charts

companies often display their work performance in column charts. When you make a chart in Excel, the data source of the chart is manually selected from the existing data. But sometimes the number of employees is not fixed, which means that the original data area is also variable, it is likely to continue to increase, so that the increase in data will have to manually update the data source of the chart, so that the operation is more cumbersome. In fact, if the original data for the variability of settings, you can achieve the graph and data linkage dynamic chart.

If you need to use column chart to show the performance of employees, when the number of staff increase or decrease, the number of columns to achieve the corresponding changes automatically, that is, increase or decrease (Figure 1). In general operation, the data source for the numeric series of a column chart in an instance is a manually selected B2:b9, and the data source of the horizontal (category) axis label is also a manually selected A2:a9, which is fixed. To achieve these effects, you need to change the two data sources to a mutable expression.

1. Data Source Name definition

Open the datasheet with Excel 2013, click "Formula → define name", enter "category axis" in the name of the pop-up window, enter "=offset ($A $,,, COUNTA ($A: $A) -1,1)" In the same way, and define a name called "value axis" in the same manner. , at the reference location, enter "=offset ($B $,,, COUNTA ($A: $A) -1,1)" (Figure 2).

2. chart Data Source Modification

After the data source name definition is complete, you should modify the two data sources for the column chart.

Right-click the column chart, select "Select Data", and click the "Edit" button under "Legend Entries (series)" In the pop-up window, enter the sheet1! value axis at the pop-up series value (Sheet1 depends on the name of the datasheet), and click the Edit button under horizontal (category) axis labels. Enter the "=sheet1! category axis" in the pop-up window (Figure 3).

Finally click "OK" to return to the Excel worksheet interface. When you add one row of data in column A and column B, the chart automatically changes and the new column is automatically added.

Extension tips:

The above only by the staff sales performance for example. In fact, it is possible to use this method to achieve the dynamic addition of table items, such as student make-up test scores and family amateur income statistics. WPS Tutorial