Charting a Dynamic Year-to-Date Period Using the OFFSET Function in Microsoft Excel (Part 1 of 2)

In the last video I showed you a dynamic chart with rolling 6 months, 12 months, or a variable end date selected by the user from a drop-down list. In this video you will see, how to make a dynamic chart with the year-to-date period using the OFFSET function. 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. A year-to-date is the period from the beginning of the year to the current date. The formulas will be shown for the calendar year and a fiscal year that is different from it. In this example the fiscal year will start in April. The OFFSET function will recalculate every time that a change is made on the sheet, even in cells not related to it, so in part 2 I will show you an alternative for large files using the index function.

00:00 Dynamic year-to-date period
01:00 Preparing the table and chart
01:36 Formula for calendar year-to-date period using the OFFSET function
04:15 Defining the dynamic named range for values
04:55 Defining the dynamic named range for axis labels
05:26 Using the dynamic named ranges in the chart
06:36 Formula for fiscal year-to-date period using the OFFSET function
08:55 Dynamic named range for values
09:13 Dynamic named range for axis labels
09:44 Choosing the ending date from a drop-down list
10:00 Preparing the drop-down list using data validation
10:44 Adapting the formula for calendar year-to-date
12:18 Using the formula in the Name Manager
14:06 Adapting the formula for fiscal year-to-date
14:55 Using the formula in the Name Manager
15:38 Troubleshooting

The link to the last video with the rolling months:
   • Excel Chart with Rolling 6 Months, 12 Mont...  

The link to part 2 with the index function:
   • Charting a Dynamic Year-to-Date Period Usi...  

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