Excel Video 86 walks you through two of the three steps to use OFFSET to automatically update charts as we discussed in Excel Video 85. You’ll remember Excel Video 85 explained the need to use OFFSET, define a name, and use that name as the chart’s data source for a chart to automatically update. OFFSET is the most complicated of the three steps, so we’ll review defining a name in Name Manager and changing the chart’s data source in this video and work through the OFFSET function next time.
It’s easy to define a cell or a range of cells using the Name Manager in the Formulas tab. Once you define the name, you need to tell Excel the scope of the name. Scope refers to where Excel should recognize the name, in the entire workbook or just in a specific tab of the workbook. Our first example names a range BilledCharges. If the scope of BilledCharges is only this tab, other tabs in the same Excel workbook won’t recognize the name BilledCharges. If you plan on using the name in multiple tabs, you can make the scope of BilledCharges the entire workbook. I typically make the scope just the tab I’m working on unless I know beforehand that I’ll need the name in multiple worksheets.
Once the name’s defined in Name Manager, you need to assign the name to the Chart Series using the Select Data Source window. Be sure to include the tab name with an exclamation mark before the series name so Excel gets the right range for your data. You’ll see what happens if the range changes (the chart stops automatically updating) and how to restore the name back to the chart series as part of Excel Video 86.
Next time, we’ll walk through our first example of using the OFFSET function to define a range to name and then set as a data series for an automatically updating chart. I have several examples coming that I think will be helpful to explain OFFSET. Once you understand this process thoroughly, you can do all kinds of powerful things to your charts.
