Stock Register Format in Excel
One sheet per item: every receipt and issue with its reference, the balance after each entry, the value, and a flag when stock falls below the minimum.
Stock register format in Excel
- Opening stock and minimum level
- Receipts and issues with references
- Running balance worked out
- Value and below-minimum flag
Quick answer
A stock register records every receipt and issue of an item with the date, the party or department, the document reference (GRN or issue slip), the quantity in or out and the balance after each entry. Balance = Previous balance + Received − Issued. Value = Balance × Rate.
| Balance | Previous balance + Received − Issued |
|---|---|
| Value | Balance × Rate |
| References | GRN for receipts, issue slip for issues |
| Required under GST | Yes — stock account per commodity |
A stock register — also called a stock book or stock ledger — is the running record of one item: what came in, what went out and what should be on the shelf now. It is the first thing an auditor or a GST officer asks for when stock is questioned, and the basis for physical verification.
This Excel format starts from the opening stock, works out the balance after every line, values it at the rate you enter and flags the line where the balance drops below your minimum stock.
What the stock register format contains
A header for the item name, code, unit, location, opening stock and minimum stock; then one row per movement with date, particulars (supplier or department), reference number, quantity received, quantity issued, balance, rate, value and remarks. Thirty rows per sheet; copy the sheet for each item.
The balance column carries forward automatically, so a wrong entry shows up immediately as a balance that does not match the shelf.
Keeping a stock register that matches the shelf
Enter receipts from the GRN, not the supplier's invoice, and issues from a signed issue slip, not memory. Count a few items every week rather than everything once a year, and record any difference as an adjustment with a reason, never by overwriting the balance.
Under GST, a registered person must keep accounts of stock for each commodity received and supplied, including opening balance, receipts, supplies, goods lost, stolen, destroyed or written off, and closing balance.
When a spreadsheet stops being enough
One sheet per item works for a few dozen items. Beyond that, stock is updated in many places at once and a spreadsheet cannot keep them in step. In CRE8Rx ERP the stock ledger is written for you as goods are received on a GRN, issued to production and corrected through stock adjustments.
Questions people ask.
What is a stock register?
The running record of receipts, issues and balance of each item, with dates and document references. It is also called a stock book or stock ledger.
How do I make a stock register in Excel?
Columns for date, particulars, reference, received, issued, balance, rate and value, with the balance formula = previous balance + received − issued. This free format has all of them ready.
Is a stock register mandatory under GST?
Yes. Registered persons must keep an account of stock for each commodity — opening balance, receipts, supplies, losses and write-offs, and closing balance.
What is the difference between a stock register and a bin card?
A bin card sits with the goods and shows quantities only; the stock register is kept by stores or accounts and usually adds rates and values.
How often should stock be checked against the register?
Fast-moving and high-value items monthly or weekly through cycle counts; everything at least once a year in a full physical verification.
Related free tools
See all free toolsDoing this for every item, every day?
A calculator is the right tool once. When it is every item in the store, it belongs in the system that already knows the numbers.