To calculate inventory balance in Excel, start with an opening quantity and add incoming stock while subtracting outgoing stock.
A simple transaction table can show how much remains after every receipt or sale, and a few formula choices determine how well it handles multiple products and edge cases.
Set up an inventory transaction table
Use one row for each stock movement.
Put headers in row 1, then enter transactions from row 2 onward:
- A: Date
- B: Product
- C: Opening balance
- D: Stock received
- E: Stock issued or sold
- F: Closing balance
Enter the starting quantity in C2, receipts in D2, and units sold or used in E2.
In F2, calculate the closing inventory with:
=C2+D2-E2
For example, if C2 contains 40, D2 contains 15, and E2 contains 9, the result is 46 units.
This formula treats receipts and issues as nonnegative quantities entered in separate columns, which makes the movement of stock easier to review.
Calculate a running inventory balance
For a single product, carry the previous row’s closing balance into the next row as its opening balance.
In C3, enter =F2, then copy it down.
In F3, use =C3+D3-E3 and fill down the column.
For a more compact running balance, place the opening quantity in F1 or another clearly labeled cell.
If that starting quantity is in F1 and the first transaction is in row 2, use this formula in F2:
=$F$1+SUM($D$2:D2)-SUM($E$2:E2)
Copy the formula down column F.
The dollar signs keep the opening balance and the first transaction row fixed, while the final row reference expands for each transaction.
With an opening balance of 40, receipts of 15 and 10, and issues of 9 and 12, successive balances are 46 and 44.
Keep transactions in chronological order if you want the row-by-row balance to represent stock after each event.
If transactions can be entered out of order, sort the table by date and, when dates tie, by a transaction ID or timestamp.
Otherwise, the balances may be mathematically correct for the listed sequence but misleading as a timeline.
Track balances for multiple products
When the table contains several products, each row’s balance must include only movements for that product.
A simple method is to keep a separate opening-stock table and use SUMIFS to total receipts and issues through each transaction date.
For example, put product names in H2:H100 and their opening quantities in I2:I100.
With product in column B, date in A, receipts in D, and issues in E, use this formula in F2:
=XLOOKUP(B2,$H$2:$H$100,$I$2:$I$100,0)+SUMIFS($D$2:D2,$B$2:B2,B2)-SUMIFS($E$2:E2,$B$2:B2,B2)
Copy it down.
The running ranges end at the current row, so each balance includes transactions for the same product up to that point.
If transactions are chronologically ordered across the entire table, this produces the balance as of each row.
If rows are not ordered by date, sort first or use a date-bounded summary formula instead.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
For older Excel versions, use VLOOKUP when product names are in the first column of the opening-stock table: =IFERROR(VLOOKUP(B2,$H$2:$I$100,2,FALSE),0)+SUMIFS($D$2:D2,$B$2:B2,B2)-SUMIFS($E$2:E2,$B$2:B2,B2).
The SUMIFS function is available in Excel 2007 and later and in Google Sheets.
Show a balance for a chosen date
To report inventory as of a date, put the requested date in K1 and a product name in K2.
With opening products and quantities in H2:H100 and I2:I100, calculate the balance using:
=XLOOKUP(K2,$H$2:$H$100,$I$2:$I$100,0)+SUMIFS($D$2:$D$100,$B$2:$B$100,K2,$A$2:$A$100,"<="&K1)-SUMIFS($E$2:$E$100,$B$2:$B$100,K2,$A$2:$A$100,"<="&K1)
This adds receipts and subtracts issues for that product on or before the date in K1, assuming transaction dates contain no time values.
If transaction cells may include times, use a next-day exclusive boundary such as <K1+1 to include the entire selected date. Use <K1 instead when the report should exclude transactions on the selected date.
Flag negative balances and missing data
A negative result can indicate a sale was recorded before a receipt, a transaction was duplicated, or the opening quantity is too low.
To flag it, add a status column and use =IF(F2<0,"Check stock","OK").
This marks the row without hiding the calculated balance.
If negative stock is not allowed in your workflow, resolve the underlying transaction rather than replacing the result with zero.
To catch blank or misspelled product names in the opening-stock table, use an explicit lookup message.
For Excel versions with XLOOKUP, a formula such as =XLOOKUP(B2,$H$2:$H$100,$I$2:$I$100,"Product not found") can check that the name exists.
In older versions, use IFERROR around VLOOKUP.
A zero returned for an unknown product may look like a valid opening quantity, so an error message is often safer.
Apply number formatting to quantities and dates, and use data validation lists for product names when possible.
This reduces errors caused by extra spaces or inconsistent spelling.
If fractional units are allowed, keep decimal quantities; if products are counted only in whole units, format quantities appropriately and validate entries as whole numbers.
Turn the data into a maintainable report
Convert the transaction range into an Excel Table by selecting it and choosing Insert > Table.
Tables expand as rows are added, and formulas can fill down automatically.
Use descriptive column names such as Received, Issued, and Balance, then apply filters to review a product or date range.
For a compact stock summary, create a PivotTable using Product as the row field and sum of receipts and issues as value fields.
A calculated balance is opening quantity plus total receipts minus total issues; keep opening stock in the source data or join it into the summary.
A PivotTable is useful for periodic reports, while the running-balance formula is more suitable when staff need to inspect the quantity after each transaction.
In Google Sheets, the same arithmetic and SUMIFS formulas work.
Some regional settings use semicolons instead of commas as formula separators, and dates must be stored as dates rather than text for date criteria to compare reliably.
Keep a backup of the source transactions and avoid manually overwriting formula cells so the inventory history remains auditable.