Excel Get The Rolling Total For The Last 12 Months Using XLOOKUP - Episode 2564

Microsoft Excel Tutorial: Adding a Rolling 12 Month Total in Excel

Welcome to episode 2564 of the MrExcel netcast, where we explore the hidden secrets of Excel. In this episode, we will be discussing a tip from Paul, who attended my seminar in Akron last week. Paul is an experienced Excel user, having won an Excel Master pin in Albuquerque and an Excel Guru patch. He shared with us a clever way to get a rolling 12-month forecast using XLOOKUP, and I am excited to share it with all of you.

The goal of this tip is to get the total for the last 12 months, or any number of months, ending on a specific date. We have two input cells, one for the number of months and one for the ending date. Paul's date is by month end, so we will be using that format. We will be using XLOOKUP, but at first glance, it may not seem like the right function for this task. However, there is a secret behavior in Excel that we will be utilizing to make this work.

We will start by using the EO month function to get the starting date. Then, we will use XLOOKUP to get the total for the first month. But here's where the secret comes in - if we add a colon immediately after the XLOOKUP function, it will return the cell address instead of the value. We can then use this cell address in our sum formula to get the total for all the months in between. This is a game-changing trick that can save us from using the volatile OFFSET function. And the best part? It works not just with XLOOKUP, but also with eight other functions - CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET, SWITCH, and XLOOKUP.

I want to thank Paul for sharing this brilliant tip with us, and I hope it will make your Excel experience even better. Don't forget to subscribe to our channel for more hidden secrets and tips from the world of Excel. And if you have any tips or tricks that you would like to share with us, please leave them in the comments below. Thank you for watching, and we'll see you in the next episode of the MrExcel netcast.

Table of Contents
(0:00) Problem Statement: Excel Rolling 12 Months Total
(0:40) Use Excel EOMONTH with negative 11 to find start month
(1:05) XLOOKUP appears to not solve problem
(1:31) Nine functions that can appear next to colon
(2:10) Change comma to colon inside of SUM in Excel
(3:00) XLOOKUP from Starting point to end point and get everything in middle
(3:15) Using Excel Evaluate Formula to watch formula in slow motion
(3:50) Changing date input cells in Excel
(4:20) Avoids volatile OFFSET function
(4:45) 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 #excelformula #microsoft365 #walkthrough #xlookup

This video answers these common search terms:
colon makes xlookup return cell reference
xlookup return cell reference with colon
excel xlookup result as cell reference
using colon for cell reference in xlookup
xlookup return address with colon
excel xlookup and cell reference syntax
how to sum cumulative in excel
how to do running sum in excel
how to create excel running sum
how to do running total excel
how to use xlookup formula in excel

Thanks for this tip from Paul A who was in my Akron seminar.

Paul uses a function like SUM(XLOOKUP:XLOOKUP) to return the total of all numbers from a start date through an end date.

[excelisfun]

So many videos, Mr Had to watch all 10 videos you posted since yesterday, today lol

[DimEarly]

It’s not just the colon - you can also put a # on the end of an XLOOKUP to give the array anchored on the cell address that you looked up. You can do some pretty cool tricks with that : )

[thegaminglearning]

The cell reference technique of XLOOKUP is extraordinary.

However, we could simply use the FILTER function or SUMIFS function to do it.

[alexandrasmaridge]

This has solved my YTD issues like a charm! Instead of a fixed second date, it references a TODAY cell for a continuously updating monthly dashboard. Thanks!

[nadermounir]

Thank you Mr.Excel for this nice video 📹

[drsteele]

It's neat how the evaluator cleanses everything near the colon.

[KO]

So that's a cool trick regarding returning the cell address but why would anyone solve the problem in that way in real life? First, it assumes the data is sorted because if it is not the returned result will not be correct. Why not just use SUMIFS to sum the revenue based on the date being between the start and end date?

[SusanFerguson-fm]

I am new to pivot tables. Working with Windows version 11. Data has already been dumped into a pivot chart and updates every time it is opened. Can I create a 12-month rolling chart in this table or do I have to start over?

[ExcelInstructor]

Hi Bill, its a neat trick for sure, but why would any1 choose this trick that is not well known over sum(Filter()) functions?

[EgoShredder]

I need to have excel locate the last added cell entry in a column using todays date, and then sum that cell with the previous 11, to give a rolling 12 month total figure.

[antique-bsbb]

So presumably without the Sum( ) function the double xlookup should simply give the spilled range alone (It does).
Which makes it easier to understand, but is not nearly as impressive!

[monicadiaz]

Thank you. Would it be possible to do the same but for excel 2016 version? Im unable to use xlookup

[JonathanExcels]

I’m pretty sure I specifically remember you mentioning that xloopup returns a cell address when you first told us about the function. Wasn’t it in a whitepaper?

[a.j.wilkes]

Ordering my cell references "Animal Style." :)