Yes. A simple Excel inventory tracker needs a product list, a log of stock received and issued, and a formula that calculates current stock from those records. In Microsoft Excel for Microsoft 365 on Windows, you can build this with two worksheets and a few ordinary formulas; the key is to record each stock movement rather than repeatedly overwriting a quantity.
What a basic inventory tracker should record
Inventory tracking is a record of what you have, what changes it, and when you may need to replenish it. A small spreadsheet can answer everyday questions such as how many units are available, which items are running low, and whether a recent delivery was entered. It is not automatically an accounting system or a substitute for a detailed warehouse platform, but it can provide a useful, transparent record for a modest operation.
At minimum, each product needs a unique identifier, a clear name, an opening quantity, a current quantity, and a reorder point. A transaction log should record each receipt, sale, use, return, or other adjustment as a separate dated entry. Keeping movements in a log makes it possible to review why a balance changed instead of relying on a number that was simply typed over.
- SKU: A unique stock-keeping code, such as MUG-BLU-12. If you do not use SKUs, assign another identifier that is consistent and unique.
- Item name and category: Descriptive fields that make filtering and reviewing the list easier.
- Opening quantity: The verified starting balance when tracking begins.
- Reorder point: A practical threshold that signals when someone should review replenishment.
- Transaction details: Date, SKU, movement type, quantity, and, if useful, a reference such as a receipt number.
Set up the two worksheets
Create a workbook with sheets named Products and Transactions. On Products, enter these headers in row 1: SKU, Item, Category, Opening Stock, Reorder Point, Current Stock, and Status. Put one product on each subsequent row. For example, row 2 could contain a blue mug with SKU MUG-BLU-12, an opening balance of 12, and a reorder point of 5.
On Transactions, enter Date, SKU, Type, Quantity, and Reference in row 1. Record a receipt using the type IN and an issue using OUT. Enter quantities as positive numbers: the type determines whether the movement adds to or subtracts from stock. A transaction might say 2026-11-03, MUG-BLU-12, OUT, 4, Order 104. Do not type a minus sign for an outgoing quantity if the formula below is being used.
Use one row per movement, not one row per day or per product. If a delivery contains three different SKUs, enter three rows. This makes filtering and checking much easier. Avoid merged cells, blank rows in the middle of the data, and multiple product codes in one cell; those practices make basic sorting, formulas, and later imports less dependable.
Calculate current stock from the transaction log
With Products!A:A holding SKUs and Products!D:D holding opening quantities, and Transactions!B:B holding SKUs, Transactions!C:C holding IN or OUT, and Transactions!D:D holding positive quantities, enter this formula in Products!F2:
=D2+SUMIFS(Transactions!$D:$D,Transactions!$B:$B,$A2,Transactions!$C:$C,"IN")-SUMIFS(Transactions!$D:$D,Transactions!$B:$B,$A2,Transactions!$C:$C,"OUT")
For a mug with opening stock of 12, one IN transaction of 5, and one OUT transaction of 4, the formula returns 13: 12 + 5 − 4. Copy the formula down column F for the other products. A transaction with a misspelled SKU or a type other than IN or OUT will not be counted as intended, so use consistent codes and movement labels.
For a simple status alert, with the current balance in Products!F2 and the reorder point in Products!E2, enter this formula in Products!G2:
=IF(F2<=E2,"REORDER","OK")
If that same mug has 13 units and a reorder point of 5, the result is OK. If the balance reaches 5 or less, it returns REORDER. Copy the formula down column G. The threshold is a prompt for review, not a calculation of the ideal order quantity: demand, supplier lead time, and the cost of holding extra stock may also matter.
Make entries easier to use and review
Turn each data range into an Excel table so headers, filters, and formatting stay organized as you add rows. Select a cell in the range and choose Home > Format as Table, then confirm that the range has headers. Give each table a clear name if you plan to use structured-reference formulas later. The formulas above use ordinary cell references and whole-column ranges, so they do not depend on table names.
Reduce inconsistent transaction types by selecting the cells in the Type column and choosing Data > Data Validation. Set Allow to List and enter IN,OUT as the source. This creates a dropdown for those entries. You can also apply a consistent date format to the Date column and a number format with zero decimal places to unit quantities when you only track whole units. Excel for the web has similar table and validation tools, although the exact controls can differ by release; Excel for Mac has different menus and shortcuts, so use the corresponding Data and Home ribbon tabs rather than Windows keyboard instructions.
Filters help answer questions such as which products belong to one category or which transactions were entered on a particular date. To make low balances more visible, select the status cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then format cells containing REORDER. Keep the actual status text in the sheet as well: color alone is easy to miss and is less useful when someone prints or exports the list.
Choose sensible starting balances and reorder points
Before entering an opening balance, count the physical stock and decide exactly what counts as usable inventory. For instance, separate saleable units from damaged, expired, reserved, or customer-returned items if those distinctions affect what staff can promise. Write down the date of the count and use it as the point from which the transaction history begins. If transactions are entered for dates before that starting count, the calculated balance may double-count activity.
A reorder point is a trigger, not a universal number. A shop that sells a product quickly or waits weeks for delivery may need a higher threshold than one with slow sales and next-day replenishment. A simple starting estimate can consider expected use during supplier lead time plus a buffer for variability. Review the threshold when sales patterns, delivery times, or seasonal demand change; Excel will not choose an appropriate buffer on its own.
Keep units consistent. If a product is purchased by the case but issued by the individual item, decide whether the tracker records cases or eaches and convert every movement to that unit. For example, if a case contains 24 pieces and the tracker uses pieces, a receipt of two cases should be recorded as 48. Mixing cases and individual units without a conversion rule produces balances that look plausible but are wrong.
Daily routine and basic controls
A spreadsheet is only as useful as the entries and counts behind it. Establish who records receipts and issues, when they record them, and how corrections are handled. Enter a movement when it happens or as soon as practical, using a reference to a sales order, delivery note, or adjustment record when available. If an error is found, a correcting transaction with a clear note generally preserves a more understandable history than silently changing an old entry.
- Check that every transaction has a date, a valid SKU, a permitted type, and a positive quantity.
- Compare selected spreadsheet balances with a physical count on a regular schedule that suits the value and movement of the goods.
- Investigate differences before changing the opening balance or removing transaction rows.
- Keep a dated backup in a controlled location, especially before major edits or importing data.
- Restrict editing or sharing where appropriate; a workbook’s formulas do not prevent an authorized user from changing cells.
Physical counts are important because the calculated balance is a record of expected stock, not proof of what is on the shelf. Breakage, theft, mispicks, unrecorded use, and data-entry mistakes can create a gap. Record a count discrepancy as a documented adjustment, with a reason if known, instead of treating the formula as a replacement for checking inventory.
Common problems and practical limits
The most frequent errors are duplicate or inconsistent SKUs, misspelled movement types, quantities entered with the wrong sign, and transactions omitted altogether. A product name is not always a safe identifier: “Blue Mug” might refer to several sizes or suppliers. Use a unique code and avoid reusing a retired SKU for a different product. If an item is discontinued, mark it inactive or keep it out of the active product list rather than deleting history that explains previous movements.
Another common misconception is that a low-stock formula tells you exactly how much to order. The REORDER flag only compares the current balance with a threshold. It does not know the supplier’s minimum order, open purchase orders, expected sales, delivery delay, or cash constraints. Likewise, the tracker counts units but does not automatically establish their accounting value or calculate a defensible cost-of-goods figure. Ask an accountant or use an appropriate inventory system for accounting and valuation requirements.
One workbook may become difficult to maintain when many people edit it at once, when there are many locations, or when the business needs lot numbers, serial numbers, expiry dates, barcode workflows, or a detailed audit trail. Whole-column SUMIFS formulas are convenient for a small list, but very large transaction histories may make recalculation slower. Keep the file tidy, archive data carefully, and consider a dedicated system when the process has outgrown a manually maintained workbook.
When Excel is a good fit—and when to move on
Excel is a reasonable starting point when the catalog and transaction volume are manageable, one person or a small team can maintain the records, and occasional manual checks are acceptable. It is flexible, familiar, and easy to adapt with additional columns such as supplier, location, or product status. Its main advantage is that the process is visible: a user can inspect the movement rows and understand how a balance was reached.
A shared cloud workbook can help collaborators see the same file, but sharing does not by itself create strong inventory controls. Decide how simultaneous edits, permissions, backups, and corrections will be handled. If operations depend on real-time stock across several locations, automated sales integrations, barcode scanning, batch tracking, or reliable role-based approvals, compare inventory-management software with the cost of staff time and errors in the spreadsheet. Test any replacement with a sample of real workflows before relying on it.
Frequently Asked Questions
Can I track inventory in Excel for free? If you already have access to Excel, you can create a basic tracker without buying a separate inventory application. Excel licensing and availability depend on how you access Microsoft 365. The spreadsheet itself still requires time for data entry, checking, and backup; “free” does not mean the process is automatic.
How do I automatically subtract sold items in Excel? Add each sale or issue as an OUT transaction with the SKU and a positive quantity. The SUMIFS formula in the Products sheet totals those outgoing quantities and subtracts them from opening stock, while adding transactions marked IN. The balance changes when the transaction is entered, not merely because a sale occurred elsewhere.
Should I use one sheet or two? Two sheets are clearer for a basic tracker: Products holds one row per item and its calculated balance, while Transactions holds the movement history. Putting every sale and receipt into the product list would require overwriting or duplicating fields and makes it harder to audit how stock changed.
Can Excel warn me when stock is low? Yes. A status formula can compare current stock with a reorder point and display REORDER when the balance is at or below that threshold. Conditional formatting can make the alert easier to spot. The alert depends on accurate entries and a suitable threshold; it does not place an order automatically.
Is an Excel inventory sheet suitable for a small business? It can be, especially when the range of products and transactions is modest and the business can check physical stock and maintain the workbook consistently. If several users need simultaneous workflows, traceability by batch or serial number, or integration with sales and purchasing, a dedicated inventory system may be safer and more efficient.
Related
- Quick Search in Excel: Find Data Fast
- Superscript in Excel: Quick Key and Formatting Steps
- Quick Key to Zoom In Excel: Shortcuts and Zoom Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Subtract Time in Excel to Get Hours as Decimal HoursTo subtract an end time from a start time in Excel, put the start time in A2 and…
- How to Disable Track Changes in Excel Without Losing DataTo disable Excel’s legacy Track Changes feature in Microsoft 365 for Windows, open the workbook, go to Review…
- Can You Make a Word Document a Fillable Form in Word?Yes. In Microsoft Word for Microsoft 365 on Windows, add content controls from the Developer tab—such as text…
- Most Used Excel Keyboard Shortcuts for Everyday WorkThe most-used Excel keyboard shortcuts on Windows are Ctrl+C, Ctrl+V, and Ctrl+Z for copying, pasting, and undoing; Ctrl+Arrow…
- How to Create Inbox Rules in Outlook on Windows and WebTo create an inbox rule in Outlook, open the rules settings, choose a condition such as who sent…
- Excel Engineering Number Format ExplainedIn Excel for Microsoft 365 on Windows, an engineering number format is a custom scientific format that puts…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.