Chart automatically updates 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 exercise Excel workbook: Http://www.yesky.com/imagesnew/software/still/update.xls).

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