O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #52. Create Charts That Adjust to Data

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.

Data to be charted

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).

Tip

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 formula:

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

Notice that you included a -1 immediately after the COUNTA argument. This ensures that the heading is not included in the named range for that particular series.

Tip

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required