Stock Register / Inventory Template in Excel

⬇ Download the free stock register template (Excel) — no sign-up

Last updated: 27 June 2026

A stock register in Excel tracks every item coming in and going out of your store or warehouse, giving you an accurate closing-stock balance at any point in time. This guide shows you the exact columns, formulas and sheet layout to build a functional register in under an hour — with a free template you can download and use today.

Key takeaways

Build on two sheets: a Transactions sheet (one row per movement) and a Stock Summary sheet (one row per SKU).
Use SUMIFS on the summary sheet to calculate live balances — never edit a running total by hand.
Include HSN code and GST rate columns; both are needed for GSTR-1 and ITC reconciliation.
Data Validation dropdowns on Item Code and GST Rate prevent typos that break your formulas.
The free template is ready to use in Excel 2010 and later — no macros required.

Fact box. CGST Rule 56 requires every GST-registered business to maintain accounts of stock in hand, including production, purchases, receipts and supply of goods. An Excel register covering these fields is legally sufficient — no specific software is mandated.

What columns should a stock register have?

A practical stock register for Indian SMBs needs two layers: a master item list and a transactions log.

Master item list (one row per SKU)

Column	Notes
Item Code	Alphanumeric; keep consistent across all sheets
Item Name	Short, clear description
HSN Code	4 digits (turnover ≤ ₹5 cr) or 6 digits (above ₹5 cr)
Unit of Measure	Pcs, kg, litre, box, set
GST Rate (%)	0 / 5 / 18 / 40 — current slabs from 22 Sep 2025
Reorder Level	Qty at which to place a new order
Reorder Qty	Standard order quantity
Supplier	Default supplier name or code

Transactions log (one row per movement)

Column	Notes
Date	Transaction date
Document No	GRN no. (for purchases) or invoice no. (for sales/issues)
Supplier / Customer	Name or code
Item Code	Looked up from master list
Type	IN (purchase/receipt) or OUT (sale/issue/return)
Quantity	Always positive; Type column determines direction
Unit Rate (₹)	Cost price for IN; selling price for OUT
Value (₹)	= Qty × Rate
Remarks	Optional

How do I build the Stock Summary sheet with live formulas?

Step 1 — Set up the Transactions sheet as a named Excel Table

Enter your column headers in row 1.
Click any cell in the data range → Insert → Table → My table has headers.
Rename the table to Trans (Table Design tab → Table Name box).

Step 2 — Build the Stock Summary sheet

In a new sheet, list each SKU once. Add these formula columns:

Total IN (all-time receipts):

=SUMIFS(Trans[Quantity], Trans[Item Code], [@[Item Code]], Trans[Type], "IN")

Total OUT (all-time issues):

=SUMIFS(Trans[Quantity], Trans[Item Code], [@[Item Code]], Trans[Type], "OUT")

Closing Stock (Qty):

= [@[Opening Stock]] + [@[Total IN]] - [@[Total OUT]]

Closing Stock Value:

= [@[Closing Stock Qty]] * [@[Avg Cost]]

(For avg cost, use a SUMPRODUCT formula — see the FIFO & Weighted-Average guide for detail.)

Step 3 — Add a reorder alert column

=IF([@[Closing Stock Qty]] <= [@[Reorder Level]], "REORDER", "OK")

Apply conditional formatting: Home → Conditional Formatting → Highlight Cells Rules → Text that contains "REORDER" → Red fill.

Step 4 — Lock headers and freeze panes

View → Freeze Panes → Freeze Top Row on both sheets. This keeps column headers visible when scrolling through hundreds of rows.

How do I prevent data entry errors?

Data Validation on Item Code:

On the Transactions sheet, select the Item Code column (excluding header).
Data → Data Validation → Allow: List → Source: =Master[Item Code].
This restricts entries to valid codes from your master list.

Data Validation on Type:

Allow: List → Source: IN,OUT (typed directly).

Data Validation on GST Rate:

Allow: List → Source: 0,5,18,40.

Fact box. As of 22 September 2025, India's four active GST slabs are 0%, 5%, 18% and 40%. The old 12% and 28% slabs are abolished. Your GST Rate dropdown should contain exactly these four values — any template still showing 12% or 28% is out of date.

Free stock register template

The free Excel stock register template includes:

Pre-built Transactions and Stock Summary sheets.
All SUMIFS formulas wired up.
GST Rate dropdown with the correct 2026 slabs (0/5/18/40).
Conditional formatting for reorder alerts.
Sample data to illustrate the structure.

Free to download, no sign-up.

Download the Stock Register Excel Template »

What is the difference between a stock register and a stock ledger?

In practice, Indian accountants use the terms interchangeably. In strict accounting usage:

Stock register — physical record of items: quantities in, out and on hand. Maintained by the stores or warehouse team.
Stock ledger — the financial valuation of the same: quantities × unit cost = value. Maintained by accounts.

In Excel, a well-designed workbook combines both: the transactions sheet is the register; the summary sheet with value columns is the ledger.

How do I handle purchase returns and sales returns?

Add "IN-RETURN" and "OUT-RETURN" as additional Type values:

Type value	Meaning
IN	Normal purchase / receipt
OUT	Normal sale / issue
IN-RETURN	Sales return (goods coming back in)
OUT-RETURN	Purchase return (goods going back to supplier)

Update your SUMIFS formulas accordingly:

Net IN = SUMIFS(IN) + SUMIFS(IN-RETURN) - SUMIFS(OUT-RETURN)
Net OUT = SUMIFS(OUT)

This keeps your register accurate without deleting historical rows (which breaks the audit trail).

How Ankeshan helps: Ankeshan embeds a stock register engine inside Excel that auto-populates GST rates from HSN codes and flags mismatches against your GSTR-2B purchase data — so your register and your returns stay in sync without manual cross-checking. It's launching soon; join the waitlist.

Frequently asked questions

Can I use one Excel file for multiple financial years? Yes, but keep each year's opening stock as a separate entry (Type = "OPENING") rather than merging data. Use a "FY" column to filter by year in reports and pivot tables.

How do I carry forward closing stock to the next month? You don't need to — if you use the all-time SUMIFS formula, closing stock is always calculated dynamically from all transactions. Just keep adding rows; the formulas update automatically.

Is it safe to share this Excel file with my accountant? Yes. Password-protect the formula cells (Review → Protect Sheet, select "Protect worksheet and contents of locked cells") and share a copy. Your accountant can view and add transactions without breaking the formulas.

What if I have thousands of SKUs? Excel handles up to ~1 million rows. For 5,000 SKUs with daily transactions, performance is fine. Above ~50,000 transaction rows, consider converting the SUMIFS to Power Query or use structured Tables with calculated columns for faster recalculation.

Do I need macros (VBA) for this register? No. The setup described here uses only standard Excel formulas, Tables and Data Validation. Excel 2010 and later is sufficient.

Sources

CGST Rules 2017, Rule 56 — cbic.gov.in.
CBIC GST rate notifications effective 22 September 2025 (56th GST Council).
GSTN HSN reporting requirements — gstn.org.in.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.