Inventory Management in Excel: Setup, Limitations, and Alternatives

Excel is one of the most common starting points for inventory tracking. It’s already in most businesses’ software stack, and for small catalogs on a single channel, it works. 

Once stock moves across more than one channel, though, the spreadsheet can’t update itself. Every change needs manual input, and those gaps are where overselling and stockouts happen. This post covers how to manage inventory in Excel and where it reaches its limits. 

How to manage inventory in Excel 

A functional inventory management system in Excel needs more than a list of products and quantities. The structure you set up at the start determines how reliable the data stays as orders and stock movements accumulate. 

Setting up your inventory sheet 

Each row should represent one SKU. The columns that do the most work are: 

• SKU: a unique identifier for each product or variant.  
• Product name and category for filtering and reporting.  
• Unit of measure so quantities stay unambiguous across products.  
• Opening stock as a fixed starting number.  
• Stock in to record incoming purchases.  
• Stock out to record sales and other outflows.  
• Stock on hand, calculated as opening stock plus stock in minus stock out.  
• Reorder point and reorder quantity to flag when replenishment is needed. 
• Supplier and lead time so purchasing decisions have context. 

Keep the calculated columns formula-driven from the start. If stock on hand requires manual arithmetic, it will eventually be wrong. 

Tracking stock movements 

The cleanest way to maintain accurate stock on hand is to log every movement in a separate transactions tab — one row per event, with columns for date, SKU, movement type (purchase, sale, adjustment), and quantity. Your main inventory sheet then pulls from this log using SUMIF, totalling stock in and stock out per SKU automatically. 

This approach keeps the audit trail intact. The stock on hand figure updates as the log grows, rather than requiring edits to the main sheet. 

Setting reorder points 

A reorder point tells you when to place a purchase order before stock runs out. The basic formula is average daily sales multiplied by supplier lead time in days. If demand is variable, add a safety stock buffer on top. 

Once reorder points are set, conditional formatting can flag any SKU where stock on hand falls below that number, making low-stock items visible without having to scan every row manually. 

Reporting and trend visibility 

Pivot tables are the most practical tool for summarising inventory data — by category, by supplier, or by movement volume over a period. For cost of goods sold, calculate it using average cost per unit, updated as new purchase prices come in. Keep this in a separate summary tab rather than embedding it in the main sheet, so the core data stays clean. 

Limitations of Excel for inventory management 

The structure from the previous section holds up well at small scale. As the business grows, the manual upkeep makes it harder to sustain. 

Multichannel sync doesn’t happen on its own 

When you sell through Shopify and wholesale simultaneously, your spreadsheet reflects neither channel until someone manually updates it. No matter how well the spreadsheet is built, that gap between what has actually sold and what the sheet shows leaves space for overselling and stockouts. The tool has no connection to your sales channels, so it can only ever show you what someone last typed in. 

As a rough threshold, a spreadsheet stays manageable for most businesses under around 100 SKUs, selling through one channel, with one person keeping the file current. Past that point, the manual update load typically outweighs the cost advantage of staying on Excel. If stock already moves across two or more channels, that threshold arrives faster. 

When a product goes out of stock, 66% of consumers will shop from another retailer rather than wait. For a fuller look at what that lag costs week to week, [this breakdown of the real cost of spreadsheet inventory management covers it in detail. 

Accuracy degrades with more SKUs and more people 

Every additional SKU is another row to maintain. Every additional person touching the file introduces the possibility of version conflicts and overwritten formulas. There is typically one person who understands the sheet well enough to fix it when something breaks — which makes the whole system dependent on their availability. 

I didn’t know how stressed I was until I left our old system. Things would be wrong, or the Shopify sync wouldn’t update for 24 hours. 

Liz Carter, Founder 

Purchase orders become a separate tracking problem 

In Excel, PO tracking typically lives in a second tab or a separate file from the main inventory sheet. When a purchase order arrives partially, both need updating. When a supplier delivery slips, the expected stock figure needs adjusting manually. There is no automatic connection between what has been ordered and what the inventory sheet shows, and the two fall out of sync as order volume grows. 

If you make or assemble products, the picture gets harder 

For businesses with a bill of materials, raw materials and finished goods need tracking separately, then reconciling manually as production runs. The formulas connecting those two sheets are fragile: one accidental cell edit can return wrong numbers with no indication that anything has changed. 

Excel alternatives for small and medium product businesses 

The right tool depends on what the business does. 

For pure resellers on a single channel, Shopify-native inventory tools may cover enough ground. For larger operations with the budget and implementation appetite for it, an ERP handles inventory as part of a broader business system. Between those two sits a large group of product businesses — multiple channels, growing SKU counts, often some degree of assembly or manufacturing — for whom neither is built. 

Katana is built for that group. It handles real-time inventory tracking, purchase orders, sales orders, and — where relevant — manufacturing in one place. Real-time stock levels feed directly into Shopify inventory sync and Amazon inventory management, and orders across all channels are visible from a single dashboard without separate records per channel. You can read more about how to manage inventory across all your sales channels. 

Hornby Organic, a Canadian food producer selling through their online store and retail partners, managed everything across multiple spreadsheets before switching. In their words: “Katana is one integrated solution that replaces our many spreadsheets. Also, the integration with our Shopify store and accounting software was a key consideration.” After switching, they improved inventory control by 100% and cut admin time in half. 

“If I didn’t have Katana, I would be drowning in spreadsheets, which would all break at some point. Because of Katana and our pre-order methods, we can make sure our hero products are always in stock.”  

– Lisa Diep, Chief Operating Officer, Peace Collective 

Moving from Excel to Katana 

Switching inventory tools takes effort, and it’s worth going in with an accurate picture of what that involves. 

The most concrete starting point is data import. Katana’s data import accepts CSV and Excel files covering products, materials, bills of materials, stock levels, and more.  Your existing spreadsheet data transfers directly into the system, it does not need to be rebuilt. 

From there, setup typically covers products, opening stock levels, and connecting your sales channels. How long that takes depends on catalog size and how clean the source data is. 

Getting the team up to speed takes deliberate time, and Katana’s onboarding services are there to support it. The onboarding team works through the setup with you, which is particularly useful if the spreadsheet has historically been one person’s domain and others are coming to inventory management fresh. 

The free plan is a practical way to test before committing. You can bring in your own inventory data and run it alongside your existing spreadsheet until the new system has proven itself.

FAQ: Inventory management in Excel

Andreia Mendes

Andreia proves that even the most technical topics can (and should) be interesting. At Katana, she works with exclusive data from thousands of product SMBs and turns those insights into practical content for growing brands — covering everything multi-channel merchants need to know about inventory management.