• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Running Total Formula

You are here: Home / Excel Formulas / Excel Running Total Formula
November 30, 2010 by Mynda Treacy

Quite often we want to see a running total or running balance column for our data.  There are a few ways we can do this, the technique differs depending on whether your data is in an Excel Table or not.

Watch the Video

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook to practice here. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Let’s take the table below; say we want to add a running total to column G.

Excel running balance formula

Excel Running Total Formula

Option 1:

Use a simple = + calculation in column G that goes as follows:

In cell G3 =E3

In cell G4 =G3+E4

In cell G5 =G4+E5

And so on....

You can Auto Fill from G4 to the bottom as your formula remains the same from this point on.

Excel running total formula

The problem with this approach is that you can’t add or delete rows without breaking the formulas.

The easy way to fix this is to enter the formula again and copy it down.  But since we’re all busy, and if you’re constantly adding to or changing your table, option 2 is a more efficient solution.

Option 2 (preferred non-tables):

Use a SUM formula with a combination of absolute and relative references.

Putting our running balance SUM formula in column I it would read:

Row 3 =SUM($E$3:E3)

Notice the combination of both absolute and relative references in the SUM range.

Now when we copy the formula down the column the relative reference will automatically increase by one row.

Row 4 =SUM($E$3:E4)

Row 5 =SUM($E$3:E5)....and so on.

Excel running total with absolute and relative references

Benefits of this approach:

1)      The formula on the first row isn’t different to the rest, so you won’t make a mistake and copy the wrong formula into new cells.

2)      When you insert or delete rows the formula automatically updates itself, and you only have to copy the formula from the row above/below into the new cells.

Option 3 (for tables):

Click here for the tutorial on running totals for Excel Tables, or watch the video at the top of the page.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:hlookup functionExcel HLOOKUP Formulas Explained
Next Post:INDEX MATCH Functions in Excelindex and match formulas

Reader Interactions

Comments

  1. Eric Kalin

    September 25, 2020 at 4:50 am

    Hi, Mynda.

    Before Excel, I used Microsoft’s Multiplan, which used relative references, now Excel calls them “R1C1 reference style” in the formula options. These allow a very simple solution. First, define a variable, say “CellAbove”, as =R[-1]C, which means one row above this cell and in the same column. (You can switch to R1C1 reference style, define the variable, switch back to A1 reference style, and it will still work.)

    This works especially well in Excel (structured reference) tables. If the total column is called “Total”, then in the running total column, use this formula:
    =SUM(CellAbove,@Total)

    Reply
    • Mynda Treacy

      September 25, 2020 at 11:00 am

      Nice tip, Eric! Thanks for sharing 🙂

      Reply
  2. Therese Duane

    November 2, 2018 at 10:20 am

    Thanks for the downloads, I look forward to learning from them.
    I have an immediate problem. I created a simple spreadsheet for recording donations to our annual non-profit fund raising drive. I put in a column to a running total. Rather than having to scroll down to read the last entry, is there a way I can put just one cell at the top (maybe over to the right) to reflect the running balance. This would save scrolling down if someone just wants to check the latest total.
    Thanks,
    Therese

    Reply
    • Mynda Treacy

      November 2, 2018 at 10:56 am

      Hi Therese,

      This tutorial explains how to write a formula that returns the last value in a column.

      Mynda

      Reply
  3. Alister Bottomley

    November 11, 2017 at 2:38 am

    Hi

    Can you tell me please what would the formula be if instead of adding I3 to E4 giving the total of 5440 in I5 you wanted to add I3 to C4+D4+E4 making a total of 5834 and continue to add the SUM of columns C+D+E.

    Reply
    • Mynda Treacy

      November 11, 2017 at 2:08 pm

      Hi Alister,

      It’s difficult to visualise your scenario, can you please post your question on our Excel forum and upload a sample file so we can see what you’re trying to accomplish.

      Mynda

      Reply
  4. Jim Klein

    February 24, 2017 at 1:21 am

    Can you provide a formula for a running cumulative balance that would have as its input either a negative number (e.g., a withdrawal amount) or a positive number (e.g., a deposit amount)? each row would only have one input (negative or positive number).

    Thank you.

    Reply
    • Mynda Treacy

      February 24, 2017 at 10:40 am

      Hi Jim,

      Sure, you can modify the formula like so:

      =SUM($E$3:E3)+SUM($F$3:F3)

      Assuming column F contains your other values.

      Mynda

      Reply
  5. Mort Wakeland

    November 8, 2015 at 12:42 am

    My situation is different, for running totals in that it needs to be continuous. Say Jan 2015 to Dec 2015, 12 month period for the total. As soon as one would enter a figure for Jan 2016, the Jan 2015 amount would drop and the new running total would be Feb 2015 to Jan 2016. Thank you bunches Mynda & Phil….Mort in Dallas, TX

    Reply
    • Catalin Bombea

      November 8, 2015 at 4:20 pm

      Hi Morton,
      Please upload a sample file on Help Desk (create a new ticket) , or by mail.
      Cheers,
      Catalin

      Reply
      • Morton Wakeland

        November 9, 2015 at 12:54 am

        Thanks Catalin – this may(?) benefit some. Let me see how to upload a simple lil file. At the present time, I’m simply trying to get the data into a worksheet via a UserForm, then worry about data manipulation. See? When I get to data manipulation, will post, OK? Best Sunday wishes to all, Mort in Dallas, TX

        Reply
        • Catalin Bombea

          November 9, 2015 at 1:52 am

          Ok, I’ll wait then 🙂
          Catalin

          Reply
  6. Pat Shikoski

    August 5, 2014 at 11:53 pm

    Pretty new to Excel and I cannot figure out why the SUM of my #s in a column does not go in the SUM column.

    Reply
    • Mynda Treacy

      August 6, 2014 at 9:45 am

      Hi Pat,

      That sounds odd. Are you able to show us the Excel workbook so we can take a look. You can send it to us via our Help Desk.

      Cheers,

      Mynda

      Reply
  7. Cherie Daniel

    May 16, 2013 at 2:21 am

    I want to make a spreadsheet that works like a checkbook. A column for date, one for description, one for add, one for subt, and one for balance. The balance column should pick up the balance brought forward and continue on from that point, adding or subtracting according to the column the figure is placed in. That way I will have a running balance of the check book that I know is accurate. It’s too easy to make a mistake when entered manually. Can you make one for me. I don’t have any other needs for excell so don’t want to take an entire course. Thanks for your feedback.

    Reply
    • Mynda Treacy

      May 16, 2013 at 8:04 pm

      Hi Cherie,

      From what you’ve described you don’t need me to create a workbook for you. Just put your column headers in, use the running total formula in the above tutorial and start entering your data. Job done.

      Kind regards,

      Mynda.

      Reply
  8. DS

    August 20, 2012 at 1:28 pm

    I’ve been doing it the old way and hate having to update the formulas when I insert/delete rows. Thanks!

    Reply
    • Mynda Treacy

      August 20, 2012 at 6:49 pm

      🙂 You’re welcome, DS.

      Reply
  9. Jeff

    June 28, 2012 at 6:21 am

    This great, how would I do the same thing for a Pivot Table that contains a column of numbers that I want to have a running total of? By creating a measure…not sure of the calculation though

    Reply
    • Mynda Treacy

      June 29, 2012 at 10:53 pm

      Hi Jeff,

      If you have Excel 2010 you can go into your Value Field Settings and on the Show Values As tab choose ‘Running Total In’ from the list.

      Kind regards,

      Mynda.

      Reply
  10. Steve B

    May 20, 2012 at 7:39 am

    I created a checkbook in excel 2010 and i was able to figure out the running balance which basically adds, subtracts accordingly and it gives me a running balance. My question is, is there a way to display that running balance (considering it keeps changing rows with each new entry) in another cell? I am trying to create a cell near the top of the worksheet that also displays my current balance and well as within the worksheet. Any thoughts? Thanks you

    Reply
    • Mynda Treacy

      May 20, 2012 at 9:46 pm

      Hi Steve,

      Let’s say your running balance is in column C and you want to find the last value in the column. You could use this formula in cell A1, or any near the top:

      =INDEX(C1:C1000,MATCH(9.99999999999999E+307,C1:C100))

      I hope that’s what you were after. If not let me know.

      Kind regards,

      Mynda.

      Reply
  11. Vicki-Lee Harry

    January 19, 2012 at 11:03 am

    excellent help thank you so much

    Reply
    • Mynda Treacy

      January 19, 2012 at 9:02 pm

      You’re welcome, Vicki-Lee.

      Reply

Trackbacks

  1. Excel Table Running Total Formula • My Online Training Hub says:
    June 9, 2022 at 5:57 pm

    […] probably seen a running total formula like this […]

    Reply
  2. Anatomy Of An Excel 2007 Formula « Information On Computers says:
    December 6, 2010 at 4:41 am

    […] Excel Running Total Formula […]

    Reply
  3. Organizing On-Site Microsoft Excel Training Courses « Information On Computers says:
    December 2, 2010 at 3:46 am

    […] Excel Running Total Formula […]

    Reply
  4. Head Lice Blog » Blog Archive » Florida Company SmartNutra, Inc Releases Breakthrough Formula For Treating Superlice says:
    November 30, 2010 at 9:43 pm

    […] Excel Running Total Formula […]

    Reply
  5. Excel Running Total Formula | excel says:
    November 30, 2010 at 8:36 pm

    […] artykuÅ‚ na: Excel Running Total Formula a-running-total, running-balance, running-total-, total-or-running, two-quick This entry was […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.