Inventory Spreadsheet Examples: 7 Layouts, Free Download

The inventory spreadsheet examples worth copying are small and do one job each, because one giant sheet that tries to do everything ends up doing nothing reliably. Which layout you need depends on the question you're asking: what to reorder, what's on hand, what's missing, what expires soon, what a batch cost, when to order, or what each batch made and sold. Below are seven examples, each filled in for the same small kitchen, with its columns, the formula that does the work, and what it can't tell you. All seven are in one free workbook that works in Excel, Google Sheets, LibreOffice and Numbers, and at the end are ideas that make any inventory spreadsheet better.

The short version

• Pick the layout by the question: reorder, on hand, missing, expiring, cost, timing or batches.
• A stock list is the simplest; an in and out log is the most honest, because it adds up what happened.
• A count sheet should show what each difference is worth, not only how many.
• Keep one row per lot when dates matter, and let the sheet count the days left.
• Never type a total or an on-hand figure you could calculate.
• Seven sheets don't update each other: one batch means editing five of them.
• TaroStack is those seven sheets as one system, so one action updates them all. More below.

Which example answers which question

The examples use Windward Roots, the made-up taro business I use in these articles, which makes kūlolo and taro chips.

Example 1: a stock list

One row per item, and a status column that says Reorder for what you buy and Make for what you make, with a formula like =IF(D2<=E2,IF(B2="Made","Make","Reorder"),"OK"). It's the right first sheet. Its limit is that the on-hand column is whatever someone last typed, so it's only as current as the last count.

Example 2: an in and out log

Every delivery, batch and loss is a line with a reason, and the on-hand figure is calculated, never typed: =H2+SUMIFS(C:C,B:B,G2)-SUMIFS(D:D,B:B,G2). This is the most honest layout, because the number on the right is the sum of what happened. Its limit is typing: one batch of kūlolo takes taro, coconut milk, sugar, trays and labels, so it's five lines every time.

Example 3: a count sheet

The worth column is the point. Forty missing labels look alarming and cost $2.40; two missing bags of chips look like nothing and are the biggest loss on the sheet. Chase the money, not the count. How to run the count itself is in inventory count sheet example.

Example 4: lots and dates

One row per lot, not per item, because two lots of the same thing have two dates. Days left is =D2-TODAY(), and the status flags anything under a week. Sort by days left and the top of the sheet is what to use or sell first. More formulas for dates are in Excel formula to track expiration dates.

Example 5: recipe cost

The batch is costed, then divided by the packs that came out good, not the 48 the recipe hoped for. This example leaves out labor and overhead; add them before setting a price. A fuller version is in recipe costing template for Google Sheets.

Example 6: reorder points and days of cover

The reorder point is daily use × (lead time + safety days), and days of cover is on hand ÷ daily use. Days of cover is the more useful number: coconut milk with 4.9 days left and a 7-day lead time is already late. How to set each number is in reorder point example.

Example 7: batches

One row per batch: what it made, what's sold, what's left and which ingredient lots went in. The 8 and 6 left of kūlolo add up to the 14 on the stock list, which is the kind of check that tells you the sheets still agree. For the full trace, from a supplier's lot to the customers who got it, see lot tracking spreadsheet.

Ideas that make any inventory spreadsheet better

• One row per lot when dates or recalls matter; one row per item when they don't.
• A unit column, and the same unit every time: kilograms or pounds, never both for one item.
• Dropdowns for item names and reasons, so "Coconut Milk" and "coconut milk" don't become two items.
• Calculate, don't type: on hand, totals, days left and status should all be formulas.
• A reason and a date on every change, so a difference can be explained later.
• Shade the cells people type in and leave formulas white, so nobody types over a formula.
• Freeze the header row and keep each sheet to one job.
• Color the flags: red for Reorder and Expired, amber for Use soon.

Get the workbook

Download the inventory spreadsheet examples. Seven tabs, one per example, filled in for the sample kitchen; type over the examples with your own items. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.

Where this stops working

The seven sheets don't talk to each other. One batch of kūlolo means five lines in the log, two lots down and a new one added on the lots sheet, a row on the batches sheet, a new on-hand figure on the stock list, and a reorder check. Make three batches a week and receive two deliveries, and by Friday one sheet is behind the others. The count sheet's expected column is then wrong, and the differences it finds are the spreadsheet's, not the shelf's. When that happens more than once a month, the sheets have become the job. How to tell when it's time is in inventory software vs spreadsheet.

How TaroStack does it

TaroStack is those seven sheets as one system, so one action updates all of them.

One batch, every sheet at once. Recording a batch takes each ingredient off stock from the lots used, first-expired-first-out by default, notes those lots on the batch, and puts the good packs into a new lot with its best-by date, costed from what you paid. The log, the lots, the batch record, the stock and the cost all change together. Units convert: buy taro by the case, use it by the kilogram.

Counts that show the money. Counting works on a phone or a printed sheet, with no signal in the walk-in, shows what each difference is worth before you post it, and suggests what to count first.

Reordering in days of cover. The reorder list shows days of cover against each supplier's lead time and minimum, and reorder points are suggested from how stock moves. Lots that are close to their date show in your alerts before they expire.

Bring your spreadsheets: items, recipes, suppliers, customers and opening stock import from CSV, with a preview of every row before anything is saved. Stock, counts, lots, recipes, batches and the reorder list are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do I make a simple inventory spreadsheet?

Start with one row per item and five columns: item, unit, on hand, reorder point and a status formula that says Reorder when on hand falls to the reorder point. Add an in and out log when you want the on-hand figure calculated.

What should an inventory spreadsheet include?

The item, its unit, the quantity, the cost each, where it's kept, a lot and date if it expires, a reorder point, and a date and reason for every change.

Can Excel track inventory automatically?

Excel adds up what you type, but nothing changes when you sell or make something until someone types it. Formulas make the totals right; they can't make the entries happen.

What's the best layout for an inventory spreadsheet?

An in and out log with a summary, because the on-hand figure is the sum of what happened rather than a number someone typed. Add a lots sheet if anything you keep has a date.