Freeze the Top Row:
Protect the Formula Cells:
This is the heart of the register. We need to calculate the "Balance Qty" after every row.
The logic: Previous Balance + Receipts - Issues
Assuming Row 2 is your first transaction: Material Stock Register Format In Excel
However, this only works for the first row. For Row 3 and beyond, the formula must reference the previous balance.
For Cell K3 (Balance Qty): =K2 + E3 - H3
Copy this formula down the entire column.
Important Correction: Wait. If you have multiple different materials, the above formula will mix them (e.g., add Bolts to Nuts). That is wrong. Freeze the Top Row:
Solution: The SUMIFS Method for Multi-Material Registers
If you combine all materials in one sheet, you cannot simply use K2 + E3. You must calculate balance based on the specific Item Code.
Instead of a simple running balance, use this formula in Column K (Balance Qty):
=SUMIFS(E$2:E3, C$2:C3, C3) - SUMIFS(H$2:H3, C$2:C3, C3) + VLOOKUP(C3, 'Item Master'!B:F, 5, FALSE) Protect the Formula Cells:
Breakdown:
To prevent someone from typing "Five" instead of "5":
Before writing a single formula, you must define your columns. A professional Material Stock Register typically contains the following fields. We will split these into two sections: Master Data (static) and Transaction Data (dynamic).