Inventory management on Excel for beginners

You launch into entrepreneurship, you have a brilliant idea, products to sell… and then comes the tricky question: inventory management. Before jumping into complex, often expensive software, many of us have a simple, effective reflex: open Excel. And it’s a great idea.

Managing your inventory on Excel is the most direct and economical solution to get started, especially when you’re a sole trader or running a small business. It’s the perfect tool to get your hands dirty, understand the mechanics of your inventory, and build a system that fits you, without spending a cent on a subscription.

Why Excel remains a powerful ally for your inventory

While the market is full of specialized solutions, why does a “simple” spreadsheet like Excel remain the go-to choice for so many entrepreneurs? For a small business, the answer comes down to two words: simplicity and cost control. That’s what really matters.

Inventory management on Excel meets these needs perfectly. You already have the software, so there are no acquisition or subscription fees eating into your margin. It’s also a matter of independence: your data belongs to you, it isn’t locked into a third-party platform.

Flexibility that never lets you down

Excel’s real superpower is its flexibility. It’s almost limitless. Inventory software imposes its own structure, its own fields, its own logic. With Excel, it’s the opposite: the tool adapts to your way of working.

Need to track batch numbers? Add a new column. Want to calculate a specific margin on a product category? A quick custom formula does the trick. You can even build visual dashboards that make sense for you and your team.

Concretely, the benefits are clear:

• Full control over your data: Your information stays with you, on your computer or your cloud. You’re the boss.
• Unlimited customization: Expiry dates, warehouse locations, preferred supplier… add anything relevant to your business.
• Instant onboarding: Who doesn’t know at least a little bit of Excel? The learning curve is nearly flat, so you can get up and running right away.
• Zero subscription: The money you save every month is extra budget to buy stock, run ads, and grow your business.

Think of Excel not just as a spreadsheet, but as a blank canvas. For a beginning entrepreneur, it’s a unique chance to build a management system that grows with you, without hitting technical or financial walls.

This isn’t just a hunch. In France, managing inventory on Excel is a widespread practice, especially among small and mid-sized businesses. A 2022 Microsoft analysis found that 83% of professionals in retail and marketing use Excel to manage their data, including inventory. This shows the trust placed in it, even in the era of specialized tools. If you’re curious, you can look into the details of this analysis.

Ultimately, starting with Excel is a sensible decision. It forces you to think things through, clearly define your needs, and understand the logic behind every stock movement in and out. It’s a solid foundation that will serve you throughout your entrepreneurial journey, even the day you move to a more automated system.

Building the foundations of your inventory file

For inventory management on Excel to work well, everything starts with a clear, logical structure. Forget messy files where information is scattered. We’ll build a solid base from the start by creating three distinct tabs that communicate with each other: Products, Stock In, and Stock Out.

This three-pillar organization is really the key. It clearly separates reference data (your products, which change rarely) from movement data (transactions, which happen daily). This separation is what will let you automate your calculations later without pulling your hair out.

The Products tab: your reference catalog

This tab is the core of the system. It will contain the complete, detailed list of every item you manage. The golden rule is simple: each product is unique and must appear only once in this list. Rigor is non-negotiable here, because the cleanliness of this database determines the reliability of the entire system.

Think of this tab as the ID card for every item in your inventory.

Here are the columns I strongly recommend including:

• Product Reference (SKU): The unique identifier for each product (e.g., TSH-BL-M). There must NEVER be a duplicate. This is the key that links all your tabs together.
• Description: A clear, simple description of the product (e.g., Blue Cotton T-shirt - Size M).
• Unit purchase cost (excl. VAT): What this product actually costs you to buy. This is vital for calculating the value of your inventory.
• Alert threshold: The minimum stock level that should catch your attention and trigger a reorder. We’ll see a bit further on how to create automatic alerts with this.
• Supplier: Very handy for knowing who to call when you need to restock.

Pro tip: Never use the product name as the unique identifier. A name can change, contain typos, missing accents… A short, standardized reference (the well-known SKU) is far more robust and will save you countless errors.

Once your list is ready, select your data and use Excel’s “Format as Table” feature (in the Home tab). It’s not just for looks! It turns your data into a structured object, which makes your formulas much smarter and more dynamic. For example, they’ll automatically apply to every new row you add. A real time-saver.

The Stock In and Stock Out tabs: your logbook

These two tabs work exactly like a logbook or a ledger. Their role? Chronologically recording every stock movement. Whether it’s a delivery from your supplier or a sale to a customer, everything must go through here. Discipline is your best ally: every movement, without exception, must be logged.

For the Stock In tab:
Here, each row corresponds to a receipt of goods.

• Receipt date: The day the products physically arrived.
• Product Reference (SKU): The unique identifier of the received product.
• Quantity received: The number of units just added to stock.
• Supplier order number: Optional, but trust me, it’s very useful for linking to your purchase orders.

For the Stock Out tab:
Here, each row represents a sale or another type of outbound movement (breakage, loss, personal use…).

• Outbound date: The day of the sale or actual outbound movement.
• Product Reference (SKU): The identifier of the product leaving stock.
• Quantity out: The number of units you’re removing.
• Customer order number: Perfect for easily finding the corresponding invoice.

To help you visualize this, here’s a table summarizing the basic structure.

Tab structure for effective inventory management

This table summarizes the essential columns to include in each tab of your Excel file for optimal data organization.

Tab	Recommended Columns	Main Purpose
Products	Product Ref. (SKU), Description, Purchase cost, Alert threshold, Supplier	Create a single, reliable database of all items.
Stock In	Date, Product Ref. (SKU), Quantity received, Supplier order number	Track all stock increases chronologically.
Stock Out	Date, Product Ref. (SKU), Quantity out, Customer order number	Track all stock decreases (sales, losses, etc.).

With this organization, every type of information has its place, making everything much easier to manage and analyze.

No more typos! Make data entry reliable with drop-down lists

One of the biggest enemies of inventory management on Excel is human error. A simple typo in a product reference can throw off all your calculations and make you think a product is in stock when it isn’t. To avoid this trap, we’ll set up a simple but remarkably effective safeguard: data validation, or more simply, the drop-down list.

The idea? In your Stock In and Stock Out tabs, instead of typing the product reference by hand, you’ll pick it from a list. And this list will be generated directly from the “Product Reference” column in your Products tab. Simple, and it changes everything.

Here’s how to do it:

• In your Stock In tab, select the entire “Product Reference” column.
• Go to the Data menu, then click Data Validation.
• In the dialog box, under “Allow”, choose List.
• In the “Source” field, click the small arrow, navigate to your Products tab and select the entire references (SKU) column.

Do exactly the same thing for the “Product Reference” column in your Stock Out tab. And that’s it! It is now physically impossible to enter a reference that doesn’t exist in your catalog.

With these three well-structured, secured tabs, you’ve just laid a solid, clean foundation. You’re now ready to move on to the next step: making this data talk and automating real-time stock calculations.

Making the numbers talk: automate your inventory with the right formulas

Now that our foundations are solid and well organized, let’s move on to the most interesting part. This is where our simple Excel file will turn into a real management tool for your inventory. No more manual calculations, no more oversights and costly errors. We’re going to make it dynamic and smart.

The secret is getting Excel to work for us with a few well-chosen formulas. They’ll pull information from your Stock In and Stock Out tabs to calculate, in real time, what you actually have left in stock for each product. This is the core engine of your inventory management on Excel.

The logic is as simple as it gets: take everything that came in, subtract everything that went out, and there you have your current stock. This is the flow of information we’re now going to automate.

This image perfectly sums up our architecture: the Products database is constantly updated by the movements we record in Stock In and Stock Out.

Calculating real-time stock with SUMIFS

For this mission, our best ally is the SUMIFS formula. It’s remarkably effective. Its role? To add up numbers, but only if they meet one or more conditions you give it. In our case, we’ll ask it to calculate the sum of quantities received and sent out for one specific product reference.

The current stock calculation follows this logic:
(Total quantity received) - (Total quantity out) = Current stock

Specifically, in your Products tab, add a “Current Stock” column. For the first product in your list (let’s say row 2, with the reference in cell A2), the magic formula will be:

=SUMIFS(StockIn_Table[Quantity received]; StockIn_Table[Product Reference]; [@Product Reference]) - SUMIFS(StockOut_Table[Quantity out]; StockOut_Table[Product Reference]; [@Product Reference])

Let’s break it down together:

• The first part SUMIFS(...) tells Excel: “Go into my stock-in table, look at the ‘Quantity received’ column and add up the numbers you find there…
• …but only if the product reference on the same row matches the one on the row we’re currently on ([@Product Reference]) in the Products tab.”
• The second part of the formula does exactly the same thing for outbound stock, and then we subtract that total from the first.

My expert tip: By using table names like StockIn_Table (thanks to the “Format as Table” feature), your formulas become crystal clear. It’s much more readable than wrestling with StockIn!C2:C100, trust me!

Once this formula is entered in the first cell, the magic of Excel tables kicks in: it’s automatically copied to every row. Your stock is now alive and will update with every movement you record.

Easily retrieving product info with XLOOKUP

Now that the calculation is automated, let’s make our movement logs more practical. Wouldn’t it be nicer to display the product name directly in the Stock In and Stock Out tabs? It saves you the constant back-and-forth of checking what a reference corresponds to.

This is where the XLOOKUP function comes into play. It’s much more flexible and modern than its ancestors VLOOKUP or the INDEX/MATCH duo. Its job is simple: find a value in one column and return the info found on the same row, but in another column.

For example, in your Stock In tab, add a “Description” column. For it to fill in automatically as soon as you type a product reference, use this formula:

=XLOOKUP([@Product Reference]; Products_Table[Product Reference]; Products_Table[Description]; "Product not found")

It’s very easy to understand:

• What I’m looking for: The product reference on this row ([@Product Reference]).
• Where I’m looking for it: In the “Product Reference” column of my catalog (Products_Table[Product Reference]).
• What I want to retrieve: The information from the “Description” column (Products_Table[Description]).
• And if I don’t find it? Display the message “Product not found”. This is a great option that avoids ugly #N/A errors.

This small formula will make your tracking much more pleasant to use day to day. You can use it to pull in any info: supplier, purchase price, location…

Go further by combining formulas

The real potential is unlocked when you start combining these formulas. For example, an essential metric is the value of your stock. Nothing simpler! In the Products tab, create a new “Stock Value” column.

The formula is a simple multiplication:
=[@Current Stock] * [@Unit purchase cost (excl. VAT)]

All you have to do is add a SUM at the bottom of this column to know, at any moment, the total value of your inventory. This is crucial data for managing your cash flow.

You see, we’ve moved from a simple spreadsheet to a real dashboard. Your file no longer just tracks quantities, it gives you financial indicators to make better decisions. This is a first step toward business process automation, an approach that can revolutionize many other facets of your business.

Thanks to these few formulas, your inventory management on Excel gains in precision and autonomy. The time you save and the errors you avoid are resources you can reinvest where it really matters: growing your business.

Creating alerts to never run out of stock again

Tracking your stock in real time is good. Anticipating stockouts is even better. Your inventory management on Excel file becomes truly powerful when it starts working for you, warning you before problems happen. That’s exactly what we’re going to do now: set up a visual alert system, simple but incredibly effective.

The goal? Get Excel to flag it for you when a product reaches its critical reorder threshold. No more tedious manual scanning of your product list. At a glance, you’ll know where your emergencies are.

The magic of conditional formatting

The perfect tool for this mission is conditional formatting. As its name suggests, it applies a specific format (a background color, bold text…) to a cell only if a precise condition is met.

In our case, the condition is very simple: “Is the current stock less than or equal to our alert threshold?” If the answer is yes, we want the entire product row to turn red so it jumps out at us.

To set it up, the process is very intuitive:

• Go back to your Products tab and select all the data in your table, excluding the header row.
• In the “Home” ribbon, click Conditional Formatting, then New Rule.
• Choose the option “Use a formula to determine which cells to format”.
• This is where we enter our condition. For the first row of data (say, row 2), if your current stock is in column E and the threshold in column F, the formula will be: =$E2<=$F2.

The small $ sign in front of the column letters is crucial. It “locks” the check onto those specific columns, allowing the formatting to apply correctly to the whole row as Excel goes down your table.

Then, all that’s left is to choose the format. Click “Format…”, pick a light red fill with bold text, for example. Confirm, and that’s it!

From now on, as soon as a product drops below its safety threshold, its row will color instantly. You’ll know clearly and unmistakably where you need to act first.

The impact of anticipation on your business

This simple visual automation isn’t just a gadget. It completely changes how you manage inventory by shifting you from a reactive mode to a proactive one. The benefits are immediate: you drastically reduce the risk of running out of stock, which causes not only a direct loss of sales but also real frustration for your customers.

Anticipating is the difference between being at the mercy of your stock and being in control of it. A visual alert gives you time to order, receive the goods, and restock before your inventory hits zero.

The impact of this kind of method is, in fact, well documented. Automation, even partial through Excel, combined with centralized information, can help anticipate up to 98% of stockouts and reduce excess inventory by nearly 50%. Good organization in Excel therefore has very concrete effects on your profitability.

By setting relevant alert thresholds, you also optimize your cash flow by avoiding costly overstocking of slow-moving products. To fine-tune these thresholds, it’s essential to know your sales cycles well. You can explore different sales forecasting techniques that will help you better anticipate demand. Your Excel file then becomes a real strategic tool for balancing supply and demand.

Turning your data into strategic decisions

At this point, your inventory management on Excel file is much more than a simple list. It’s a goldmine of information just waiting to be tapped. Having clean, up-to-date data is a first win, but the real added value is your ability to make it talk in order to make truly informed decisions.

Now we’re going to turn this database into a real command center for your business. The idea is to create a simple dashboard that gives you a clear, sharp view of your performance, without having to write a single more complex formula.

The power of pivot tables

To analyze our stock data, our tool of choice will be the pivot table. It’s arguably one of Excel’s most powerful yet accessible features. It lets you summarize, group, and analyze large amounts of data in just a few clicks.

Forget lengthy formulas to answer simple business questions. The pivot table does all the work for you. Just drag and drop the fields you’re interested in to get instant answers.

A pivot table is like having a conversation with your data. You ask a question (“what are my best-selling products this month?”), and it gives you the answer as a clear, concise table.

This visual, interactive approach is just perfect for extracting strategic insights from your stock tracking.

Identifying your star products and your dead weight

The very first question every entrepreneur asks is: what sells best? With the structure we’ve put in place, the answer is within reach.

To find out, we’ll create a pivot table from your Stock Out tab. Here’s how:

• Click anywhere in your stock-out table.
• Go to the Insert menu and choose PivotTable.
• A window opens, with a list of fields on the right.
• Drag the Product Reference field into the Rows area.
• Then drag the Quantity out field into the Values area.

And there you go! In a few seconds, Excel generates a table summarizing total sales for each product. By sorting this table from largest to smallest, you immediately identify your star products (the ones generating the most volume) and your dead weight (the ones gathering dust on your shelves).

Well-maintained stock data can also directly feed into and refine your marketing strategy by helping you know which products to highlight.

Calculating the value of your stock at a glance

Another fundamental metric is the total value of your inventory. Knowing how much money is “tied up” in your stock is crucial for managing your cash flow.

Here again, it’s very simple. Just enrich our Products tab with a calculated “Stock Value” column (Current Stock * Purchase cost). Then, a quick pivot table based on this tab will give you the answer.

• Drag Supplier or Product Category into Rows.
• Drag Stock Value into Values.

You instantly get the value of your stock broken down by supplier or category. This is golden information for seeing where your capital is concentrated, negotiating with your suppliers, or deciding where to cut back on investment.

Managing your business with the right indicators

Going beyond simple quantities is where it all comes together. Using Excel to track inventory management indicators lets you reach a very precise level of control. Thanks to formulas and pivot tables, it’s possible to calculate key ratios such as inventory turnover, average storage time, or coverage rate.

This level of detail in your tracking helps you reduce costs linked to overly long storage periods and better adjust your orders based on market fluctuations.

By turning your raw data into a dashboard, you’re no longer just managing an inventory; you’re truly running your business. If you want to go further, our complete guide on building a management dashboard for your business will give you all the keys to visualizing the essential facets of your performance.

Frequently asked questions about inventory management with Excel

Even with the best dashboard in the world, you’ll always run into edge cases eventually. That’s normal. Here are the most common challenges you’re likely to encounter, with direct answers and tips drawn from experience, so your file remains an ally and not a burden.

How do I track batch numbers or expiry dates?

Great question, and it’s a crucial point if you sell food, cosmetics, or anything perishable. The solution is fairly simple: you just need to slightly enrich the basic structure of your file.

Just add two columns, “Batch Number” and “Expiry Date”, in your Stock In and Stock Out sheets. Every time a product comes in or goes out, you fill in this information.

To manage your stock effectively, especially if you apply the FIFO method (first in, first out), the reflex to have is to sort your inventory by expiry date. This will immediately show you which products need to go out first.

The tip that changes everything: Use conditional formatting so important dates jump out at you. For example, a simple rule: highlight in orange products expiring in less than 30 days and in red those due within the week. Visually, it’s unbeatable.

My Excel file has become very slow, what should I do?

Ah, the classic! The more data you add, the more Excel can start to lag. It’s a sign your file is getting substantial, but it’s frustrating. Usually, the culprit hides in one of two places: overly demanding formulas or an accumulation of formatting.

Fortunately, there are ways to give it a boost:

• Target your formulas: The typical mistake is applying a formula to an entire column (like A:A). It’s convenient, but it forces Excel to calculate over more than a million rows! Prefer precise ranges or, better yet, use structured tables, which automatically adjust the scope of formulas.
• Go easy on conditional formatting: It’s super useful, but each rule is an extra calculation for Excel. Apply it only where it’s really necessary.
• Archive old data: Do you really need stock movements from three years ago in your active file? Probably not. Once a year, do some housekeeping: copy past years’ data into a separate “Archive” file and delete it from your main file. It will instantly become lighter and more responsive.

Can this system manage multiple warehouses?

Yes, absolutely. This is actually one of the benefits of Excel’s flexibility. To track your stock across multiple sites, just add a “Warehouse” or “Location” column to your Stock In and Stock Out tabs.

For every movement, you’ll just need to specify which warehouse it involves.

Then you’ll just need to slightly adapt your SUMIFS formula to account for this new criterion. This will let you calculate the stock of a specific product for a given warehouse. On your dashboard, a simple drop-down filter will let you switch the view from one warehouse to another. Very handy.

What are the real limits of this method?

Let’s be honest, while Excel is a great tool to get started, it has its limits. The number-one risk is human error. A simple typo in a product reference, and your entire stock calculation is off. Even drop-down lists don’t prevent everything.

The other big challenge is working as a team. If different people need to update the file at the same time, it opens the door to version conflicts or overwritten data, even with the online versions of Excel.

When transaction volume becomes very high or multi-user management is essential, dedicated software will always bring more security, reliability, and automation.

---

When your Excel system starts to crack, when manual errors cost you money, or when management takes up too much of your time, it’s a sign that it’s time to switch to a tool built for entrepreneurs. Bizyness automates invoicing, quotes, and even accounting, so you can focus on what matters most: growing your business.

Discover how Bizyness can simplify your management today