Excel - Best Way For Running Totals - Episode 2590

Microsoft Excel Tutorial: Best Methods for Creating Running Totals in Excel | Comparison of 3 Formulas | MrExcel.

Microsoft Excel how-to video about Running Totals in Excel.
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I've never seen before.

I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?

In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which are easiest to explain? Which run the fastest? Thanks to Zack Barresse for teaching me how =SUM(Number,Text) ignore Text without giving an error. Thanks to Charles Williams for the Formula Speed whitepaper and for the Fast Excel tool to measure the speed of the formulas.

You will also catch a Nancy Faust preview of the Spinning Wheel song to entertain you while we wait for the slow versions to calculate.

Welcome to episode 2590 of MrExcel's YouTube channel! In this video, we will be discussing the best way to create running totals in Excel. This topic was inspired by a comment from Carlo in Italy on one of my Bing shorts videos. Carlo shared a unique running total formula that caught my attention and led me to explore different methods for creating running totals in Excel.

In this video, we will be comparing three different methods for creating running totals: the "Bill Kindergarten" method, the "MrExcel" method, and Carlo's method. Each method has its own advantages and disadvantages, and we will be discussing the criteria for judging the best method. These criteria include having the same formula all the way down, being easy to explain and teach, and impressing coworkers.

The "Bill Kindergarten" method, which I used for 12 years while working in accounting, involves using two different formulas to calculate the running total. The "MrExcel" method, which I currently use, involves using the SUM function to add the cell above and the cell to the left. Carlo's method, on the other hand, uses the INDEX function to point to a specific cell and then uses the SUM function to add the cell above and the cell to the left.

After testing each method using Charles Williams' Fast Excel tool, we found that the "Bill Kindergarten" method was the fastest, followed by the "MrExcel" method and then Carlo's method. However, the "MrExcel" method received the highest score due to its simplicity and ease of explanation. Carlo's method, while impressive, may be more difficult to teach and understand for those who are not familiar with the INDEX function.

In the end, the best method for creating running totals may depend on personal preference and the specific needs of the user. I would love to hear from you in the comments below about your preferred method for creating running totals and how you teach it to others. And if you enjoyed this video, don't forget to like, subscribe, and ring the bell to be notified of future videos. Thank you for watching and see you next time on MrExcel's YouTube channel!

Table of Contents
(0:00) Running Totals in Excel
(0:14) Bing Shorts explanation
(0:31) Formula from Carlo
(1:08) 8 Functions change when colon-adjacent
(1:40) Why not shorten INDEX?
(2:06) =Up plus =Left with two formulas
(2:45) Zack Barresse and SUM(Up,Left)
(3:15) MrExcel way with expanding range
(4:15) Judging criteria for best running total
(4:45) Charles Williams fastest Running Total
(5:07) Simple formula: less than a second
(5:24) Expanding range: 26.3 seconds
(5:44) Carlo 36.4 seconds
(5:58) Why the simple formula is faster
(6:39) =SUM(Left,Up) is winner
(7:18) Wrap-up

#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

This video answers these common search terms:
Bill Kindergarten method
Bing shorts
Cell references in Excel formulas
Expanding range in Excel
Fast Excel by Charles Williams
Formula speed in Excel
INDEX function in Excel
MrExcel method
Running total formula
Running totals in Excel
Teaching coworkers Excel techniques

[excelisfun]

I use that SUM function with ignoring text. I love that unsophisticated one lol

[JoseAntonioMorato]

Dear Bill,
With the new dynamic SCAN function it is much easier, and the result spills over:
=SCAN(0, D5:D13, LAMBDA(x, y, x+y)) 🤗

[Excelambda]

Super interesting survey!!✌
Remembered, before having SCAN, how I did this, single cell, using MMULT
-for running totals
=LET(a, B2#, r, ROWS(a), s, SEQUENCE(r), MMULT(IF(s>=SEQUENCE(, r), TRANSPOSE(a), 0), s^0))
-and even running "subtraction" 😉
=LET(a, B2#, r, ROWS(a), s, SEQUENCE(, r), TRANSPOSE(MMULT(s^0, IF(SEQUENCE(r)>=s, a, 0))))
What is interesting with this is that because of its "squareness" mmult concept, excel runs out of resources for vectors larger than 7327 elements.
Believe it or not, Excel largest square sequence that can "print" or calculate is only =SEQUENCE(7327, 7327)
Anyhow, SCAN is fast and easy with a single initial acumulator and an array, the challenge is to reset the initial value by row or by column for 2D super large arrays. Have a study on this one, one day will get published at the forum.
Again, Super cool to test speeds of different methods. ✌🙏

[notesfromleisa-land]

I use running totals in tables in excel as I typically need table structure with index column to analyze account activity. I use the Sum(header row in column:first value in column) from Jon at Excel Campus. This application works well for adding/deleting items in the table without bastardizing the runttl function. That said, I love the simplicity of what you demonstrated.

[excelisfun]

Fun and informative video, Mr Excel!!!!

[nadermounir]

Thank you Bill for this interesting Video

[ricos]

Very interesting Bill, thanks. I'll just SCAN the comments for any differing suggestions.

[davidabuang]

I love the =SUM(E4, D5) tip! Simplicity is best! My biggest beef with this type of running total, though, is that inserting or deleting rows causes cell reference issues. To overcome that, I use a relative cell reference in Name Manager called CellAbove. Then my running total would be =CellAbove+D5, and my first row would be a hard keyed “opening balance” (when working with accounting data) to avoid the different formula. Now I should be able to just use =SUM(CellAbove, D5). Thanks!!!

[drsteele]

This works well in a range, and it works especially well in an Excel Table (where the cells are structured references). E5 =N(OFFSET(E5, -1, ))+D5

[paulsingleton]

Thank you for your video Bill, very interesting.
I like the Scan/Lambda combination although sometimes it does not suit your situation.
For example, when producing the running total for sales for the month and you have 5 days to go the formula repeats the last value for the remaining cells.
I had to resort to this: =SCAN(0, C8#, LAMBDA(a, b, IF(VALUE(OFFSET(b, -6, ))<TODAY(), a+b, NA()))) which uses a volatile function in order to curtail the results.
Now I just need Microsoft to allow Excel to show the last data label in a dynamic chart!

[einoconsult]

@Bill I do not have the add-in to calculate the speed, what if you wrap Carlo's formula in a LET statement
=LET(a, INDEX($D$5:$D$13, 1), SOMME(a:D5)), so that it will calculate the INDEX($D$5:$D$13, 1) only once ?

[KO]

Thanks Bill. One additional point for consideration. I know you don't like tables but if this is being done inside a table (A1:B10) where A is number and B is running total the formula for column B = SUM (A2, B1) will break (and the user probably won't know it) if the user inserts a row in the the middle of the table (lets say new row = 5) because the formula in the new row will be correct (SUM(A5, B4) but row 6 will not adjust accordingly but instead will be SUM(A6, B4). To combat this I've moved to using the formula =SUM(A2, OFFSET(B2, -1, 0)) which will still be accurate if a row is inserted in a table. It loses points on "explainable to someone else" criteria but better than breaking and still much faster than SUM(A$2:A2).

[kleinboertjie]

Hi
One more way with one formula adding cell above

In the header cell, type 0 and change the custom formatting to display the header description in all the categories for positive values; negative values; zero values; text values.
Then just add the value to the left to the value above

[c.e.bingham]

Very interesting analysis of which formula works faster. I often use the Mr. Excel way. However, I will consider going back to Bill kindergarten way. It just makes more sense even if my spreadsheets are relatively small by comparison of 100, 000 rows.

[realBkay]

00:28 - Bill, I have never been annoyed by any of ur content.

[richardhay]

In a Table (vitually all my data sets are in Tables) click on header, then insert a colon, then click on the cell below the header, then wrap the expsnding range in SUM (or SUMIFS, etc). Really fast to set up.

[kurtneubek]

This only refers to 2 cells and is easy to explain. Too slow? E5=N(E4)+D5

[carlo_migliari]

Thank you Mr. excel! I'm curious about the time that the SCAN solution posted by someone below may takes compared to the other methods

[thepoolstatguy]

Can you do one video showing the splll vs copying the formula😜

[RambozoClown]

But can you create a formula that takes over an hour to do 100, 000 cells?