Warehouse inventory spreadsheet

A reliable warehouse inventory spreadsheet has four tabs with one job each: Items, Locations, Movements and Stock. People add lines to Movements and the Stock tab does the arithmetic, so nobody edits a quantity directly. A well built sheet can carry a business for a long time. A badly built one eats an hour a day and still gives you the wrong number.

This page lays out a sheet that holds up: which tabs to make, which columns each one needs, and how to stop people typing over things they should not touch. It is a column guide, not a file to download. Build it yourself in whatever spreadsheet tool you already use.

The idea: four tabs, one job each

Most broken sheets have one giant tab where everything lives. Stock, orders, notes and customer names all share rows, so one sort ruins everything.

Split the work into four tabs instead:

1. Items. One row per product you stock. Never changes during the day.
2. Locations. One row per place goods can sit. Rarely changes.
3. Movements. One row per thing that happened: received, moved, picked, adjusted. This is the tab people type into.
4. Stock. A summary that counts the movements. Nobody types here.

The key rule: people add lines to Movements, and the Stock tab does the arithmetic. Nobody edits a quantity directly. That one habit is what separates a sheet you can trust from one you cannot.

Items tab: columns

Keep the item code to letters, digits and hyphens. If your supplier's code is messy, put it in its own column and keep your own code clean.

Locations tab: columns

Include odd places too: the receiving dock, the returns corner, the damaged-goods cage. Goods that have no location code are the ones that go missing.

Movements tab: columns

This is the working tab. Keep it narrow so people can fill it fast.

A receive has only a "to" location. A pick has only a "from" location. A move has both. This is the only way a sheet can tell you where something went, not just where it is.

Stock tab: the part that does the sum

For each item and location pair, the Stock tab adds up everything received or moved in, and subtracts everything picked or moved out. Use your spreadsheet's conditional sum function rather than typing numbers.

Add two helper columns:

• Below reorder point? A simple flag, so the buyer sees it without hunting.
• Negative? A flag if any quantity falls below zero. A negative means a movement was missed or entered wrong. Treat it as a task for today, not a curiosity.

Dropdowns and locks

Most errors in a warehouse sheet are typing errors: "WIDGET-A" in one row and "Widget A" in the next. Dropdowns remove that.

• Make every item and location cell a dropdown that reads from its tab.
• Lock the Items, Locations and Stock tabs so only one named person can edit them.
• On the Movements tab, lock the header and the formula columns.
• Turn on version history, and keep a copy of the file at the end of each week.
• Never allow two people to edit the same file from two different saved copies. One live file, shared properly.

The daily mini-count tab

Add a fifth tab for a small daily count. Each morning, pick a handful of locations, count what is actually there, and write it next to what the Stock tab says.

Most of these lines will match, and that is the point. When they do not, you find the cause while memory is fresh, and you correct it with an Adjust line in Movements so the trail is complete.

An example (hypothetical)

A small pet supply distributor keeps 400 items in one room. Staff used to change quantities directly in one tab. Each month the owner found three or four items that showed stock but had none on the shelf.

They split the sheet into the tabs above, made quantities impossible to overwrite, and began a ten-location mini-count every morning. After a few weeks, most differences traced back to one thing: returns going on a shelf without a movement line. They added a "Returns" zone and a rule that nothing leaves it without a Move entry. The count differences became rarer, and the ones that remained were easy to explain.

The example is invented, but the pattern is common: the fix was a habit, not a tool.

Signs you have outgrown it

A sheet is the right tool until the cost of keeping it right is higher than the cost of replacing it. Watch for these:

• Two people need to enter movements at the same time and collide.
• Entering movements after the fact, from paper, because nobody wants to type at the dock.
• Orders arrive from more than one place and you copy them in by hand.
• You cannot answer "where is lot X" without opening three files.
• Someone has built a clever formula that only they understand.
• You are scared to change anything.

If you recognize several of these, the sheet has done its job and the next step is a first system. Until then, a disciplined sheet beats a badly chosen tool.

A note on where we stand

We think most small warehouses should keep a sheet until it hurts, and this page is written so you can do that well. If you do reach the point of choosing software, Cargohold is a one-time custom build, no per-seat, per-truck or per-client fees. It is one option among several and the sheet above helps you judge any of them.

Before you start

Build the four tabs and fill the Items and Locations tabs first. Run the sheet side by side with your old method for a week. If the two disagree, work out which is right before you trust either.

Questions people ask

How can I create a WMS in Excel?

Build four tabs: Items, Locations, Movements and Stock. People add a line to Movements for every receive, move, pick or adjustment, and the Stock tab does the arithmetic. It suits a small team, and the page lists the signs you have outgrown it.

Is there a warehouse inventory template to download?

This page is a column guide, not a file to download. It tells you which tabs and columns to build in the spreadsheet tool you already use.

What tabs should a warehouse inventory spreadsheet have?

Four: Items for one row per product, Locations for every place goods can sit, Movements for each receive, move, pick or adjustment, and Stock as a summary that counts the movements. A fifth tab can hold a daily mini-count.

Why shouldn't staff edit quantities directly?

Direct edits leave no trail and are how sheets drift from reality. Adding a line to Movements and letting the Stock tab do the sum keeps the history complete.

How do I stop typing errors in a warehouse sheet?

Make item and location cells dropdowns that read from their tabs, lock the Items, Locations and Stock tabs to one named person, and lock the header and formula columns on Movements.

What should a daily mini-count record?

The date, the location, the item code, what the sheet says, what you counted, the difference and a short cause written the same day. Correct real differences with an Adjust line so the trail stays complete.

When has a spreadsheet been outgrown?

When two people need to enter movements at the same time and collide, orders arrive from more than one place and are copied in by hand, or you cannot answer where a lot is without opening three files.