Excel Chart with Rolling 6 Months, 12 Months, or User-Defined End Date using Dynamic Named Ranges скачать в хорошем качестве

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.