Inventory Management Excel

Excel is where most businesses start with inventory tracking. It's free, flexible, and already open on every computer. For a single-location business with one sales channel, a small catalog, and no concurrent users editing the same file, an Excel inventory tracker is a reasonable solution.

This page covers how to build and use one effectively — the core spreadsheet structure, the key formulas, how to integrate a barcode scanner, and an honest account of where Excel inventory management breaks down and what to do about it.

Core Structure of an Excel Inventory Tracker

A functional Excel inventory tracker requires at least two sheets:

Products sheet — one row per SKU with columns for:

• SKU / Item Code
• Product Name
• Description
• Unit of Measure
• Reorder Point
• Supplier
• Unit Cost
• Current Quantity on Hand

Transactions sheet — one row per inventory movement with columns for:

• Date
• SKU
• Transaction Type (Received / Sold / Adjusted / Returned)
• Quantity (positive for in, negative for out)
• Reference (order number, PO number, or note)

The Products sheet is your master record. The Transactions sheet is your ledger. Current quantity on hand is calculated from the transactions, not stored as a static number — this prevents the "someone typed the wrong number" problem.

Key Excel Formulas for Inventory Management

SUMIF — calculating current quantity from transactions

This is the most important formula in an Excel inventory tracker. In your Products sheet, use `SUMIF` to sum all transaction quantities for each SKU:

```

SUMIF(Transactions!B:B, A2, Transactions!D:D)

```

Where column B in Transactions is your SKU column, A2 is the current SKU in your Products sheet, and column D is your Quantity column. This sums every transaction for that SKU — receipts add, sales subtract — giving you a live on-hand count.

VLOOKUP — looking up product details from a SKU

When recording transactions (especially with a barcode scanner), use `VLOOKUP` to auto-populate product name and other details when a SKU is entered:

```

VLOOKUP(B2, Products!A:H, 2, FALSE)

```

This looks up the value in B2 (your SKU entry) in the Products sheet and returns the value from column 2 (Product Name). Change the column index to retrieve other fields (3 for description, 7 for unit cost, etc.).

Conditional formatting — low stock alerts

Highlight cells in your Products sheet when quantity on hand falls below reorder point:

1. Select your Quantity on Hand column
2. Home → Conditional Formatting → New Rule → "Use a formula"
3. Formula: `=C2<E2` (where C2 is Quantity on Hand and E2 is Reorder Point)
4. Set fill color to red or yellow

This gives you a visual low-stock alert without needing to scan every row.

IFERROR — handling missing SKUs cleanly

Wrap VLOOKUP in IFERROR to avoid error values when a SKU isn't found:

```

IFERROR(VLOOKUP(B2, Products!A:H, 2, FALSE), "SKU not found")

```

Using a Barcode Scanner with Excel

Most USB barcode scanners work as keyboard emulators — they "type" the barcode value into whatever cell is active, then send a carriage return (Enter) to move to the next row. No drivers required. Plug in the scanner, click on a cell, and scan.

Basic scan-to-lookup workflow:

1. Create a simple transaction entry form with columns: Date | SKU | Product Name | Type | Quantity
2. In the SKU column, scan the barcode — it auto-populates the cell and advances
3. In the Product Name column, use `VLOOKUP(B2, Products!A:H, 2, FALSE)` to auto-fill the product name from your Products sheet
4. Enter the transaction type (Received / Sold) and quantity manually

Scan-to-decrement (selling):

For point-of-sale style use where scanning an item records a sale:

1. Create a "Sales Entry" sheet with: Scan SKU | Product Name | Qty Sold | Date
2. Scan populates SKU; VLOOKUP fills product name
3. Each row becomes a transaction that your SUMIF formula picks up automatically

Bluetooth and wireless scanners:

Most Bluetooth barcode scanners pair as HID (keyboard) devices and work identically to USB scanners. Range is typically 30–100 feet from the receiver. For warehouse use, a corded scanner at a fixed packing station is usually more reliable than wireless.

Where Excel Inventory Management Breaks Down

Excel works for inventory until it doesn't — and the breaking points are predictable.

Multiple sales channels. If you sell on Shopify and Amazon, your Excel file doesn't know about either. After every sale on every channel, someone must manually update the spreadsheet. At 20 orders a day across two channels, that's manageable. At 100 orders across three channels, it becomes a full-time job — and you'll oversell before you finish updating.

Multiple concurrent users. Excel files aren't built for simultaneous editing. Two warehouse staff updating the same file at the same time will produce conflicts. Even with SharePoint or Google Sheets, you're one network hiccup away from an overwrite.

Kitting and bundles. If you sell a "Starter Kit" containing three individual products that you also sell separately, Excel has no native way to calculate kit availability from component inventory. You're either manually tracking it or accepting that your kit quantity is wrong.

Purchase orders and receiving. You can build a PO template in Excel, but it won't update your inventory automatically when stock arrives. Someone has to open the file, find the right SKUs, and update quantities — and if they receive a partial shipment, you need to track what's outstanding manually.

Inventory commitment. When an order comes in, Excel doesn't "commit" that stock. It just sits in the same available quantity until someone manually decrements it. If two orders arrive before you update, you've oversold.

Barcode receiving at scale. Receiving 50-line purchase orders by scanning into Excel is slow. Each scan requires manually entering a quantity and verifying the SKU matches your PO. At volume, this takes hours that a purpose-built receiving workflow handles in minutes.

Audit trail. Who changed what quantity, and when? Excel has no native inventory adjustment log. You can build one in the Transactions sheet, but enforcing discipline across multiple users is difficult.

When to Move to Dedicated Inventory Software

The clearest signals that Excel has become the bottleneck:

• You're selling on more than one channel and updating quantities manually after each sale
• You have more than one person who needs to edit inventory simultaneously
• You've had an oversell because two channels both sold the same stock before you could update
• You're managing purchase orders from multiple suppliers and tracking outstanding quantities in a separate spreadsheet
• You sell kits or bundles and can't accurately calculate how many you can assemble
• You receive inventory against purchase orders and the receiving process takes more than 15 minutes per shipment

None of these are Excel's fault — it was designed for data analysis, not operational inventory management. The right tool for the job is different.

How Ordoro Handles What Excel Can't

Ordoro is inventory management software built for ecommerce sellers who've hit the Excel ceiling. The capabilities that replace each Excel limitation:

Multichannel sync. Ordoro connects to Shopify, Amazon, WooCommerce, BigCommerce, eBay, Etsy, Walmart, and others. When a sale comes in on any channel, Ordoro decrements the shared inventory count and writes the updated quantity back to every other channel automatically. No manual updates after each sale. See: How Ordoro syncs inventory across channels | Watch: How Inventory Sync Works in Ordoro

Inventory commitment. Ordoro tracks three inventory states per SKU — Physical On Hand (POH), Committed to open orders (CMT), and Available On Hand (AOH = POH minus CMT). When an order arrives, inventory commits immediately — before the writeback to other channels happens. The same units can't be sold twice.

Kitting at the component level. Define a kit by its component SKUs and quantities. Ordoro calculates kit availability from live component inventory. When a kit sells, component quantities decrement automatically — and kit availability across all channels updates on the next sync. See: How kitting works in Ordoro | Watch: How to Set Up Kits in Ordoro

Purchase orders with barcode receiving. Create a PO against a supplier, email it directly from Ordoro, and receive inventory against it by scanning barcodes as stock arrives. Quantities update automatically on receipt. Partial receipts track the outstanding balance. See: How to receive items from a PO

Barcode pick/pack verification. When fulfilling orders, warehouse staff scan each item before the label prints. If the wrong item is scanned, Ordoro flags it — preventing the mispick from becoming a shipped error. This runs in Ordoro without a separate scanning system. Watch: Reduce Shipping Errors with Ordoro's Barcode Packing Workflow

Audit trail. Every inventory adjustment in Ordoro is logged with a timestamp, user, quantity change, and reason code. The full inventory history for any SKU is available on demand.

Multi-warehouse tracking. If your stock is split across a warehouse, a 3PL, and a retail location, Ordoro tracks each location independently. Each warehouse maps to a Shopify Location or equivalent in your connected channels. See: How to transfer inventory between warehouses

Frequently Asked Questions

Can Excel be used for inventory management?

Yes, for a single-channel, single-location business with one person managing inventory. Build a Products sheet with SUMIF-calculated on-hand quantities and a Transactions sheet as your ledger. The model works until you add a second channel, a second warehouse, or a second person editing the file simultaneously.

What is the best Excel formula for inventory management?

`SUMIF` to calculate current on-hand from a transactions ledger, `VLOOKUP` to look up product details from a SKU (useful with barcode scanners), and conditional formatting rules to flag items below reorder point. These three cover most single-location tracking needs.

How do I use a barcode scanner with Excel?

Most USB barcode scanners work as keyboard emulators — plug in and scan directly into a cell. Use `VLOOKUP` in adjacent cells to auto-populate product name and other fields from your Products sheet when a barcode is scanned into the SKU column. Wireless and Bluetooth scanners pair as HID devices and work the same way.

What is an Excel inventory management template?

A pre-built spreadsheet with the Products sheet structure, Transactions ledger, SUMIF formulas, and conditional formatting already set up. Useful as a starting point, though you'll need to customize column names, formula ranges, and reorder thresholds for your specific catalog.

What are the limitations of Excel for inventory management?

No automatic sync to sales channels (manual updates required after each sale), no inventory commitment (same stock can be sold twice before you update), no native kit/bundle calculation, no barcode receiving against purchase orders, difficulty with concurrent users editing simultaneously, and no audit trail enforcement. These limitations become operational bottlenecks once you're selling across multiple channels at meaningful volume.

When should I switch from Excel to dedicated inventory software?

When you're selling on more than one channel and manually updating quantities, when you've had an oversell, when you're managing POs from multiple suppliers, or when you sell kits and can't accurately track component availability. At that point, the time spent maintaining the spreadsheet costs more than the software that replaces it.

How does Ordoro compare to Excel for inventory management?

Ordoro replaces the manual update loop that Excel requires: inventory decrements automatically on sale, writes back to every connected channel, commits stock immediately on order receipt, and calculates kit availability from live components. For multichannel ecommerce sellers, it eliminates the core workflows that Excel handles slowly — multichannel sync, PO receiving, kitting, and barcode pick verification.