Excel Chart with Rolling 6 Months, 12 Months, or User-Defined End Date using Dynamic Named Ranges

This video has been updated with newer functions for Microsoft 365. You can find the newer video here:
   • How to use GROUPBY to chart rolling 6 mont...  

In this video you will see, how to make a chart in Microsoft Excel with dynamic rolling periods that are updated, as soon as new months are added to the table. Alternatively, this solution can be extended to show the consecutive months, the number of which and end date can be flexibly chosen by the user from a drop-down list to zoom in and out of one single chart. The solution does not need additional tables or Visual Basic for Applications and is instead based on using dynamic named ranges and formulas returning arrays. Two options will be shown for the formulas-one using the OFFSET function and the other using the INDEX function. The OFFSET function will recalculate every time that a change is made on the sheet, even in cells not related to it, so if calculation time is an issue for you because of a very large file, then please use the formulas with INDEX function instead.

Chapters
00:00 Introduction
01:04 Preparing the table and line chart
01:41 The concept for charting the rolling months
03:11 Formula for rolling 6 months with OFFSET function
04:48 Creating dynamic named ranges with the formula
06:13 Using dynamic named ranges in the chart
07:27 Adapting the named ranges for rolling 12 months
07:56 INDEX function as alternative to OFFSET for rolling months
11:24 User-defined end date and number of months
11:59 Creating a drop-down list of months using data validation
13:09 Formula with OFFSET and MATCH functions
15:43 Updating the named ranges with the new formula
17:20 Using INDEX and MATCH functions for a similar effect
19:13 Updating the named ranges

If you need an alternative to using dynamic named ranges for making the dynamic chart in Microsoft 365 and Excel for the web, then take a look at this video    • A feature that you absolutely need to know...  

For more contents like this, please subscribe to my channel.
#MsExcel #ExcelTips
Screenshots used with permission from Microsoft.