Excel - Formula for Running Totals - in Ranges OR Tables! - Episode 2004

Microsoft Excel Tutorial - Formula for Running Totals.

Welcome to the MrExcel Podcast, Episode 2004 - Running Totals. In this episode, we will be discussing different methods for creating running totals in Excel. Make sure to click the "i" on the top right-hand corner to subscribe to our channel for more Excel tips and tricks.

Our guest for this topic is my friend Zach Parise, who is known as the world's expert on Excel tables. He has even written a book about Excel tables. But before we dive into that, let's first talk about running totals outside of tables.

There are three different ways to create running totals in Excel, and the first method is to use a different formula in the first row and then a simple formula for the rest of the rows. However, this can lead to errors when trying to add text and numbers together. The second method is to use the expanding range, which can be useful but can also slow down your spreadsheet. The third method, which I used to love, is to use structured referencing. However, this method was debunked by Charles Williams, an Excel MVP from England, who proved that it is much slower and can cause issues when adding new rows.

But then, I discovered Zack's book about Excel tables and learned about the automatic formatting and formula maintenance that tables offer. However, I also discovered a problem with using the previous methods in tables. This led to Zakh offering two workarounds, one of which uses the OFFSET function, which can cause issues with volatile functions. After exploring all these methods, my final opinion is to use the method that only looks at two references and uses structured table references, as it is the most efficient and will work with tables.

For more Excel tips and tricks, make sure to check out our book "MrExcel XL: The 40 Greatest Excel Tips of All Time". In this book, we cover 40 useful tips, including the three methods for creating running totals that we discussed in this episode. Thank you for tuning in to this episode of the MrExcel Podcast, and we'll see you next time for another netcast from MrExcel.

Table of Contents:
(00:00) Running Totals in Excel
(00:17) Three ways to do running totals in Excel
(00:27) First method: different formula for first row
(00:37) Second method: using SUM function
(00:47) Third method: using expanding range
(01:33) Using R1C1 reference style
(02:28) Charles Williams' debunking of expanding range method
(03:02) Problem with using this method in Excel tables
(03:51) Workarounds using structured references
(04:09) First workaround: using INDEX function
(04:29) Second workaround: using OFFSET function
(05:06) 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 #excelformulasandfunctions #runningtotals

This video answers these common search terms:
excel how to do a cumulative sum
how to do a running total in excel
how to do running sum in excel
how to keep a running total in excel
how to do a running total in excel table
creating a running total in excel
how do you do a running total in excel
how to create a running total in excel table
excel how to calculate a running total
how do you get the running total to work in excel
how to create a running sum on excel
how to calculate a running sum in excel table

Running totals, both in regular data and in Ctrl+T tables. Recap:
This episode shows three ways to do running totals
The first method has a different formula in Row 2 than all the other rows
The first method is =Left in row 2 and =Left+Up in rows 3 through N
If you try to use the same formula, you get a #Value error with =Total+Number
Method 2 uses =SUM(Up,Left) or =SUM(Previous Total,This Row Amount)
SUM ignores Text so you don't get a VALUE error
Method 3 uses an expanding range: =SUM(B$2:B2)
Expanding ranges are cool, but they are slow
Read the Charles Williams whitepaper on Excel Formula Speed
The third method is a problem when you use Ctrl+T and add new rows
Excel can't figure out how to write the formula
The workarounds require some knowledge of structured referencing in Tables
[Qty] refers to all Qty values

[brianxyz]

I just tried the Offset method with 1 million rows of data. Took forever to do the calculations. Power Pivot on the other hand was amazingly fast.

[jensstadsgaard]

A fast way to running totals without writing formulas is using the quick analysis button or right click menu. Either by marking the data or right click in a cell. Then select totals an find the one with running totals in a column.
The only disasvage is that it uses method 3 to write the formulas.

[nickchan]

How do I get the sum of a single cell? example A1 is updated by RTD function and has 10 at 9 am. An hour later, A1 is 5, so my total is 15 so on...