Free Excel template

Inventory Format in Excel: Free Template + Worked Example

An inventory format in Excel is a stock sheet with one row per item. Its columns are item code, item name, unit, opening stock, stock in, stock out, closing stock, rate and stock value. Closing stock = opening stock + stock in − stock out. An item needs reordering when its closing stock falls to its reorder level. Download our free format below: a blank sheet plus a filled example.

Download the Excel template

What is an inventory format in Excel?

An inventory format shows what stock you have, what to buy and what your stock is worth. In a factory, it covers raw material, bought-out parts, consumables and finished goods.

It works best as three sheets in one Excel file:

  • Item Master: one row per item, filled once.
  • Stock Movements: one row per GRN, issue slip, production entry or invoice.
  • Stock Summary: one row per item, with closing stock, value and reorder status by formula.

What each column in the inventory format means

Item Master columns

ColumnWhat to enterExample
Item codeOne unique code per item. Never reuse it.RM-001
Item nameName with size or grade.MS sheet 2 mm
CategoryRaw material, bought-out part, consumable, finished goods or scrap.Raw material
UnitOne unit per item, used in every entry.kg
Rate (₹)The rate you value stock at. Keep one method all year.62
Reorder levelThe stock level at which you buy again.500

Stock Movements columns

ColumnWhat to enterExample
DateThe day the material moved.03-10-2026
Document no.GRN, issue slip, production entry, invoice or challan number.GRN-0412
Item codePicked from an Item Master drop-down list.RM-001
Movement typeStock in, issue, production output, dispatch, return or adjustment.Stock in
Qty in / Qty outQuantity in the item's unit. Fill only one.2,000 in
StoreWhere it sits, if you have more than one godown.Main store
RemarksSupplier, job worker, batch or adjustment reason.Batch no.

Stock Summary columns

One row per item. Item details come from the Item Master, and opening stock from a physical count or last month's closing. Stock in, stock out, closing stock, value and status are formulas. See the difference between opening stock and closing stock.

Excel formulas for closing stock, stock value and reorder alerts

The formulas use this layout. Summary sheet: A item code, E opening, F in, G out, H closing, I rate, J value, K reorder level, L status. Movements sheet: A date, C item code, E qty in, F qty out, G store.

What is the formula for closing stock in Excel?

Closing stock: =E2+F2-G2. Stock value: =H2*I2. Copy both down, then total column J for the value of all your stock.

Totals of stock in and stock out

Stock in: =SUMIFS(Movements!E:E,Movements!C:C,A2). Stock out: =SUMIFS(Movements!F:F,Movements!C:C,A2). Pull item names from the Item Master with XLOOKUP, or VLOOKUP in older Excel.

How do I set a reorder alert in Excel?

In column L, enter =IF(H2<=K2,"Reorder","OK"). To colour the row red, go to Home → Conditional Formatting → New Rule → Use a formula, and enter =$H2<=$K2. To set the reorder level itself, see our minimum stock level guide.

Can Excel handle multiple stores or godowns?

Yes. Add a store condition to SUMIFS, for example =SUMIFS(Movements!E:E,Movements!C:C,A2,Movements!G:G,"Main store"). Log a transfer as an out from one store and an in to the other.

How do I maintain daily stock in Excel?

Enter every movement on the day it happens, with its document number. For stock on a past date, add Movements!A:A,"<="&$N$1 to SUMIFS, with the date in N1.

How to make and fill the inventory format, step by step

  1. Give each item one code. Check the Item Master before adding a new one.
  2. Fix one unit per item. If you buy sheets but issue by kg, convert to kg on entry.
  3. Take opening stock from a physical count.
  4. Log every movement the same day with its goods received note, issue slip or invoice number. Between stores, use a material transfer note.
  5. Never type over formula cells. Lock them with Review → Protect Sheet.
  6. Count physically each month. Post differences as adjustments, as our stock reconciliation guide shows.

If you also make what you stock, use our bill of materials format for each product.

When Excel stops keeping up, and where TranZact fills it in for you

Excel slips when several people edit the file, stock sits in more than one store, or closing stock stops matching the count.

TranZact is manufacturing AI software, used by 10,000+ manufacturers. Each column of this sheet maps to a feature in its inventory management.

In your Excel sheetIn TranZact
Stock in rows with GRN numbersStock in, via MRP report → indent → RFQ → PO → GRN
Issue to production rowsIssue
One item's movement historyBin card
Store / godown columnMulti-store
Reorder level and IF formulaReorder and AI stock alerts
Closing stock × rateInventory ageing & valuation
Batch numbers in remarksBatch tracking (batch-wise stock on Scale+ and above)
"At job worker" storeSub-contracting
Copying figures to your accountsOne-way sync to Tally or Zoho Books, with no double entry

Accounts stay in Tally, and operations run in TranZact. It also integrates with Busy, Gmail, Excel and Google Sheets.

The Grow plan is ₹8,333/month billed yearly for 4 users. It includes stock tracking, inventory ageing & valuation and 65+ reports. There is no free plan; see all plans and prices. TranZact goes live in 2–3 weeks, and your data stays in India on AWS servers in Mumbai.

Book a demo →

[[IMAGE {"type":"steps","title":"One month in the stock sheet","items":["Count stock to set opening balance","Log each GRN, issue and dispatch daily","SUMIFS totals stock in and out","Status flags items at reorder level","Count again and post adjustments"]}]]

Worked example: one month of stock at a small fabrication unit

This unit makes electrical panel enclosures from MS sheet and angle. All numbers are illustrative.

Table 1: Item Master

Item code and nameCategoryUnitRate / reorder level
RM-001 MS sheet 2 mmRaw materialkg₹62 / 500
RM-002 MS angle 40x40x5 mmRaw materialkg₹58 / 300
BO-001 M10 hex boltBought-out partnos₹4 / 1,000
CN-001 Welding electrode 3.15 mmConsumablekg₹180 / 20
FG-001 Electrical panel enclosureFinished goodsnos₹2,400 / 10

Table 2: A few of the month's movements

Date and documentItemMovementQty
3 Oct, GRN-0412RM-001 MS sheetStock in+2,000 kg
6 Oct, ISS-0218RM-001 MS sheetIssue to production−1,300 kg
20 Oct, PRD-0040FG-001 EnclosureProduction output+40 nos
27 Oct, INV-1187FG-001 EnclosureDispatch−36 nos

Table 3: Stock Summary at month end

ItemOpening + in − out = closingStock valueStatus
RM-001 MS sheet1,200 + 2,000 − 2,600 = 600 kg₹37,200OK
RM-002 MS angle450 + 500 − 700 = 250 kg₹14,500Reorder
BO-001 M10 bolt3,000 + 0 − 1,800 = 1,200 nos₹4,800OK
CN-001 Electrode35 + 25 − 42 = 18 kg₹3,240Reorder
FG-001 Enclosure8 + 40 − 36 = 12 nos₹28,800OK

Total stock value is ₹88,540. MS angle and welding electrodes are at or below their reorder levels, so both go on this week's purchase list.

What GST expects your stock record to show

Under CGST Rule 56(2), registered businesses other than composition taxpayers must keep a stock account. It shows opening balance, receipts, supplies and closing balance, plus goods lost, stolen, destroyed, written off, or given as gifts or free samples. It covers raw materials, finished goods, scrap and wastage. In this format, receipts are stock in and supplies are dispatches. Losses, write-offs and samples are adjustments with the reason in remarks. Confirm the details with your CA.

Common mistakes in an inventory sheet

  1. Two codes for one item. Stock splits, and neither total is right.
  2. Mixed units. Kg one day and sheets the next makes closing stock meaningless.
  3. Issues to production not logged. The sheet shows more raw material than the store holds.
  4. Job-work material marked as used. It is still your stock. Move it to an "At job worker" store with the challan number.
  5. Typing over formulas. One typed number breaks every month after it.
  6. No document number. A wrong figure can't be traced back.
  7. No physical count. Small gaps grow into big ones.

Keep the sheet, or let the system keep it

Start with the free format today and keep it honest with a monthly count. When the file needs more chasing than it saves, see your own items running in TranZact.

Don't be the bottleneck. Become the AI-Run Factory.

Book a demo →

Frequently asked questions

Is this inventory format free, and do I need to sign up?

Yes, it's free. The Excel file has a blank format and a filled example. Download it free from this page; no sign-up needed. Here, 'free' refers to the file only.

Can I track raw material and finished goods in the same sheet?

Yes. Use the Category column and filter on it. Issues to production reduce raw material, and production output adds to finished goods.

How do I track material sent to a job worker?

Material at a job worker is still your stock. Log it with the challan number and an 'At job worker' store or remark, so it isn't counted as used. In TranZact, this sits under sub-contracting.

Will this sheet work with Tally?

The sheet stands alone, and your accounts can stay in Tally. If you run operations in TranZact, it syncs to Tally one way, with no double entry.

When should a factory move from Excel to software?

When several people edit the same file, you run more than one store, or closing stock rarely matches the physical count. At that point the sheet needs more chasing than it saves.

Does TranZact have a free plan?

No. TranZact's plans are Grow, Scale+, Dominate and Enterprise. Grow is ₹8,333/month billed yearly for 4 users. Only this Excel template is free.

Don’t be the bottleneck.
Become the AI-Run Factory.

Book a demo

Born in India. Building for the world.