Inventory management with Excel: a complete guide

Inventory management with Excel starts with a clean list where every row is one product, with columns for SKU, quantity, and reorder point. Formulas like VLOOKUP and SUMIFS plus conditional formatting keep it up to date. For a small catalog on a single channel it works fine; with multiple channels you hit its limits.

For many webshops and sellers, inventory management with Excel is the obvious first step: a free, flexible spreadsheet you already know how to use. It works fine while your catalog stays small and clear. This guide walks you through building a workable inventory system, with the right columns and formulas, and shows you where Excel starts to hit its limits.

Setting up an inventory list in Excel

The foundation is a clean list where every row is one product. Keep it simple and consistent so your formulas and filters keep working.

1. Create columns for product name, SKU, location, quantity in stock, purchase price, and minimum stock (your reorder point). Add separate columns for incoming and outgoing quantities, and for supplier and lead time, so you know how far ahead to reorder.
2. Use one row per product and apply the same format to every SKU. A single odd entry will break your lookups.
3. Convert your list into an Excel table (Insert, Table). Formulas, formatting, and filters then extend to new rows automatically, so nothing falls out of your ranges as your catalog grows.
4. Add data validation to columns like category or supplier, so every entry is picked from a fixed list. That one setting stops the typos that quietly break your filters and lookups. Apply data filters to the header row too, so you can sort by supplier, category, or stock level in one click.
5. Calculate stock value with a column for =quantity*purchase price. To keep current stock up to date automatically, calculate it with opening stock + received - sold.
6. Flag low stock with conditional formatting (turn a row red when the quantity drops below your reorder point), or use an IF formula so a cell shows "Reorder" the moment stock falls below your minimum.

As your catalog grows, use separate tabs per product category or warehouse location, or one tab for the inventory itself and separate tabs for purchasing and sales. That keeps your main overview clean and saves you scrolling through hundreds of rows.

You don't have to build the layout from scratch. Download our free Excel inventory management template and adapt it to your own product range.

Useful Excel formulas for your inventory

A handful of formulas save you the most manual work. Use VLOOKUP to pull product details from a SKU, SUMIFS to total units sold per product, and IFERROR to hide error messages that clutter your list. A pivot table then summarizes your sales by product or by month, so you can see what moves fast and what stays on the shelf.

Alongside SUM, functions like COUNT and AVERAGE let you count your SKUs and work out your average margin, and SUMPRODUCT of stock times purchase price gives your total inventory value in a single cell. When you copy a formula down your list, lock the ranges it points to with absolute references (a dollar sign, like $A$2). Skip that and your ranges shift row by row, so your totals stop adding up without any visible error.

Add your supplier's average lead time next to each product's minimum stock. Together they give you a simple reorder signal, so you restock early enough that you never have to tell a customer an item is out. To combine data from several files, Power Query can merge them into one overview, though for most webshops that effort isn't worth it, because the real bottleneck is the lack of real-time sync, not the formulas.

Common mistakes to avoid

Excel works fine as long as you keep it tidy. Steer clear of the most common pitfalls:

• Duplicate or inconsistent entries. Decide how you write a SKU and enforce it with data validation, so the same product never shows up twice.
• Forgetting to update. Counting down by hand after every sale is easy to skip, and quickly leaves your numbers wrong.
• No backup. A spreadsheet keeps no automatic version history of its own, so save to the cloud or keep versions, so a crash doesn't wipe out your records.

Where Excel falls short

Excel is static: it knows nothing about your sales. If you sell across multiple channels like bol, Shopify, or WooCommerce, you have to retype every stock change by hand. Miss one update and you sell something that's already gone. As your volume grows, that retyping eats more time and invites mistakes.

That's the point where a tool that tracks stock automatically makes more sense. ShopLinkr syncs your inventory in real time across every channel, so you never oversell again. Your orders land in one place, you pack them with pick lists, and you print shipping labels for carriers like PostNL and DPD. Track and trace flows back to the sales channel automatically.

Calculations you would rebuild by hand in Excel come ready to use, like purchase advice per supplier and reports on revenue and margin per product.

Frequently asked questions

Can I manage my entire inventory in Excel?

For a small, clear catalog on a single channel, Excel works fine. Once you sell on several channels or your order volume climbs, manual updates become error-prone and take too much time.

Is inventory management in Excel free?

Yes. If you already have Excel, or a free alternative like Google Sheets, a stock list costs nothing. The hidden costs sit in the time manual updates take and in mistakes such as overselling.

How do I prevent errors in my inventory file?

Use data validation for entries, check your formulas with Excel's auditing tools, and update your stock at fixed moments instead of ad hoc. A second pair of eyes on your formulas catches the rest.

What are the biggest risks of inventory in Excel?

Outdated numbers and human error. Because every change is manual, your stock is only correct if everyone enters every sale and delivery right away. A single missed update leads to a double sale or a missed order.

What is the difference with inventory software?

Software works in real time, connects directly to your sales channels and carriers, and automates the pick and ship process. A spreadsheet does all of that by hand, and for each channel separately.

How do I prevent overselling with Excel?

In Excel you can only limit overselling by updating counts immediately after every sale. Preventing it entirely takes real-time inventory sync that keeps all your channels up to date automatically.

How do I avoid losing my inventory file?

Keep the file in the cloud, back it up regularly, and limit who can make changes. That keeps one bad edit from wrecking your whole list.

When should I switch to inventory software?

As soon as you connect multiple channels, work with variants or bundles, or lose time to retyping. At that point, automation pays for itself quickly.

By all means start with Excel and the free template. Outgrowing that single spreadsheet? Take a look at our guides, ask a question through support, or try ShopLinkr free for 14 days and manage your inventory and orders in one place from now on.