Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteThis tutorial creates an inventory stock balance report—not a company’s formal balance sheet of assets, liabilities, and equity. It shows each item’s opening quantity, receipts, issues or sales, closing quantity, unit cost, and estimated closing value.
The core calculation is:
Closing quantity = Opening quantity + Stock in − Stock out
For a constant-cost or deliberately simplified worksheet, closing value is Closing quantity × Unit cost. The five-step workflow below uses Excel Tables and SUMIFS so new transactions continue to flow into the report.
What you will build
Create three worksheets named Items, Transactions, and Stock Balance. This separates master data, entries, and reporting instead of scattering blocks across one sheet.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
- 14" diagonal, 1366x768 resolution, HD BrightView LED, Glossy NON-TOUCH Display
| SKU | Item name | Opening qty | Stock in | Stock out | Closing qty | Unit cost | Closing value |
|---|---|---|---|---|---|---|---|
| P001 | Product A | 150 | 100 | 80 | 170 | $12 | $2,040 |
Use a unique SKU or item code. Product names can be misspelled, renamed, or duplicated for different sizes, suppliers, batches, or locations.
Before you start
- A list of SKUs, item names, and units of measure.
- Opening quantities at the beginning of the reporting period, ideally reconciled to a physical count or existing inventory record.
- Receipts or purchases, and issues, sales, transfers, or returns.
- A defined cost convention and reporting date.
Step 1: Set up the Items sheet
Enter one row per product with these columns:
| SKU | Item Name | Unit | Opening Qty | Opening Unit Cost | Reorder Level |
|---|---|---|---|---|---|
| P001 | Product A | Each | 150 | 12 | 40 |
| P002 | Product B | Each | 80 | 8 | 20 |
Select the range and choose Insert > Table (or press Ctrl+T). Name the table Items in Table Design > Table Name. Keep SKUs unique; check with =COUNTIF(Items[SKU],A2). A result other than 1 means the SKU is missing or duplicated.
Step 2: Enter opening stock
Opening stock is what is physically or systemically available at the start of your reporting period. Record the opening date, SKU, quantity, and opening cost in the Items table or a separate opening-balance table.
Do not enter the same opening quantity again as a purchase. Doing so counts it twice. If you introduce the workbook during the year, reconcile the opening figure to a count or trusted inventory record before entering new transactions.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Step 3: Record stock received
On Transactions, create a table named Transactions with these columns:
| Date | Reference | SKU | Type | Quantity | Unit Cost | Value | Location | Notes |
|---|---|---|---|---|---|---|---|---|
| 2026-10-01 | PO-1001 | P001 | IN | 100 | 12 | 1,200 | Main | Supplier receipt |
For each receipt, choose IN, enter a positive quantity and the applicable unit cost. In the Value column use:
=[@Quantity]*[@[Unit Cost]]
Apply Data > Data Validation > Allow: List to the SKU column, using the valid SKU list as the source. A drop-down reduces spelling errors; using SKU rather than item name is safer. You can display the name beside the SKU with =XLOOKUP([@SKU],Items[SKU],Items[Item Name],"Unknown SKU"). Older perpetual Excel editions may not include XLOOKUP; use =IFERROR(VLOOKUP(C2,Items!$A:$D,2,FALSE),"Unknown SKU") instead.
Rank #2
- 1.1 GHz (boost up to 2.4GHz) Intel Celeron N5030 Quad-Core
- 4GB DDR4 System Memory; 128GB Solid State Drive
- 11.6" HD (1366 x 768) Multi-Touch Display
- Combo headphone/microphone jack - Noble Wedge Lock slot - HDMI; 2 USB 3.1 Gen 1
- Windows 11 Pro
Step 4: Record stock issued or sold
Enter sales, internal issues, or other removals as OUT transactions with positive quantities. The summary subtracts them. Do not record one sale as both an OUT transaction and a separate manual deduction.
Use additional standardized types when needed:
CUSTOMER_RETURNorADJUSTMENT_INfor quantities coming back in.SUPPLIER_RETURNorADJUSTMENT_OUTfor quantities leaving stock.- A transfer-out and transfer-in pair when stock moves between locations.
Keep the transaction date as a real Excel date, not text that only looks like a date. Add a location column if the same SKU exists in more than one store or warehouse.
Step 5: Calculate the stock balance
On Stock Balance, create one row per SKU with these columns:
SKU | Item Name | Opening Qty | Stock In | Stock Out | Closing Qty | Unit Cost | Closing Value | Status
Bring in item details
Item name:
=XLOOKUP(A2,Items[SKU],Items[Item Name],"Unknown SKU")
Free tools Windows power users keep installed
One-click scans. No signup required.
Opening quantity:
=XLOOKUP(A2,Items[SKU],Items[Opening Qty],0)
Sum receipts and issues
Stock in:
=SUMIFS(Transactions[Quantity],Transactions[SKU],A2,Transactions[Type],"IN")
Stock out:
=SUMIFS(Transactions[Quantity],Transactions[SKU],A2,Transactions[Type],"OUT")
Rank #3
- 256 GB SSD of storage.
- Multitasking is easy with 16GB of RAM
- Equipped with a blazing fast Core i5 2.00 GHz processor.
Criteria must match exactly. OUT, Out, and Stock Out are different stored values unless you standardize them.
Calculate quantity and value
Closing quantity:
=C2+D2-E2
Simplified closing value:
=F2*G2
This follows the basic opening-plus-inward-minus-outward model described in ExcelDemy’s stock balance example. A fixed-range alternative is =SUMIF($C$6:$C$15,P6,$D$6:$D$15)+SUMIF($H$6:$H$15,P6,$I$6:$I$15)-SUMIF($L$6:$L$15,P6,$M$6:$M$15); absolute references stop the ranges moving when you copy the formula, but fixed ranges can silently omit later rows. Excel Tables with structured references are safer for an ongoing file.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Add useful controls
Negative-stock warning
Use:
=IF(F2<0,"CHECK: negative stock","OK")
Do not hide the problem with MAX(0,formula). A negative result can indicate a missing receipt, a mistyped quantity, duplicate or wrong SKU, incorrect opening stock, a location error, or an omitted return.
Reorder status
If Reorder Level is in the Items table, bring it into the report and use:
=IF(F2<=H2,"REORDER","OK")
Physical-count variance
Add Counted Qty and Variance columns:
=CountedQty-ClosingQty
Record an adjustment transaction to correct a difference; do not rewrite opening stock and destroy the audit trail.
As-of-date reporting
Put the report date in B1. For stock in through that date:
=SUMIFS(Transactions[Quantity],Transactions[SKU],A2,Transactions[Type],"IN",Transactions[Date],"<="&$B$1)
Rank #4
- EFFORTLESS EVERYDAY PERFORMANCE: Powered by Intel Celeron N4020 processor and Windows 11 Home system, delivering reliable, low-power efficiency for daily tasks like document editing, email, online classes, and web browsing
- 15.6-INCH FULL HD DISPLAY: Enjoy immersive visuals on the 15.6" FHD (1920x1080) anti-glare screen with micro-edge bezels. Delivers clear details and comfortable viewing for long study sessions, working on spreadsheets, and video playback
- RESPONSIVE MULTITASKING & STORAGE: Built with 4GB LPDDR4 RAM and 128GB eMMC storage for smooth daily essential use. Expand your storage by up to 1TB via the integrated TF card slot to easily store movies, photos, and working files
- ADVANCED CONNECTIVITY: Outfitted with 2x Full-Featured Type-C ports for data transfer, fast charging, and dual-monitor output, alongside 2x USB 3.2 Gen1 ports and a 3.5mm audio jack for complete peripheral compatibility
- LIGHTWEIGHT & SILENT OPERATION: Slim and portable for effortless travel or commuting. Features a 1MP HD webcam for remote meetings, 38Wh battery with 45W Type-C fast charging, and a fanless silent design for peaceful work environments.
For stock out:
=SUMIFS(Transactions[Quantity],Transactions[SKU],A2,Transactions[Type],"OUT",Transactions[Date],"<="&$B$1)
This lets you produce a month-end or year-end report instead of only a live balance.
Worked example
For SKU P001, opening stock is 150 units, stock in is 100, stock out is 80, and the stated unit cost is $12:
- Closing quantity: 150 + 100 − 80 = 170 units.
- Simplified closing value: 170 × $12 = $2,040.
The example assumes one location and unit of measure, no returns, damage, adjustments, partial units, or cost changes. It illustrates the mechanics, not an accounting policy.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How to handle changing costs
Closing quantity × Unit cost is suitable only when cost is constant or your business intentionally uses a stated standard cost. It can misstate value when purchases have different prices, freight, discounts, taxes, damage, or obsolescence.
Choose and document the method required by your business and adviser, such as fixed standard cost, specific identification, FIFO, or weighted average. A basic weighted-average calculation is:
=IFERROR(TotalAvailableCost/TotalAvailableQty,0)
For example, 10 units at $10 and 10 at $14 have a simple weighted average of $12, but that number should not be silently assumed. Quantity tracking can be correct while valuation is wrong. Keep tax-inclusive and tax-exclusive amounts, selling prices, and inventory cost distinct.
Best Value
- WINDOWS 11 | STABLE PERFORMANCE: Powered by Intel Celeron N4020 processor and Windows 11 system, this laptop delivers stable performance for everyday computing tasks. It supports web browsing, online learning, document editing, email communication, and basic office work with optimized power efficiency, providing a practical and reliable experience for essential daily use for daily use.
- 15.6” FHD IPS DISPLAY: Features a 15.6-inch Full HD IPS display with narrow bezels, offering wider viewing angles and clearer image details compared to standard panels. The improved screen-to-body ratio enhances visual experience for study, reading, document work, and video playback, making it suitable for both productivity and entertainment use.
- 4GB DDR4 + 128GB eMMC STORAGE: Equipped with 4GB DDR4 memory and 128GB eMMC storage for everyday basics such as browsing, documents, email, and online learning platforms. The built-in TF card slot supports storage expansion up to 1TB, giving you more flexibility for files, photos, videos, and daily documents. TF card not included.
- CONNECTIVITY & PORTS: Includes 1× TF card slot, 2× USB 3.2 Gen1 ports, and 2× full-featured Type-C ports (USB 3.2 Gen1). The Type-C ports support data transfer, charging, and video output, enabling flexible connection with external devices such as monitors, storage, and peripherals for daily work and study use.
- LIGHTWEIGHT DESIGN | ONLINE COMMUNICATION: Designed with a slim, portable profile, this laptop is easy to carry for school, commuting, and travel. A built-in 1MP front camera supports online classes, video meetings, remote communication, and everyday conferencing. The 3300mAh battery works with the low-power system design to support practical daily use, while thermal optimization helps maintain quieter operation during extended tasks.
Common problems and fixes
- New rows are ignored: replace fixed ranges with an Excel Table and structured references.
- Totals are zero: check that SKU, type, and date values are stored consistently and contain no trailing spaces.
- The drop-down is stale: base its source on a Table column or named range that expands with new SKUs.
- Names do not match: use SKU keys and a lookup, not manually typed names.
- Dates do not filter: convert text dates to real Excel dates and verify regional date settings.
- Quantities are wrong: check pieces versus boxes, cases, kilograms, or liters and define conversions.
- Negative stock appears: investigate sequence, omissions, duplicate SKUs, opening balances, and locations rather than forcing the result to zero.
When Excel is no longer a good fit
Excel is practical for a small, low-complexity, single-location inventory with controlled entry. Consider dedicated inventory or accounting software when you need barcode scanning, simultaneous multi-user entry, multiple warehouses, lot or serial tracking, purchase-order and sales-channel integration, automatic accounting entries, or a strong audit history. A formal stock report in systems such as TallyPrime distinguishes opening, inward, outward, and closing balances; see its stock-item documentation.
For collaboration, Microsoft’s business plans page lists Excel availability by plan. Excel sharing can reduce conflicting copies, but it does not replace approvals, reconciliation, or accounting controls.
Frequently Asked Questions
Is a stock balance sheet the same as a company balance sheet?
No. This worksheet reports inventory by item. A formal balance sheet reports total assets, liabilities, and equity at an accounting date.
What is the closing-stock formula in Excel?
Use =OpeningQty+StockIn-StockOut, or the equivalent SUMIFS formula when quantities are stored in a transaction table.
Can I track multiple warehouses?
Yes. Add Location to Items and Transactions and include it as another SUMIFS criterion.
How do I enter customer returns?
Use a standardized type such as CUSTOMER_RETURN and include it in an inward calculation, or use a signed-quantity design.
Is the closing value accounting-ready?
Not automatically. It is reliable only when the cost method, units, adjustments, tax treatment, and reconciliations match your accounting policy.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
