Managing inventory in Excel can get tricky when you’re tracking multiple products, batches (lots), and different units of measure. You might receive goods in full packs but sell them individually — and then deal with damaged or adjusted quantities.

If you’ve ever tried to maintain a running total across all these variations, you know how complex it can get. That’s where a Cumulative Inventory Balance Excel formula comes in handy.

In this tutorial, you’ll learn how to calculate cumulative inventory balances in Excel using a single, dynamic formula — no helper columns, no manual grouping. This formula automatically handles Pack (Whole) and Each (Loose) units, supports product-lot grouping, and updates balances in real time as new transactions are added.

👉 Using Google Sheets instead? Check out the equivalent guide — Inventory Tracker with Unit Conversion in Google Sheets (Packs, Inner, Each)

Note: Requires Excel 365, Excel 2024, or any version that supports modern dynamic array functions such as SCAN, CHOOSECOLS, and others.

What Is a Cumulative Inventory Balance in Excel?

A cumulative inventory balance in Excel is a running total that continuously updates your stock position after every transaction — whether an item is received, issued, or adjusted.

When managing both whole and loose quantities, this balance is usually displayed as two columns:

• Whole (Pack) balance
• Loose (Each) balance

Together, they show your precise stock position at any point in time, automatically reflecting every new transaction.

Why Use a Single-Formula Approach

Dealing with multiple products, lots, or unit types (like packs and individual items) requires Excel to calculate balances dynamically for each product-lot combination.

Traditional Excel methods often rely on helper columns or multiple formulas to calculate cumulative balances, conversions, and groupings. These methods can work for smaller datasets but quickly become inefficient and hard to maintain as inventory grows.

A single-formula solution simplifies everything. It performs all calculations — including grouping by product and lot, converting between pack and each units, and generating running totals — within one dynamic array formula, making your file cleaner, faster, and easier to scale.

Data Setup for Cumulative Inventory Balance

To make this formula work correctly, structure your stock transaction data in a specific way. The setup below supports both single-unit items and multi-pack items, all handled by the same cumulative inventory balance formula.

Product Types

1. Single-Unit Items
   • Received and issued as individual pieces (e.g., Washing Machines)
2. Multi-Pack Items
   • Received in packs or boxes containing several inner units
   • Can be issued as full packs or individual items (e.g., AA Batteries, USB Flash Drives)

The formula remains the same for both cases — maintain a Unit Conversion Table so Excel can automatically convert between pack quantities and individual units.

Unit Conversion Table

Range: M2:Q50

Entering accurate data in the Unit Conversion Table is crucial — mistakes can lead to incorrect cumulative inventory results.

Columns

• Product: Must exactly match the product name used in inventory transactions (ideally unique).
• Pack Size: Must be 1 — represents one complete pack.
• Inner Units: Number of smaller boxes or strips inside a pack (enter 1 if none).
• Units per Inner: Number of individual items in each inner unit (enter 1 if no inner units).
• Units per Pack: Automatically calculated as Inner Units × Units per Inner. Use this formula in Q2:

=LET(output, O3:O50*P3:P50, VSTACK("Units per Pack", IF(output=0, "", output)))

The cumulative inventory balance formula only requires Product, Pack Size, and Units per Pack for calculation.

Tip: For products received only as individual items, enter 1 in Pack Size, Inner Units, and Units per Inner.

Handling Products with Changing Inner Units

If a product’s packaging changes over time (e.g., USB Flash Drives originally shipped with 2 inner boxes per pack, later shipped with 3):

• Create a new product entry in the Unit Conversion Table with a distinct name (e.g., “USB Flash Drives New”).
• This preserves historical data and ensures accurate cumulative balances.

Optional: For rare one-off changes, you can record adjustments in the Each Adj column (see the table below) as a positive value, but frequent packaging changes are best handled with a new product entry.

Inventory Transactions (Pack & Each) Table

Range: A2:I1000

This table records all inventory movements, including incoming stock, outgoing stock, and adjustments. Accurate entry is essential.

Column Usage

• Pack In: Number of full packs received
  • Example: USB Flash Drives come in packs of 50 units. You receive 100 units → enter 2 in Pack In.
• Each In: Loose individual units received outside full packs
  • Example: 3 extra drives received separately → enter 3 in Each In.
• Pack Out: Full packs issued/sold/dispatched (negative values)
• Each Out: Loose units issued (negative values)
• Pack Adj: Adjustments to full packs (positive or negative for corrections/damage)
• Each Adj: Adjustments to individual units (positive or negative)

Tips for Accurate Entries

• Enter incoming stock as Pack In for full packs; use Each In for loose units.
• Use Pack Adj and Each Adj for corrections, damages, or one-off adjustments.
• Keep track of lot numbers for products with multiple batches — the formula calculates cumulative balances per lot automatically.
• For packaging changes, follow the guidance in Handling Products with Changing Inner Units.

Cumulative Inventory Balance Formula

After setting up the Unit Conversion Table and Inventory Transactions Table, use this formula in cell J2:

=LET(
  data, B3:I1000,
  lCol, FILTER(M3:M50, M3:M50<>""),
  rCol, FILTER(Q3:Q50, M3:M50<>""),
  seq, SEQUENCE(ROWS(data)),
  dataS, SORT(HSTACK(data, seq), {1, 2}, {1, 1}),
  prd, CHOOSECOLS(dataS, 1),
  lot, CHOOSECOLS(dataS, 2),
  whole, CHOOSECOLS(dataS, 3)+CHOOSECOLS(dataS, 5)+CHOOSECOLS(dataS, 7),
  loose, CHOOSECOLS(dataS, 4)+CHOOSECOLS(dataS, 6)+CHOOSECOLS(dataS, 8),
  sKey, prd&lot,
  lkp, XLOOKUP(prd, lCol, rCol),
  header, HSTACK("Pack Bal", "Each Bal"),
  units, SCAN(0, seq, LAMBDA(acc,val,
    LET(
      capture, LAMBDA(col, INDEX(col, val)),
      capturePv, LAMBDA(col, CHOOSEROWS(INDEX(col, val-1), 1)),
      key, capture(sKey),
      balqty, capture(whole*lkp+loose),
      keyPrv, capturePv(sKey),
      IF(key <> keyPrv, balqty, acc + balqty)
    )
  )),
  wBal, INT(units/lkp),
  lBal, MOD(units, lkp),
  VSTACK(header, SORTBY(IFNA(HSTACK(wBal, lBal),""), CHOOSECOLS(dataS, -1), 1))
)

Notes

• The formula is set up for up to 1,000 rows in the Inventory Transactions table and 50 rows in the Unit Conversion Table. Adjust ranges as needed.
• For very large datasets:
  • Move older data from the Inventory Transactions table to another sheet and paste it as values to preserve results.
  • Clear the main Inventory Transactions table.
  • Start fresh with the closing balances as the new opening balances in row 3 onward.

Step-by-Step Formula Breakdown

The formula may look complex at first, but it’s designed to calculate running balances for Pack (whole) and Each (loose) units in a single dynamic step. Let’s break it down:

1. Defining Your Data

data, B3:I1000

• This is your Inventory Transactions (Pack & Each) Table.
• It includes all Pack In, Each In, Pack Out, Each Out, and Adjustments.
• The Date column is excluded because it doesn’t affect the calculations.

2. Unit Conversion

lCol, FILTER(M3:M50, M3:M50<>"")
rCol, FILTER(Q3:Q50, M3:M50<>"")
lkp, XLOOKUP(prd, lCol, rCol)

• lCol and rCol point to the Unit Conversion Table: Product names and Units per Pack.
• XLOOKUP fetches the units per pack for each product so we can convert Pack quantities into Each units.

3. Sorting and Sequencing

seq, SEQUENCE(ROWS(data))
dataS, SORT(HSTACK(data, seq), {1, 2}, {1, 1}),

• SEQUENCE generates row numbers for cumulative calculation.
• HSTACK adds these row numbers to your data.
• SORT arranges data by Product and Lot, ensuring cumulative calculations are grouped correctly.

4. Extracting Columns

prd, CHOOSECOLS(dataS, 1)
lot, CHOOSECOLS(dataS, 2)
whole, CHOOSECOLS(dataS, 3)+CHOOSECOLS(dataS, 5)+CHOOSECOLS(dataS, 7)
loose, CHOOSECOLS(dataS, 4)+CHOOSECOLS(dataS, 6)+CHOOSECOLS(dataS, 8)

• prd and lot store product and lot identifiers.
• whole sums Pack In, Pack Out, and Pack Adjustments.
• loose sums Each In, Each Out, and Each Adjustments.
• This ensures all stock movements are considered in one step.

5. Creating a Unique Key for Each Product-Lot

sKey, prd&lot

• Combines Product + Lot to create a unique key.
• Helps the formula know when to reset the cumulative balance for a new product or lot.

6. Calculating Running Totals

units, SCAN(0, seq, LAMBDA(acc,val,
    LET(
      capture, LAMBDA(col, INDEX(col, val)),
      capturePv, LAMBDA(col, CHOOSEROWS(INDEX(col, val-1), 1)),
      key, capture(sKey),
      balqty, capture(whole*lkp+loose),
      keyPrv, capturePv(sKey),
      IF(key <> keyPrv, balqty, acc + balqty)
    )
  ))

• SCAN calculates a cumulative running total row by row.
• acc stores the accumulated balance.
• When the product-lot changes (key <> keyPrv), the balance resets.
• Converts all Pack quantities to Each using lkp and adds loose units for the total stock per row.

👉 Related: Reset SCAN by Another Column in Google Sheets and Excel

7. Converting Back to Pack & Each

wBal, INT(units/lkp)
lBal, MOD(units, lkp)

• INT(units/lkp) gives whole packs remaining.
• MOD(units, lkp) gives loose units remaining.

8. Creating the Output Table

header, HSTACK("Pack Bal", "Each Bal")
VSTACK(header, SORTBY(IFNA(HSTACK(wBal, lBal),""), CHOOSECOLS(dataS, -1), 1))

• Adds a header row: Pack Bal and Each Bal.
• Combines the calculated balances with the original data order.
• Returns a dynamic table showing cumulative balances for each product and lot.

FAQ

1. Can I use this formula in older Excel versions?
Requires modern functions (SCAN, XLOOKUP, CHOOSECOLS, LAMBDA). Works best in Excel 365 or Excel 2024.

2. How do I handle products without inner units or packs?
Set:

• Pack Size = 1
• Inner Units = 1
• Units per Inner = 1
  Formula treats these as standard individual items.

3. Can I track multiple lots for the same product?
Yes. The formula automatically groups by Product and Lot.

4. Do I need helper columns?
No. Everything — pack conversion, grouping, running totals — is handled in a single formula.

5. What if pack size or unit conversion changes?
Add a new product entry (e.g., “USB Flash Drive New”) instead of overwriting existing data to preserve historical balances.

6. How to improve formula performance?

• Use smaller ranges (e.g., B3:I1000).
• Paste part of the table as values in another sheet and use closing balances as opening balances.

7. Can this be used in Google Sheets?
Not directly. Requires adaptation using ARRAYFORMULA, SORT, and adjusted syntax.

Download the Example Excel File

Try the formula with this editable Excel file:

Download Excel File

Related Resources

• Running Total Array Formula in Excel [Formula Options]
• Running Total By Month in Excel
• Excel: Calculating Cumulative Sum by Group
• Limit Sales Quantity Within Stock in Google Sheets
• How to Calculate Running Balance in Google Sheets (SUMIF and SCAN Solutions)