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

Data Analysis

  • Excel Frequency Function Example
  • How To Create Frequency Distribution in Excel
  • Subtotal function in Excel
  • How to add Trendline to a chart in Excel
  • How To Remove Duplicates In Excel Column Or Row?

References

  • How to get last row in mixed data with blanks in Excel
  • Basic INDEX MATCH approximate in Excel
  • How to use Excel TRANSPOSE Function
  • Extract data with helper column in Excel
  • Find closest match in Excel

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Data validation must not exist in list
  • Excel Data validation only dates between
  • Excel Data validation must begin with

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 generate random date between two dates 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, AND, OR and NOT Functions Examples in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert date to month and year in Excel
  • Basic timesheet formula with breaks in Excel
  • Count day of week between dates in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel

General

  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to count total number of cells in a rectangular range in Excel
  • Customize Ribbon In Excel
  • Index and match on multiple columns in Excel
  • Convert column letter to number in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning