Inventory Management in Excel: A GST-Ready Stock Register

• Run inventory management in Excel as one append-only Transactions table with a reason code on every row, and calculate every balance with SUMIFS (an Excel function that adds values meeting set conditions) instead of typing it.
• Rule 56(2) of the CGST Rules requires most GST-registered businesses to account for opening balance, receipts, supplies, goods lost, stolen, destroyed, written off, or given away as gifts or free samples, and the closing balance. Businesses paying tax under the section 10 composition scheme are excluded.
• Six checks, each passing at zero, tell you every week whether the register can be trusted.
• Move to Arka Inventory as soon as a second person edits the sheet, stock spreads across godowns, or batches and expiry need tracing. This threshold is our recommendation.

Lay it out as two tables: a Transactions table where every stock movement is one row, and a Stock table where every balance is a formula over those rows. Nobody types a balance. Receipts, issues, losses and transfers go into Transactions and nowhere else.

Create the Transactions table, name it Tx, and give it these columns:

Add an Items table (SKU, description, unit, item type, reorder level). Item type holds raw material, finished good, scrap or wastage. Then add a Stock table with one row per SKU and location:

• Closing stock: =SUMIFS(Tx[Net qty],Tx[SKU],[@SKU],Tx[Location],[@Location])
• Reorder flag: =IF([@Closing]<=[@[Reorder level]],"Reorder","OK")

Set the reorder level against stock you can actually sell, as our low stock alert guide explains. Use data validation drop-downs for SKU, Location and Reason so typing errors cannot enter.

Rule 56(2) requires a registered person, other than one paying tax under section 10, to keep stock accounts showing the "opening balance, receipt, supply, goods lost, stolen, destroyed, written off or disposed of by way of gift or free sample and the balance of stock including raw materials, finished goods, scrap and wastage" (CBIC, CGST Rules 2017, Rule 56, read in November 2026).

Map the Reason column to that wording:

For a monthly report, put the month's first date in cell B1 and the reason codes in D1:O1, one per column. Then, for each SKU in A2: =SUMIFS(Tx[Net qty],Tx[SKU],$A2,Tx[Reason],D$1,Tx[Date],">="&$B$1,Tx[Date],"<="&EOMONTH($B$1,0)). Opening balance for the month is =SUMIFS(Tx[Net qty],Tx[SKU],$A2,Tx[Date],"<"&$B$1).

Three more clauses of the same rule shape the design:

• Rule 56(5)(c): keep the complete address of every premises where goods are stored, including goods in transit. Your Location codes should each map to a declared address.
• Rule 56(12): a manufacturer must keep monthly production accounts of raw materials used, goods made, and waste and by-products. Add a Production tab with month, raw material SKU, quantity used, finished good SKU, quantity made and waste quantity.
• Rule 56(8): entries "shall not be erased, effaced or overwritten", and for electronic records "a log of every entry edited or deleted shall be maintained".

They drift because balances get typed over, losses are booked as vague adjustments and entries are edited after the event. The formula is rarely the culprit.

A 2015 review by Raymond Panko of the University of Hawaii pooled audits of 85 spreadsheets and found errors in 94% of them (EuSpRIG 2015 proceedings). Those were general business spreadsheets, not stock registers or Indian firms, so read it as a warning about spreadsheets in general.

Generic Excel templates usually have no column for the events Rule 56(2) names. Add reason codes yourself.

Our recommendation for the edit-log rule: never overwrite or delete a row. Correct a mistake by posting a reversing row (same SKU and location, opposite quantity, remark "Reverses doc X") and then the right row. With no edits or deletions, there is little to log. Lock past rows with sheet protection, and ask your chartered accountant whether your setup satisfies Rule 56(8).

One quiet failure remains. A SKU typed with a trailing space is a different SKU to SUMIFS, so the movement drops out of the balance without any error message. The checks below catch it.

Run six tests every week. Each passes at zero, and any other result means the register needs fixing before anyone trusts a balance. Test each check once on a copy of the file by planting a deliberate error and confirming the cell turns non-zero.

Two functions appear below: COUNTIF counts the cells that meet a condition, and SUMPRODUCT multiplies and adds arrays, here counting how many rows fail a test.

Are any balances negative?

=COUNTIF(Stock[Closing],"<0"). Pass: 0. A fail means stock was issued before its receipt was entered, or a receipt was missed.

Does every transaction SKU exist in the Items table?

=SUMPRODUCT(--(COUNTIF(Items[SKU],Tx[SKU])=0)). Pass: 0. A fail means a typo or an unregistered item.

Are there stray spaces in SKU or Location?

=SUMPRODUCT(--(LEN(Tx[SKU])<>LEN(TRIM(Tx[SKU])))). Repeat for Location. Pass: 0 on both.

Does every row carry a valid reason code and a name?

=SUMPRODUCT(--(COUNTIF(Codes[Code],Tx[Reason])=0))+COUNTBLANK(Tx[Entered by]), where Codes is a one-column table of the twelve codes. Pass: 0. A misspelt "WRITEOFF" would otherwise change the balance yet vanish from the monthly report columns.

Do transfers net to zero?

=SUMIFS(Tx[Net qty],Tx[Reason],"TRANSFER-IN")+SUMIFS(Tx[Net qty],Tx[Reason],"TRANSFER-OUT"). Pass: 0. A fail means one side of a transfer is missing. Parking stock in the IN-TRANSIT location until the receiving godown confirms keeps both sides visible.

Does the book balance match the counted quantity?

This check runs per counted SKU, not as a single cell: for each SKU counted today, compute counted quantity minus closing stock. Pass: 0, or a difference explained by a COUNT-ADJ row dated the count date with a remark. Any unexplained difference is a fail. Our cycle counting guide gives a count schedule and adjustment rules.

Move to Arka Inventory as soon as a second person edits the sheet, stock spreads across godowns, or batches and expiry need tracing. Checks 1 to 4 failing most weeks is a further sign. These thresholds are our recommendation, not a measured limit.

We publish this page and sell inventory software, so the advice to move is not neutral.

We are cloud software for inventory, warehouse, purchase and cost management, built natively on Salesforce and aimed at small and mid-market manufacturers and wholesale distributors. We have more than 30 paying B2B customers. Here is how our plans map to the sheet's problems (as of November 2026, priced in US dollars, billed yearly; see our pricing):

Basic is $199 a month and Advance is $499 a month, which is $2,388 and $5,988 a year (our arithmetic: monthly price × 12). Enterprise pricing is flexible, so ask us for a quote. Batch, expiry and serial numbers are covered by our lot and serial tracking on Enterprise.

Onboarding with standard setup guidance and configuration help is included, and you provide your product, inventory and warehouse data. A register that passes the six checks is the cleanest data to bring (our recommendation). We publish integrations with Salesforce, QuickBooks Online and Tally. For Tally users, our Tally inventory management guide covers the stock health check, and our Salesforce inventory management guide compares the four routes to stock control inside Salesforce.

To see it on your own data, book a demo with our sales team. The 15-day free trial needs no credit card.