Excel Video 85 starts our discussion of OFFSET, a powerful Excel function you can use to update your charts automatically. If you’ve missed some of the basics of charting videos because you’re familiar with charts in Excel, it’s time to start watching again. We’re going from very basic to pretty powerful now that we’re talking about OFFSET.
Excel Video 85 starts with a bad example of a chart. The chart has a date range for the full year, but we’re only a few months into the year. Whether you delete the zeros in the remaining months in the year, delete the data for the remaining months of the year, or even delete the months themselves, the chart still only takes up about half of the chart space. You can make your charts look much better if the data ranges automatically update when you add or remove data from the chart. Here’s how to do it.
The trick is to use Excel’s OFFSET function in Excel’s Name Manager to define a name that adjusts as data are added and subtracted. Once you have the name defined, use that name when you set up the chart’s data source. Those are three keys to making the chart update automatically. You need to use OFFSET, define a name, and use that name as the chart’s data source. I’ll review those three steps with several examples in upcoming videos.
For those of you who are familiar with and use Excel 2007 Tables like we discussed in Excel Video 83, your charts are already updating automatically. Why bother learning OFFSET, defining names, and changing the chart’s data source? Stay tuned. Once you understand the OFFSET process, you can make your chart do all kinds of things that you can’t do with Tables.
