How to Calculate YTD (Year to Date) Values in Excel using the SCAN function

A common task for finance professionals is to create YTD (Year-To-Date) calculations in Microsoft Excel. Usually these formulas can get quite complex. You might use a combination of SUMPRODUCT and OFFSET. In this video I'll show you a solution using the new Excel SCAN function. We create a flexible YTD function so that you can easily adjust for fiscal months. If you get more data, the Year to date calculations automatically update.

Discover how to:
⩥ Use the SCAN function for simple to-date calculations, allowing formulas to automatically spill down without manual dragging.
⩥ Integrate LAMBDA functions with SCAN for custom calculations, enabling you to process data with precision.
⩥ Dynamically calculate YTD values, adjusting for new calendar years within your data set to reset accumulations accurately.
⩥ Apply practical examples with sales data to see the SCAN function in action, demonstrating both to-date and YTD calculations.
⩥ Adjust for fiscal calendars, showing how to modify the formula for different fiscal year starts, ensuring versatility across various accounting periods.

We'll combine the SCAN, LAMBDA and Excel OFFSET functions to create a (simple) formula that dynamically updates your results and resets whenever it crosses a year-end.

Note: The SCAN function is available in Microsoft 365.

00:00 Calculate YTD Balances Dynamically in Excel
00:41 How to Calculate To-Date With the SCAN Function
02:42 How the Excel SCAN Function Works
03:59 How to Calculate Year-To-Date With the SCAN Function
07:53 Wrap Up

🎬 LINKS to related videos:

Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[rigelabanes]

YOU ARE HEAVEN SENT!!! I wish you have an Excel book for ordinary Joes like me.

[HeatherMajors]

This is more complex than anything I am currently using excel for but I love these videos. When I do need to get back into Excel I know I can come right back to these videos and refresh my mind.

[cdyard]

I love these videos about the new functions even if I'm unable to use them (or even to understant how they work).

[pedroemanuelrebelo]

These tips are awesome. In a "simple" way we have this information without more. Congratulations on clarity.

[awesh]

Leila you make these complex problems so easy. You are just awesome

[tifouraamina]

Honestly, the lamda function is my favourite 😊thanks Leila you're the best

[SolidSnake]

Thank you. I like when you are taking excel formulas to the next level.

[HoppiHopp]

This was extremely helpful. Thanks to scan plus lambda I was able to calculate values adding up per month and finally finding the month with the highest value without pivot and fully dynamic.

[yogibearstie]

Thank you. This will be useful, once released, for a 26 year monthly Excel I maintain to have running FYTD. Actually even more used for FYTD since it is everywhere.

[LarsHHoog]

I'd simply put a pivot table next to the table and use sum and running total options

[g.muthukumar]

For Finally Scan function is used for Cumulative calculation, I love this formula, Thanks for your videos
😊

[sasavienne]

Simply amazing.
Stunning.


Thank you

[irajabaly]

wonderful helpful function and best quality course . Thank you lovely and talented Leila

[martinilopez]

your knowledge is from another world! hahaha thank you lei :)

[pauldadzie]

Best channel ever!!!! I actually got a promotion using all your videos and improving my office skills. I can't thank you enough.

[drsteele]

Leila, this is a monumentally powerful function! I'm trying to get it to determine streaks in a list, like winning streaks for sports teams or stock markets.

[wayneedmondson]

Hi Leila. Great example of how to use SCAN and LAMBDA to create a useful result for a real world challenge! Thanks for sharing :)) Thumbs up!!

[PopeLando]

I needed a way to reference another cell not via a fixed reference but a way of calculating whichever other cell I needed. Google did not find the OFFSET function for me, and it's exactly what i was looking for, so thank you!

[KrishnaKumar-znkg]

Very nice. Opens up immense possibilities.

[crp]

Excellent explanation of SCAN. I am an old-school LISP programmer. I am so glad that finally functional programming has arrived in Excel. Thus I prefer single-cell formulas. Thanks to your video I can add SCAN to my kit of powerful tools.