Chart Automatic Update Setup Tutorial when Excel enters new data

I have a friend who is a salesman. He uses Excel charts to record his daily sales performance. That day, he called to ask the question: Excel to record daily sales is very convenient, can visually express the daily sales results, but because there are new data every day, so I have to manually change the chart every day to include new data. Is there a way to let me enter new data when the chart can be automatically updated. The following method will solve the problem for him.

In Excel 97 and later, when a chart data series is selected, a border appears around the data range that corresponds to the data series in the worksheet, and you can extend the data region by simply dragging the corner points of the area border. The approach used in this article is to define a dynamic data range by using a formula to create a chart that automatically updates the chart when you enter data without having to manually change the range of data regions.

The specific steps are as follows:

1. Enter the data and create the chart as shown in Figure 1 (Download the practice Excel workbook).

Figure 1

2. Select the menu command "Insert > Name > Definition" To open the Define Name dialog box. Enter a date in the Enter box under name in current workbook, enter the formula in the input box under reference position =offset (sheet1! $A $2,0,0,counta (sheet1! $A: $A)-1), and click the Add button to add the date. As shown in Figure 2.

Figure 2

Note: The offset function refers to the first data point (cell A2) and uses the CountA function to get the number of data points in column A. Because column A contains a non-numeric data "date", subtract 1 from the formula.

3. Continue to define the name in the Define Name dialog box. Enter sales in the Enter box under name in current workbook, enter the formula in the input box under reference position =offset (sheet1! $B $2,0,0,counta (sheet1! $B: $B)-1), and click the Add button, as shown in Figure 3. Then click the OK button to close the dialog box.

Figure 3

4. To activate the chart and select the data series, you can see the unchanged formula in the Edit bar: =series (sheet1! $B $1,sheet1! $A $: $A $10,sheet1! $B $: $B $10,1), this is a very important step, and we're going to make an alternative , the formula is changed as follows: =series (, sheet1! date, sheet1! sales, 1), as shown in Figure 4. In the changed formula, you can see the names we defined in step two and step three: dates and sales.

Figure 4

Having done the above work, the friend's problem is solved. When we enter new data, the chart will be updated automatically and try it quickly. As shown in Figure 5, the chart is updated automatically with the sales of February 10. Finally, when using the offset function, be sure to point the CountA function to the first numeric data, and if you point to the first non-numeric data, then the automatic Update is not available.

Figure 5

Category:

• Excel tutorial