Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get first column number in range in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Excel Advanced Lookup using Index and Match Functions
  • Perform case-sensitive Lookup in Excel
  • Extract all partial matches in Excel

Data Analysis Examples

  • Understanding Anova in Excel
  • How to sum a total in multiple Excel tables
  • How To Filter Data in Excel
  • Get column index in Excel Table
  • How to Create Gantt Chart in Excel

Data Validation Examples

  • Excel Data validation unique values only
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain
  • Excel Data validation specific characters only
  • Excel Data validation allow weekday 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:

Worked Example:   Exact match lookup with SUMPRODUCT in Excel
=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.

Worked Example:   How to get random value from list or table in Excel

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.

Worked Example:   Sum range with INDEX in Excel

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:

Get column index in Excel Table

Next Post:

Example of COUNTIFS with variable table column in Excel

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
  • How to remove trailing slash from url in Excel
  • How to get last line in cell in Excel
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • How to get first word in Excel
  • Get day from date in Excel
  • Sum race time splits in Excel
  • Get month name from date in Excel
  • Series of dates by day
  • Add business days to date in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles