Inventory Management in Excel: A Complete Template for Small Businesses

You have 150 items in stock but don’t really know what. Some products run out unexpectedly, others sit there for years. Every month you calculate inventory value by hand and hope the figures are right. There is a better way.

A simple inventory system in Excel gives you full control over what you have, where it is and when you need to order more. For companies with 50-500 items and a limited budget, Excel is often the most practical solution.

This guide shows you exactly how to build and use an inventory management system in Excel that actually works.

When Excel-based inventory management works

Excel is not the right inventory tool for every situation, but for many small businesses it is perfect.

The right size and complexity

Excel works very well when you have: – 50-500 items to track – 1-5 storage locations – A simple stock structure (not a multi-level BOM) – 1-5 people handling the stock – A limited budget (<20,000 SEK per year for an inventory system)

Real example: An office supplies company with 280 items has used Excel-based inventory management for six years. They track stock levels, automatic reorder alerts and inventory valuation. The system costs them nothing in licence fees.

When Excel is NOT enough

Upgrade to a dedicated system if you have: – 1,000+ items with a complex structure – Multi-level bill of materials – 10+ simultaneous inventory users – Real-time integration with e-commerce – Several warehouses with automatic redistribution – Barcoding and handheld scanners

For these situations, dedicated inventory systems are recommended.

Read more: Excel vs dedicated tools: When is a template enough?

What an Excel inventory system should contain

A functional inventory system needs a few basic components.

Item register

The main list with all inventory information:

Basic info: – Item number (unique ID) – Item name – Description – Category/product group – Supplier – Supplier’s item number

Stock info: – Current stock level (quantity) – Location in warehouse – Unit (pcs, kg, m, litres) – Minimum stock level (reorder point) – Optimal stock level – Lead time (days from order to delivery)

Financial data: – Purchase price per unit – Selling price per unit – Inventory value (quantity × price)

Transaction log

A separate sheet where all stock movements are logged:

• Date
• Item number
• Type (goods in, goods out, adjustment)
• Quantity
• From/to (storage location)
• Responsible person
• Comment

This gives you traceability and history.

Order list

An automatic list of items that need to be ordered:

• Items below the reorder point
• Recommended order quantity
• Supplier
• Estimated delivery date

Stocktaking

A sheet for the physical count:

• Item number and name
• System stock level
• Counted level
• Difference
• Value of difference

Dashboard

A summary with key figures: – Total inventory value – Number of items below the reorder point – Number of items with no movement (dead stock) – Average inventory turnover – Most valuable items (ABC analysis)

Build your Excel inventory system – step by step

Now we will build a complete inventory system from scratch.

Step 1: Create the basic structure

Open a new Excel document. Create these sheets:

1. Items (main register)
2. Transactions (the in/out log)
3. Orders (automatic order list)
4. Stocktaking (count sheet)
5. Dashboard (overview)

Step 2: Build the item register

In the “Items” sheet, create the columns:

A	B	C	D	E	F	G	H	I	J
Item no.	Name	Category	Supplier	Location	Stock level	Min level	Purchase price	Inventory value	Status

Formulas:

Inventory value (column I):

=F2*H2

(Stock level × Purchase price)

Status (column J):

=OM(F2<G2,"BESTÄLL",OM(F2<G2*1.5,"LÅG","OK"))

Conditional formatting for status: – “ORDER” → Red background – “LOW” → Yellow background – “OK” → Green background

Step 3: Create the transaction log

In the “Transactions” sheet:

A	B	C	D	E	F
Date	Item no.	Item name	Type	Quantity	Responsible

Item name automatically: Use VLOOKUP to fetch the name from the item register:

=LETARAD(B2,Artiklar!A:A,Artiklar!B:B,0)

Dropdown for type: Data Validation > List Source: Goods in,Goods out,Adjustment,Stocktaking

Step 4: Update the stock level automatically

In the item register, column “Stock level”, use SUMIF to add up the transactions:

=SUMMA.OM(Transaktioner!B:B,A2,Transaktioner!E:E)

This sums all transactions for the item (positive for goods in, negative for goods out).

Note: This requires goods out to be logged as negative numbers (-5 instead of 5).

Alternative: Keep the initial stock level in a separate column and update it manually based on the transactions.

Step 5: Build the order list

In the “Orders” sheet, create an automatic filter of items below the reorder point.

Method 1: Simple copying with a formula

Use the FILTER function (Excel 365):

=FILTER(Artiklar!A:J,Artiklar!J:J="BESTÄLL")

This automatically shows all items with the status “ORDER”.

Method 2: Pivot table

Create a pivot table from the Items sheet: – Filter: Status = “ORDER” – Rows: Item number, Name, Supplier – Values: Stock level, Min level

Step 6: Create the stocktaking sheet

In the “Stocktaking” sheet:

A	B	C	D	E	F
Item no.	Name	System level	Counted level	Difference	Diff value

The system level is fetched from Items:

=LETARAD(A2,Artiklar!A:A,Artiklar!F:F,0)

Difference:

=D2-C2

Value of difference:

=E2*LETARAD(A2,Artiklar!A:A,Artiklar!H:H,0)

Colour-code the differences: – Negative difference (shrinkage) → Red – Positive difference → Yellow

Step 7: Build the dashboard

Use formulas for the key figures:

Total inventory value:

=SUMMA(Artiklar!I:I)

Number of items below the reorder point:

=ANTAL.OM(Artiklar!J:J,"BESTÄLL")

Average inventory value per item:

=MEDEL(Artiklar!I:I)

Add a pie chart for categories and a bar chart for the top 10 most valuable items.

Advanced features

Once the basics work, you can add more sophisticated functionality.

ABC analysis

Classify items based on value:

• A items: 20% of the items account for 80% of the value
• B items: 30% of the items account for 15% of the value
• C items: 50% of the items account for 5% of the value

Add an “ABC class” column with a formula that ranks items by inventory value.

Economic order quantity (EOQ)

The economic order quantity is calculated with the formula:

EOQ = SQRT((2 × Årlig efterfrågan × Orderkostnad) / Lagerhållningskostnad)

Add columns for annual demand and calculate the optimal order quantity.

Automatic alerts by email

With VBA you can send an automatic notification when items reach the reorder point:

Sub SkickaBeställningsvarning()
    Dim OutlookApp As Object
    Dim Mail As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Artiklar")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 10).Value = "BESTÄLL" Then
            Set OutlookApp = CreateObject("Outlook.Application")
            Set Mail = OutlookApp.CreateItem(0)

            With Mail
                .To = "inkop@foretaget.se"
                .Subject = "Beställningsvarning: " & ws.Cells(i, 2).Value
                .Body = "Artikel " & ws.Cells(i, 1).Value & " har nått beställningspunkt." _
                        & vbCrLf & "Aktuell nivå: " & ws.Cells(i, 6).Value
                .Send
            End With
        End If
    Next i
End Sub

Read more: Automate Excel with VBA

Common inventory management mistakes

I have seen these mistakes many times.

Mistake 1: No transaction log

Many people update the stock level directly without logging why.

Result: When the stock level is wrong, nobody knows why.

Solution: Log EVERY change in the transaction log.

Mistake 2: Stocktaking too infrequently

“We do a stocktake once a year.”

Result: 12 months of errors accumulate.

Solution: Count by ABC classification: – A items: Every month – B items: Quarterly – C items: Annually

Mistake 3: No reorder point

Items are ordered reactively when they have run out.

Result: Stockouts and lost sales.

Solution: Set the reorder point based on lead time and average usage.

Mistake 4: Forgetting lead time

The reorder point is set to 10 pcs, but the supplier delivers after 30 days, during which you sell 40 pcs.

Result: Stockout despite having placed an order.

Solution: Reorder point = (Average daily usage × Lead time in days) + Safety stock

Mistake 5: No backup

Inventory data is deleted by mistake.

Solution: Automatic backup via OneDrive or a manual weekly copy.

From Excel to a dedicated inventory system

Excel-based inventory management works very well for many small businesses. But certain signs show that it is time to upgrade.

The signs

Volume is growing: If you reach 1,000+ items or 50+ transactions per day, Excel becomes slow.

Complexity is increasing: Multi-level bill of materials, manufacturing, quality control – Excel becomes too limited.

Several simultaneous users: If 10 people need to update the stock in real time, Excel cannot handle the load.

Integration is required: Inventory needs to talk to e-commerce, invoicing and production in real time.

Upgrade paths

For small businesses (500-2,000 items): – Ongoing WMS – VISMA Lager – Fortnox Lager

For medium-sized companies: – Microsoft Dynamics – NetSuite – SAP Business One

For e-commerce: – Ongoing Warehouse – Ongoing WMS – Integrated with Shopify/WooCommerce

The upside? Excel-based inventory management teaches you exactly which features you need before you invest in an expensive system.

Common questions about inventory management in Excel

How often should stock levels be updated? Every goods-in or goods-out movement should be logged immediately. Recommended: a daily check that the transaction log is complete.

Can I use barcode scanners with Excel? Yes, with VBA programming you can integrate USB barcode scanners. It requires intermediate skills or help from an Excel consultant.

How do I handle serial numbers or batch numbers? Add columns for serial number/batch in both the item register and the transaction log. An item can have several rows if it exists in several batches.

What is the optimal stock level for my items? It depends on lead time, sales velocity and costs. Rule of thumb: 2-4 weeks of consumption + safety stock.

Can several people work in the inventory system at the same time? SharePoint/OneDrive provides basic collaboration, but not real-time updating. For 5+ simultaneous users, a dedicated system is recommended.

Next step: Build your inventory system today

Inventory control gives you insight into your most valuable asset after your staff. It takes 3-4 hours to set up the first time.

Start here:

1. List your 20 most important items
2. Create the Excel sheets according to the guide
3. Fill in the item data
4. Log today’s transactions
5. Evaluate after a week and adjust

Need help?

If your inventory is complex or you want a professional solution:

• Tailored inventory templates for your industry
• Integration with existing systems
• Training for warehouse staff
• Migration from manual processes

Book a free consultation

Read on: – Excel templates for businesses: A complete guide – Excel vs dedicated tools: When is a template enough? – Automating Excel processes with VBA

Inventory control starts with structure. Build your system today.

---

Written by Lukas Lilja, Excel Department. We have helped hundreds of small businesses build effective inventory systems, from Excel to advanced WMS solutions.