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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919