Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to sum a total in multiple Excel tables
  • Use Data Form to input, edit and delete records in Excel
  • How to count table columns in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Excel Frequency Function Example

References

  • How to get address of last cell in range in Excel
  • How to get first column number in range in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Count rows with at least n matching values

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Excel Data validation with conditional list
  • Excel Data validation allow uppercase only

How to create running total in an Excel Table

by

This tutorial shows illustrates a Running total in Excel Table.

To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference.

Formula

=SUM(INDEX([column],1):[@column])

Explanation

 In the example shown, the formula in F5 is:

=SUM(INDEX([Total],1):[@Total])

When copied down the column, this formula will return a running total at each row.

How this formula works

At the core, this formula has a simple pattern like this:

=SUM(first:current)

Where “first” is the first cell in the Total column, and “current” is a reference to a cell in the current row of the Total column.

To get the a reference to the first cell, we use INDEX like this:

INDEX([Total],1)

Here, the array is the entire “Total” column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value.

To get a reference to the current row, we use:

[@Total]

This is the standard structured reference syntax for “this row”.

The SUM function sums the values in the range at each row, creating a running total.

As the formula is copied down the column, the reference to the first cell doesn’t change, but the referent to the current cell changes at each row. The result is a reference that expands.

Simple expanding range

Why not use a simple expanding range like this?

=SUM($E$5:E5)

For some reason, this kind of mixed reference becomes corrupted in an Excel Table as rows are added. Using INDEX with a structured reference solves the problem.

Post navigation

Previous Post:

How to calculate project complete percentage in Excel

Next Post:

Popularly Used Excel Functions and their examples

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF with boolean logic in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Return blank if in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert Excel time to Unix time in Excel
  • Count times in a specific range in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Get days between dates in Excel

Grouping

  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel

General

  • AutoRecover file that was never saved in Excel
  • Using Existing Templates in Excel
  • Advanced Number Formats in Excel
  • Excel Ribbon Quick Overview For Beginners
  • How to test a range for numbers in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning