Your charts can include and plot new data automatically, the moment you add the data to your spreadsheet.
If you use dynamic named ranges in lieu of range references, your chart will plot any new data the moment you add it to your worksheet. To see how this works, begin with a clean worksheet and set up some data similar to that shown in Figure 5-7.
Figure 5-7. Data to be charted
To create the chart and make it dynamic, you need to add two named ranges. One of the named ranges is for the category labels (Dates) and the other is for the actual data points (Temperature).
If you are unsure as to how to insert a dynamic named range, check out [Hack #42], which discusses this in full.
Create a dynamic named range called
TEMP_DATES for the dates in column A by
selecting Insert → Name → Define, and type this
Notice that you included a
-1 immediately after the
argument. This ensures that the heading is not included in the named
range for that particular series.
In this example, you referenced the entire column A as the
COUNTA argument ($A:$A). In older versions of Excel, it is often good practice to restrict this range to a much smaller group of cells, so as not to add unnecessary overhead to calculations. In other words, you could be forcing Excel to look in potentially thousands of cells ...