Inventory management in Excel: a simple setup

Inventory management in Excel works well for a small shop or café with a few hundred items and one person keeping it up to date. You need three sheets, items, stock movements and a summary, plus a reorder level for each item so you can see what to buy.

Picture a café in Hindustan Park (an example, not a real client). It buys milk, coffee beans, cups and syrups every few days, and runs out of oat milk on the busiest Saturday. A clear spreadsheet would have shown it was low on Thursday.

This guide sets up a spreadsheet that works, then shows when it's time to move to software.

Inventory management in Excel: the three sheets

Keep it to three sheets in one file. More sheets means more places for numbers to drift apart.

1. Items: one row per item, with code, name, unit, supplier, cost price and reorder level.
2. Movements: one row every time stock comes in or goes out, with date, item code, quantity in, quantity out and a note.
3. Summary: one row per item, adding up the movements to show what you have now and what to reorder.

Never type stock levels directly into the summary. Change stock only by adding a movement, so you can always see why a number changed.

If your team uses phones more than computers, build the same three sheets in Google Sheets. It's free, works on a phone, and keeps one copy that everyone sees.

Set up the items sheet

Each item needs a short code you'll use everywhere, such as MILK-OAT-1L. Codes stop "Oat milk", "oat milk 1L" and "Oatly" becoming three different items.

Add these columns:

The reorder level is the number at which you buy more. Set it to roughly what you use between ordering and delivery, plus a little extra.

Record every movement

Add a row for each delivery, sale or use. For a café, that might be one row per delivery and one row at closing time for what was used.

• Use the item code, never the name.
• Write quantities as plain numbers, in the item's unit.
• Add a note for anything unusual: "2 cartons spoiled", "returned to supplier".

Format the movements as a table, so new rows are picked up by your formulas automatically.

Build the summary

The summary adds up everything in and everything out for each code. In Excel, SUMIFS does this: total of the "in" column where the code matches, minus total of the "out" column where the code matches.

Then add a "Reorder?" column that shows YES when stock is at or below the reorder level. Use conditional formatting to turn those rows red, so they stand out on a phone screen.

Sort or filter by that column before you call suppliers. That's your shopping list.

A worked example for a café

Here is how the café's oat milk looks across the three sheets in one week.

By Thursday the summary row turns red at 6 cartons, which is the moment to call the supplier. That's the Saturday shortage avoided.

Use the same idea for anything that runs out and hurts: coffee beans, cups, syrups, dental consumables or spare parts.

The one rule that makes it work

One person owns the file, and stock changes only through the movements sheet. Everything else follows from that.

Without a single owner, two people edit two copies, and within a month neither is right. If more than one person must update it, use a shared Google Sheet instead of an emailed Excel file.

Once a month, count the real stock of your top 20 items and add an adjustment row for any difference. That keeps the spreadsheet honest.

Mistakes that break a stock sheet

Most broken spreadsheets break in the same few ways. Avoid these and yours will last much longer.

• Typing the stock number straight into the summary.
• Using names instead of codes, so one item ends up with three spellings.
• Mixing units, such as cartons one day and litres the next.
• Emailing copies back and forth instead of keeping one shared file.

Keep a backup copy of the file each week, with the date in the file name. If someone deletes a row by mistake, last week's copy saves you rebuilding it from memory.

When to move to software

A spreadsheet stops being enough at a fairly predictable point. Watch for these signs:

• You bill at a counter and want stock to go down as you bill.
• More than one person updates stock during the day.
• You keep stock in two places, such as a shop and a godown.
• You need barcodes, batches or expiry dates.
• The monthly count keeps finding big differences.

Free tools can be the next step. Zoho Inventory's free plan covers 50 orders a month for one user and one location, with paid plans from ₹999 a month billed yearly, before GST, as of November 2026 (Zoho). Our guide to free inventory management software compares the free options, and our stock management software shows what we build.

If you'd like help

Want a second pair of eyes on your stock sheet? Ask for a free audit, or WhatsApp us on +91 62918 31815.