Inventory Tracking Spreadsheet Template: Setup and Formulas

Formulas & functions · Updated November 21, 2026

An inventory tracking spreadsheet template is a workbook for recording what you stock, where it is, and when to reorder it. In Excel for Microsoft 365 on Windows, start with columns for SKU, description, location, unit cost, opening quantity, received, issued, on-hand quantity, and reorder point; with opening quantity in G2, received in H2, and issued in I2, calculate on-hand stock in J2 with =G2+H2-I2.

What an inventory template should track

A useful template answers three everyday questions: what item is this, how many are available, and what action is needed? The best column set depends on the inventory. A small shop may need sizes and colors, while a workshop may need serial numbers or maintenance dates. Keep the first version focused on information someone will maintain reliably.

For a general-purpose sheet, use one row per distinct item or variant. A product in three sizes should usually have three records if each size has its own stock count or SKU. Suggested columns include:

  • SKU or item ID: A unique identifier, rather than a description that might be written several ways.
  • Description and category: Human-readable name and a grouping such as stationery, ingredients, or spare parts.
  • Location: Shelf, room, bin, or other place the item can be found.
  • Supplier and unit cost: Useful for purchasing and estimating the recorded value of stock.
  • Opening quantity, received, and issued: The quantities used to calculate the current balance for a simple period-based tracker.
  • On-hand quantity and reorder point: The calculated balance and the threshold at which a buyer should review replenishment.
  • Last counted and notes: The date of the most recent physical check and context for exceptions.

Choose units deliberately. A row measured in individual pieces should not sometimes be updated in cases unless the case-to-piece conversion is documented. For perishable stock, add an expiration date and consider separate rows for batches; a single total can conceal that some units expire sooner than others.

Set up the workbook in Excel

Enter the column headings in row 1 and item records starting in row 2. Keep identifiers consistent, avoid merged cells, and leave each cell to hold one value. Then select the range and choose Insert > Table. Confirm that My table has headers is selected. An Excel table makes filtering easier and usually copies a formula down the column when a new row is added.

Use clear formats rather than typing units into numeric cells. For example, store a quantity as 12 and put the unit, such as each or box, in a separate column. Format cost as currency, dates as dates, and quantities as numbers. To freeze the headings while scrolling, select cell A2 and choose View > Freeze Panes > Freeze Panes. In Excel for Mac, the Freeze Panes command is also on the View tab, though the ribbon layout can vary slightly by release.

For a basic first setup, add a few realistic sample records before entering the full list. Check that each item has a unique ID and that locations and units use consistent spellings. If information is imported from another system, inspect the result for numbers stored as text, extra spaces, and duplicate item IDs; these can make sorting and calculations misleading.

Calculate on-hand stock and value

The simple balance formula adds stock received and subtracts stock issued from the opening balance. With opening quantity in G2, received in H2, and issued in I2, enter this in J2: =G2+H2-I2. If the example row has 20 opening units, 8 received, and 5 issued, the result is 23 units. Copy the formula down the column, or enter it in the first data row of a table so Excel can fill the calculated column.

For a basic estimated stock value, multiply on-hand quantity by unit cost. With on-hand quantity in J2 and unit cost in F2, enter this in M2: =J2*F2. If J2 is 23 and F2 is $4.50, the result is $103.50. This is a simple extension for planning, not necessarily the accounting value required by an organization: costing methods, adjustments, and reporting rules can differ. Ask the organization’s accounting contact which valuation approach is appropriate.

These formulas assume that received and issued quantities are totals for the period represented by the row. They do not automatically preserve a transaction history. If you overwrite the received or issued amount next month, you also change the calculation’s basis. For ongoing use, a transaction log is generally safer.

Track stock movements with a transaction log

A transaction log records each movement on its own row instead of replacing a period total. It can include date, SKU, movement type, quantity, source or destination, reference number, and the person recording the change. A receipt of 10 units and a later issue of 3 units remain separate entries, making it easier to review how the balance changed.

For a small workbook, users can maintain the log and periodically update a separate balance sheet. The important control is to define whether quantities are entered as positive numbers with a movement type, or as signed numbers where receipts are positive and issues negative. Do not mix the two approaches. With frequent transactions, many SKUs, or multiple staff editing at once, a spreadsheet may become awkward; inventory software can record movements and permissions more systematically.

Record returns, damaged goods, samples, transfers between locations, and stock adjustments consistently. A transfer out of one location and into another changes each location’s balance even though it may not change the organization’s total. Adding a reason or reference for adjustments makes later checks more useful than an unexplained change to a quantity.

Set reorder alerts without treating them as orders

A reorder point is a review threshold, not an automatic purchase instruction. It should reflect how quickly an item is used, how long replenishment takes, and any buffer the organization chooses to hold. Those inputs vary, so a template should make the chosen threshold visible rather than implying there is one correct value for every item.

To label rows at or below their threshold, with on-hand quantity in J2 and reorder point in K2, enter this in O2: =IF(J2<=K2,"REORDER","OK"). If J2 is 4 and K2 is 5, the result is REORDER; if J2 is 8, the result is OK. The label signals that someone should review the item, supplier, and purchasing plan. It does not account by itself for pending purchase orders, supplier delays, minimum order quantities, or changing demand.

You can make alerts easier to scan with Home > Conditional Formatting > Highlight Cells Rules, or by applying a color to the status column. Keep color supplementary: the text label should still communicate the status if the workbook is printed in grayscale or viewed by someone who cannot distinguish the colors. Confirm that the rule applies to the intended table rows after adding records.

Choose the right template design

A single-sheet tracker is simple to understand and can work for a short list with infrequent movement. It is less suitable when the same item is stored in several locations or has many receipts and issues. In those cases, separate an item master from transaction records, and distinguish the item ID from the location. Otherwise, a total may look correct while stock is unavailable where it is needed.

Consider these design choices before expanding the workbook:

  • One row per SKU or per SKU-location: Use the latter when location-level availability matters.
  • Current balance or movement history: A balance-only sheet is quicker to maintain; a log gives more traceability.
  • One unit or multiple units: Separate conversion rules are needed when purchasing and issuing use different units.
  • Batch or serial tracking: Add batch, serial, or expiration fields only when those details are operationally important.
  • Who may edit: Decide who updates counts and who reviews adjustments, particularly for valuable or regulated stock.

Do not make the sheet elaborate just because more columns are possible. A field that nobody updates can create false confidence. Start with the minimum information needed to identify, locate, count, and replenish items, then add fields in response to a real process requirement.

Common spreadsheet problems and practical controls

One frequent error is treating a calculated balance as a physical count. A formula only reflects the numbers entered; it cannot confirm what is actually on the shelf. Schedule counts appropriate to the operation, record the count date, and investigate differences instead of silently replacing the calculated amount. For a large assortment, checking selected categories on a rotating schedule may be more manageable than counting everything at once, but the schedule should fit the organization’s needs.

Other common issues include duplicate IDs, negative balances, inconsistent units, and accidental edits to formulas. Use Data > Data Validation to restrict entries where a controlled list is helpful, such as movement type or location. Filters can help find blanks and unusual records. Protecting formula cells can reduce accidental changes, but worksheet protection is not a substitute for access control or a backup.

  • Keep a dated backup before major cleanup or structural changes.
  • Use a clear convention for adjustments and record a reason.
  • Check formulas after inserting columns, importing data, or copying a range.
  • Agree on who updates receipts, issues, and physical counts, and when updates are due.
  • Use version history in the storage service, if available, as a recovery aid rather than the only backup.

When a spreadsheet is enough—and when it is not

A spreadsheet is often a reasonable starting point for a small inventory with limited users, straightforward units, and modest transaction volume. It is inexpensive to create, adaptable, and easy to inspect. It can also support a temporary count, a small equipment register, or a simple list that does not need real-time coordination.

Its limits become more serious when many people edit simultaneously, stock moves between sites, orders need to connect to sales, audit trails matter, or errors have high consequences. A workbook can be copied, overwritten, or left out of date, and formulas do not enforce a complete purchasing or receiving process. Dedicated inventory software may offer user permissions, barcode workflows, transaction histories, and integrations, though features and costs depend on the product. Compare the effort to maintain the spreadsheet with the cost and setup burden of a more structured system.

Frequently Asked Questions

Can I use an inventory spreadsheet for multiple locations?
Yes. Add a location field and maintain a separate balance for each SKU-location combination, or record each movement with its source and destination. A single company-wide quantity can hide a shortage at one site when stock is sitting at another.

How do I make an inventory spreadsheet automatically update?
Formulas can calculate balances from the quantities entered, but a spreadsheet cannot know that stock moved unless someone records the movement or another system supplies it. A transaction log with consistent entries is a practical way to update the numbers while retaining a record of changes.

Should I track inventory by item name or SKU?
Use a unique SKU or item ID as the dependable key, and keep the item name as a readable description. Names can vary or be reused for similar products, while a stable ID helps prevent records from being confused.

How often should inventory be counted?
There is no universal interval. Set a schedule based on item value, movement, risk, and the consequences of a mismatch, and record when each count occurred. More frequent checks may be appropriate for fast-moving or sensitive items.

Can the spreadsheet tell me how much to order?
It can flag stock at or below a chosen reorder point, but a useful order quantity also depends on demand, incoming orders, supplier lead time, pack sizes, and purchasing constraints. Treat an alert as a prompt to review those details, not as an automatic recommendation.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.