Free Inventory Spreadsheet for Excel, and How to Build One

Download the free inventory workbook

The WarehouseCalc Simple Inventory workbook is free, needs no sign-up and runs in Excel without macros.

• Items: one row per item with code, name, unit, category and a minimum level.
• Stock In and Stock Out: one row per receipt or issue, with date, item code, quantity, type and reference.
• Balances: calculated for every item, with the status OK, LOW, OUT or NEGATIVE.
• Data Checks: every row is checked. A row with a mistake (missing date, unknown item code, quantity of 0) stays visible with the reason and is left out of the balances. Two identical rows are flagged.
• Dashboard: key figures (items OK, LOW, OUT and NEGATIVE, problems, the last entry date), items that need attention, an item card with a 12-month in and out chart, and store activity per month.

Download the blank workbook (.xlsx)   Download the sample with practice data (.xlsx)

How to make an inventory spreadsheet in Excel

If you prefer to build your own, these five steps give you a spreadsheet that stays correct as it grows. The formulas below use only standard functions (IF, COUNTIF, SUMIFS, ISNUMBER) that Excel 2016 and later and Google Sheets both provide.

Step 1: Make three sheets

Name them Items, Stock In and Stock Out. Keep one row of headings at the top of each and start the data in row 2. Do not leave empty rows in between.

Step 2: List every item once, with a unique code

On Items, use these columns: A Item Code, B Item Name, C Unit, D Min Level, E Total In, F Total Out, G Balance, H Status. Give every item a short code that never changes, such as BOX-01, and one unit only (pieces, rolls, kg). Codes are what the formulas match on, so a code typed two different ways becomes two different items.

Step 3: Record every movement as a new row

On Stock In and Stock Out, use these columns: A Date, B Item Code, C Qty, D Check. Every receipt or issue gets its own row. Never overwrite an old row to change stock: correct a mistake with a new row, so the history stays.

Step 4: Check each row before it counts

In D2 of Stock In (and the same on Stock Out), enter this formula and copy it down as far as you will need rows:

A row only counts when its Check says OK. Any other text tells you what to fix, and the row is ignored until you do.

Step 5: Calculate totals, balance and status

On Items, enter these in row 2 and copy them down:

The balance is always total in minus total out, counted from the checked rows. Because nobody types a balance, it cannot drift away from the movements.

Worked example

Four items, six receipts and four issues, including three mistakes:

The Items sheet then shows:

The three rows with mistakes are not counted: the 50 cartons typed as BOX-1 are waiting for the code to be corrected. The pallet shows NEGATIVE because more were issued than received, which usually means a receipt was not recorded. Find it rather than typing over the balance.

Mistakes that make inventory spreadsheets wrong

• Typing the balance by hand. Once one balance is typed over, nobody knows which movements it includes.
• Item codes typed differently. BOX-01 and BOX-1 are different items to a formula. Use a dropdown (Data Validation, list from the Items codes) on the code columns.
• Mixing units. If cartons are received by the box and issued by the piece, the balance is meaningless. Keep one unit per item and convert before you record.
• Deleting rows to fix mistakes. Add a correcting row instead, with a reference that explains it.
• Never counting. A spreadsheet only knows what was recorded. Compare it with a physical count regularly; see cycle counting and stock record accuracy.

When a spreadsheet is no longer enough

A well-checked spreadsheet works for one store or stock room with one or a few people recording movements. Consider inventory software when several people must record at the same time, when stock sits in many locations that move between each other daily, or when you need barcode scanning. Versions of the workbook for several locations, transfers and reorder lists are planned but not available to buy yet; see Excel inventory workbooks.