Excel 993 - Charts that Expand As You Add New Data (OFFSET method) - Episode 993

4,311 views · Published 15 April 2009 · 5:25 · Indexed 22 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Charts that Expand As You Add New Data (OFFSET method)

Welcome back to the MrExcel netcast! I'm Bill Jelen and today we have a question from Ellie, who sent in her question via YouTube. Ellie has a chart that she wants to automatically extend when she adds new data to the right of her existing data. In today's video, we'll be discussing how to use a dynamic named range to solve this problem. This method works for both Excel 2003 and Excel 2007, but tomorrow we'll also be looking at an easier way to achieve the same result in Excel 2007.

First, let's talk about what a dynamic named range is. It's a named range that uses the OFFSET function, which can take up to five arguments. The starting cell always has to be an anchor cell, and in this case, we'll be creating a dynamic named range that refers to a yellow range that will grow as we add new items to the right. The starting cell for this range will be B2, and we'll use a formula to determine the number of rows and columns to include in the range. This formula will update automatically as we add new data, making it a truly dynamic range.

To create the dynamic named range, we'll use the Insert>Name>Define function and enter the OFFSET function with the appropriate arguments. We'll also create a second range for the chart labels, which will be one row above the data range. Once we have our two dynamic named ranges, we can go into the chart and replace the existing data and label ranges with our named ranges. This will ensure that the chart automatically updates as we add new data to the right.

Now, this method works for both Excel 2003 and Excel 2007, but there are easier ways to achieve the same result in Excel 2007. We'll be discussing those methods in tomorrow's video, so be sure to check that out if you're using Excel 2007. But for now, this dynamic named range method is a tried and true solution that has been used for a long time. Thank you for watching and be sure to tune in next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:20) Problem with chart not automatically updating
(00:31) Solution using dynamic named range
(01:00) Explanation of OFFSET function
(02:38) Creating dynamic named range for chart labels
(03:36) Applying named ranges to chart
(04:32) Demonstration of automatic updating
(04:49) Alternative methods in Excel 2003 and 2007
(05:02) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Excel chart data extension using dynamic named ranges
Excel OFFSET function explained
Excel tutorial on creating dynamic charts with changing data
Excel tutorial on creating dynamic ranges for charts
How to automatically extend a chart in Excel when adding new data
How to update a chart automatically in Excel
MrExcel netcast on extending charts with new data
Solving chart extension problem in Excel 2003 and Excel 2007
Step-by-step guide to creating dynamic named ranges in Excel
Tips for using dynamic named ranges in Excel
Using OFFSET function to create dynamic named ranges in Excel
YouTube tutorial on using dynamic named ranges in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152356/



Ally asks how to have a chart automatically extend as you type new data. The classic solution is to use a pair of dynamic named ranges using OFFSET. While this can be painful, Episode 993 shows how to set it up. Be sure to watch Episode 994 tomorrow for an easier way in Excel 2003-2007.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

More from this channel