Office Hacks: Increase Data Line Chart Auto Extension

In Excel, many times the line graph is used to display data, the data is determined, and the line graph is basically fixed. So, when the data increases, how to automatically extend the line chart? In fact, if the line graph of the data source set to a “table”, the above effect is relatively easy to achieve.

First, the data source area as a chart selected (as shown in Figure A1:B9), switch to the “Insert” tab, click “Table”, in the pop-up window directly click “OK ” (Figure 1).

2021A-ZXTYC-1

At this point, the title of the selected data area into a filtered state, the data area cells into a blue and white state. Select the data source area as a chart again, switch to the “Insert” tab, click “two-dimensional line graph → line graph with data markers”, insert a line graph in the worksheet. Thus, when the data area to add content, this line chart will automatically extend (Figure 2).

2021A-ZXTYC-2

Although the above method is relatively simple, but it also has a disadvantage, that is, the destruction of the original style of the form, the form will be filled with color, and more fields at the drop-down list. So, there is no form on the basis of retaining the original style, but also to create a line graph automatically extend the effect of the method? Of course there is, that is, the use of OFFSET + COUNTA function method to obtain the data source of the chart.

First, switch to the “Formulas” tab, click “Name Manager”, click “New” in the pop-up window, in the pop-up “New Name” window, “Name” where you enter “data1”, “Reference Location” where you enter “= OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A),1)”; then create a new name, “name”, enter “data2”, “reference location”, enter “data2”. “reference location” at the input “=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A),1)” (Figure 3).

2021A-ZXTYC-3

Tip.

COUNTA(Sheet1!$A:$A) is used to count how many valid cells in column A. OFFSET is used to refer to the cell area starting from A1.

Next, select a blank cell, switch to the “Insert” tab, click the “two-dimensional line graph → line graph with data markers”, insert a blank chart in the worksheet. Right-click this chart, select “Select Data”, in the pop-up “Select Data Source” window on the left side of the click “Add”, in the pop-up window of ” Series Name” at the pop-up window, select cell B1, enter “=Sheet1!data2” at the “Series Value”; click the right “horizontal (category) axis label data2”; click on the right-hand side of the “horizontal (category) axis labels” at the “Edit”, and enter “=Sheet1!data1” at the pop-up axis label area. At this point, the chart’s data source is set up. In this way, the line chart will also be automatically extended when adding more data without destroying the original style of the worksheet (Figure 4).

2021A-ZXTYC-4

Leave a Comment