» Autoexpanding a Date Type Chart using Microsoft Excel

The tip submitted by Dan Nickelson, Fargo, ND

When making a Chart that contains info for a date range, you often have to fill in data you know, and go back and adjust your Chart to include the subsequent date range once data is available.

To avoid this particular issue, make the data your Chart references a link to 'real' data.  In this reference data area, insert a formula such as =if(A1<>"",A1,NA()) so the cells referencing accumulated data are filled in, while cells referenced not yet accumulated data are filled with #N/A.

You also need to set the chart to time-scale by selecting Chart Options\Axes\Time Scale

By doing this, you can have the chart reference 'dates' and data that haven't yet arrived.  Excel ignores the #N/A fields until their referenced cells contain data, expanding the chart on the fly to include the 'new' data.