{"id":1989,"date":"2026-10-01T10:50:37","date_gmt":"2026-10-01T10:50:37","guid":{"rendered":"https:\/\/www.exceldepartment.com\/?p=1989"},"modified":"2026-10-31T12:14:04","modified_gmt":"2026-10-31T12:14:04","slug":"inventory-management-excel-template-guide","status":"publish","type":"post","link":"https:\/\/www.exceldepartment.com\/en\/inventory-management-excel-template-guide\/","title":{"rendered":"Inventory Management in Excel: A Complete Template for Small Businesses"},"content":{"rendered":"<div class=\"page-content container\">\n<p>You have 150 items in stock but don&#8217;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.<\/p>\n<p>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.<\/p>\n<p>This guide shows you exactly how to build and use an inventory management system in Excel that actually works.<\/p>\n<h2 id=\"n\u00e4r-excel-baserad-lagerhantering-fungerar\">When Excel-based inventory management works<\/h2>\n<p>Excel is not the right inventory tool for every situation, but for many small businesses it is perfect.<\/p>\n<h3 id=\"r\u00e4tt-storlek-och-komplexitet\">The right size and complexity<\/h3>\n<p><strong>Excel works very well when you have:<\/strong> &#8211; 50-500 items to track &#8211; 1-5 storage locations &#8211; A simple stock structure (not a multi-level BOM) &#8211; 1-5 people handling the stock &#8211; A limited budget (&lt;20,000 SEK per year for an inventory system)<\/p>\n<p><strong>Real example:<\/strong> 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.<\/p>\n<h3 id=\"n\u00e4r-excel-inte-r\u00e4cker\">When Excel is NOT enough<\/h3>\n<p><strong>Upgrade to a dedicated system if you have:<\/strong> &#8211; 1,000+ items with a complex structure &#8211; Multi-level bill of materials &#8211; 10+ simultaneous inventory users &#8211; Real-time integration with e-commerce &#8211; Several warehouses with automatic redistribution &#8211; Barcoding and handheld scanners<\/p>\n<p>For these situations, dedicated inventory systems are recommended.<\/p>\n<p>Read more: <a href=\"#\">Excel vs dedicated tools: When is a template enough?<\/a><\/p>\n<h2 id=\"vad-ett-excel-lagersystem-b\u00f6r-inneh\u00e5lla\">What an Excel inventory system should contain<\/h2>\n<p>A functional inventory system needs a few basic components.<\/p>\n<h3 id=\"artikelregister\">Item register<\/h3>\n<p>The main list with all inventory information:<\/p>\n<p><strong>Basic info:<\/strong> &#8211; Item number (unique ID) &#8211; Item name &#8211; Description &#8211; Category\/product group &#8211; Supplier &#8211; Supplier&#8217;s item number<\/p>\n<p><strong>Stock info:<\/strong> &#8211; Current stock level (quantity) &#8211; Location in warehouse &#8211; Unit (pcs, kg, m, litres) &#8211; Minimum stock level (reorder point) &#8211; Optimal stock level &#8211; Lead time (days from order to delivery)<\/p>\n<p><strong>Financial data:<\/strong> &#8211; Purchase price per unit &#8211; Selling price per unit &#8211; Inventory value (quantity \u00d7 price)<\/p>\n<h3 id=\"transaktionslogg\">Transaction log<\/h3>\n<p>A separate sheet where all stock movements are logged:<\/p>\n<ul>\n<li>Date<\/li>\n<li>Item number<\/li>\n<li>Type (goods in, goods out, adjustment)<\/li>\n<li>Quantity<\/li>\n<li>From\/to (storage location)<\/li>\n<li>Responsible person<\/li>\n<li>Comment<\/li>\n<\/ul>\n<p>This gives you traceability and history.<\/p>\n<h3 id=\"best\u00e4llningsunderlag\">Order list<\/h3>\n<p>An automatic list of items that need to be ordered:<\/p>\n<ul>\n<li>Items below the reorder point<\/li>\n<li>Recommended order quantity<\/li>\n<li>Supplier<\/li>\n<li>Estimated delivery date<\/li>\n<\/ul>\n<h3 id=\"inventering\">Stocktaking<\/h3>\n<p>A sheet for the physical count:<\/p>\n<ul>\n<li>Item number and name<\/li>\n<li>System stock level<\/li>\n<li>Counted level<\/li>\n<li>Difference<\/li>\n<li>Value of difference<\/li>\n<\/ul>\n<h3 id=\"dashboard\">Dashboard<\/h3>\n<p>A summary with key figures: &#8211; Total inventory value &#8211; Number of items below the reorder point &#8211; Number of items with no movement (dead stock) &#8211; Average inventory turnover &#8211; Most valuable items (ABC analysis)<\/p>\n<h2 id=\"bygg-ditt-excel-lagersystem-steg-f\u00f6r-steg\">Build your Excel inventory system \u2013 step by step<\/h2>\n<p>Now we will build a complete inventory system from scratch.<\/p>\n<h3 id=\"steg-1-skapa-grundstruktur\">Step 1: Create the basic structure<\/h3>\n<p>Open a new Excel document. Create these sheets:<\/p>\n<ol type=\"1\">\n<li><strong>Items<\/strong> (main register)<\/li>\n<li><strong>Transactions<\/strong> (the in\/out log)<\/li>\n<li><strong>Orders<\/strong> (automatic order list)<\/li>\n<li><strong>Stocktaking<\/strong> (count sheet)<\/li>\n<li><strong>Dashboard<\/strong> (overview)<\/li>\n<\/ol>\n<h3 id=\"steg-2-bygg-artikelregistret\">Step 2: Build the item register<\/h3>\n<\/p>\n<p>In the \u201cItems\u201d sheet, create the columns:<\/p>\n<table style=\"width:100%;\">\n<colgroup>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<col style=\"width: 10%\" \/>\n<\/colgroup>\n<thead>\n<tr>\n<th>A<\/th>\n<th>B<\/th>\n<th>C<\/th>\n<th>D<\/th>\n<th>E<\/th>\n<th>F<\/th>\n<th>G<\/th>\n<th>H<\/th>\n<th>I<\/th>\n<th>J<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Item no.<\/td>\n<td>Name<\/td>\n<td>Category<\/td>\n<td>Supplier<\/td>\n<td>Location<\/td>\n<td>Stock level<\/td>\n<td>Min level<\/td>\n<td>Purchase price<\/td>\n<td>Inventory value<\/td>\n<td>Status<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Formulas:<\/strong><\/p>\n<p><strong>Inventory value (column I):<\/strong><\/p>\n<pre><code>=F2*H2<\/code><\/pre>\n<p>(Stock level \u00d7 Purchase price)<\/p>\n<p><strong>Status (column J):<\/strong><\/p>\n<pre><code>=OM(F2&lt;G2,&quot;BEST\u00c4LL&quot;,OM(F2&lt;G2*1.5,&quot;L\u00c5G&quot;,&quot;OK&quot;))<\/code><\/pre>\n<p>Conditional formatting for status: &#8211; \u201cORDER\u201d \u2192 Red background &#8211; \u201cLOW\u201d \u2192 Yellow background &#8211; \u201cOK\u201d \u2192 Green background<\/p>\n<h3 id=\"steg-3-skapa-transaktionslogg\">Step 3: Create the transaction log<\/h3>\n<p>In the \u201cTransactions\u201d sheet:<\/p>\n<table>\n<thead>\n<tr>\n<th>A<\/th>\n<th>B<\/th>\n<th>C<\/th>\n<th>D<\/th>\n<th>E<\/th>\n<th>F<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Date<\/td>\n<td>Item no.<\/td>\n<td>Item name<\/td>\n<td>Type<\/td>\n<td>Quantity<\/td>\n<td>Responsible<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Item name automatically:<\/strong> Use VLOOKUP to fetch the name from the item register:<\/p>\n<pre><code>=LETARAD(B2,Artiklar!A:A,Artiklar!B:B,0)<\/code><\/pre>\n<p><strong>Dropdown for type:<\/strong> Data Validation &gt; List Source: <code>Goods in,Goods out,Adjustment,Stocktaking<\/code><\/p>\n<h3 id=\"steg-4-uppdatera-lagerniv\u00e5-automatiskt\">Step 4: Update the stock level automatically<\/h3>\n<p>In the item register, column \u201cStock level\u201d, use SUMIF to add up the transactions:<\/p>\n<pre><code>=SUMMA.OM(Transaktioner!B:B,A2,Transaktioner!E:E)<\/code><\/pre>\n<p>This sums all transactions for the item (positive for goods in, negative for goods out).<\/p>\n<p><strong>Note:<\/strong> This requires goods out to be logged as negative numbers (-5 instead of 5).<\/p>\n<p>Alternative: Keep the initial stock level in a separate column and update it manually based on the transactions.<\/p>\n<h3 id=\"steg-5-bygg-best\u00e4llningslista\">Step 5: Build the order list<\/h3>\n<p>In the \u201cOrders\u201d sheet, create an automatic filter of items below the reorder point.<\/p>\n<p><strong>Method 1: Simple copying with a formula<\/strong><\/p>\n<p>Use the FILTER function (Excel 365):<\/p>\n<pre><code>=FILTER(Artiklar!A:J,Artiklar!J:J=&quot;BEST\u00c4LL&quot;)<\/code><\/pre>\n<p>This automatically shows all items with the status \u201cORDER\u201d.<\/p>\n<p><strong>Method 2: Pivot table<\/strong><\/p>\n<p>Create a pivot table from the Items sheet: &#8211; Filter: Status = \u201cORDER\u201d &#8211; Rows: Item number, Name, Supplier &#8211; Values: Stock level, Min level<\/p>\n<h3 id=\"steg-6-skapa-inventeringsunderlag\">Step 6: Create the stocktaking sheet<\/h3>\n<p>In the \u201cStocktaking\u201d sheet:<\/p>\n<table>\n<thead>\n<tr>\n<th>A<\/th>\n<th>B<\/th>\n<th>C<\/th>\n<th>D<\/th>\n<th>E<\/th>\n<th>F<\/th>\n<th><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Item no.<\/td>\n<td>Name<\/td>\n<td>System level<\/td>\n<td>Counted level<\/td>\n<td>Difference<\/td>\n<td>Diff value<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>The system level is fetched from Items:<\/strong><\/p>\n<pre><code>=LETARAD(A2,Artiklar!A:A,Artiklar!F:F,0)<\/code><\/pre>\n<p><strong>Difference:<\/strong><\/p>\n<pre><code>=D2-C2<\/code><\/pre>\n<p><strong>Value of difference:<\/strong><\/p>\n<pre><code>=E2*LETARAD(A2,Artiklar!A:A,Artiklar!H:H,0)<\/code><\/pre>\n<p>Colour-code the differences: &#8211; Negative difference (shrinkage) \u2192 Red &#8211; Positive difference \u2192 Yellow<\/p>\n<h3 id=\"steg-7-bygg-dashboard\">Step 7: Build the dashboard<\/h3>\n<\/p>\n<p>Use formulas for the key figures:<\/p>\n<p><strong>Total inventory value:<\/strong><\/p>\n<pre><code>=SUMMA(Artiklar!I:I)<\/code><\/pre>\n<p><strong>Number of items below the reorder point:<\/strong><\/p>\n<pre><code>=ANTAL.OM(Artiklar!J:J,&quot;BEST\u00c4LL&quot;)<\/code><\/pre>\n<p><strong>Average inventory value per item:<\/strong><\/p>\n<pre><code>=MEDEL(Artiklar!I:I)<\/code><\/pre>\n<p>Add a pie chart for categories and a bar chart for the top 10 most valuable items.<\/p>\n<h2 id=\"avancerade-funktioner\">Advanced features<\/h2>\n<p>Once the basics work, you can add more sophisticated functionality.<\/p>\n<h3 id=\"abc-analys\">ABC analysis<\/h3>\n<p>Classify items based on value:<\/p>\n<ul>\n<li><strong>A items:<\/strong> 20% of the items account for 80% of the value<\/li>\n<li><strong>B items:<\/strong> 30% of the items account for 15% of the value<\/li>\n<li><strong>C items:<\/strong> 50% of the items account for 5% of the value<\/li>\n<\/ul>\n<p>Add an \u201cABC class\u201d column with a formula that ranks items by inventory value.<\/p>\n<h3 id=\"optimal-best\u00e4llningskvantitet-eoq\">Economic order quantity (EOQ)<\/h3>\n<p>The economic order quantity is calculated with the formula:<\/p>\n<pre><code>EOQ = SQRT((2 \u00d7 \u00c5rlig efterfr\u00e5gan \u00d7 Orderkostnad) \/ Lagerh\u00e5llningskostnad)<\/code><\/pre>\n<p>Add columns for annual demand and calculate the optimal order quantity.<\/p>\n<h3 id=\"automatiska-larm-via-e-post\">Automatic alerts by email<\/h3>\n<p>With VBA you can send an automatic notification when items reach the reorder point:<\/p>\n<pre class=\"vba\"><code>Sub SkickaBest\u00e4llningsvarning()\n    Dim OutlookApp As Object\n    Dim Mail As Object\n    Dim ws As Worksheet\n    Dim lastRow As Long\n    Dim i As Long\n\n    Set ws = ThisWorkbook.Worksheets(&quot;Artiklar&quot;)\n    lastRow = ws.Cells(ws.Rows.Count, &quot;A&quot;).End(xlUp).Row\n\n    For i = 2 To lastRow\n        If ws.Cells(i, 10).Value = &quot;BEST\u00c4LL&quot; Then\n            Set OutlookApp = CreateObject(&quot;Outlook.Application&quot;)\n            Set Mail = OutlookApp.CreateItem(0)\n\n            With Mail\n                .To = &quot;inkop@foretaget.se&quot;\n                .Subject = &quot;Best\u00e4llningsvarning: &quot; &amp; ws.Cells(i, 2).Value\n                .Body = &quot;Artikel &quot; &amp; ws.Cells(i, 1).Value &amp; &quot; har n\u00e5tt best\u00e4llningspunkt.&quot; _\n                        &amp; vbCrLf &amp; &quot;Aktuell niv\u00e5: &quot; &amp; ws.Cells(i, 6).Value\n                .Send\n            End With\n        End If\n    Next i\nEnd Sub<\/code><\/pre>\n<p>Read more: <a href=\"https:\/\/www.exceldepartment.com\/en\/excel-automation-vba-complete-ebook\/\">Automate Excel with VBA<\/a><\/p>\n<h2 id=\"vanliga-lagerhanteringsmisstag\">Common inventory management mistakes<\/h2>\n<p>I have seen these mistakes many times.<\/p>\n<h3 id=\"misstag-1-ingen-transaktionslogg\">Mistake 1: No transaction log<\/h3>\n<p>Many people update the stock level directly without logging why.<\/p>\n<p>Result: When the stock level is wrong, nobody knows why.<\/p>\n<p><strong>Solution:<\/strong> Log EVERY change in the transaction log.<\/p>\n<h3 id=\"misstag-2-f\u00f6r-s\u00e4llsynt-inventering\">Mistake 2: Stocktaking too infrequently<\/h3>\n<p>\u201cWe do a stocktake once a year.\u201d<\/p>\n<p>Result: 12 months of errors accumulate.<\/p>\n<p><strong>Solution:<\/strong> Count by ABC classification: &#8211; A items: Every month &#8211; B items: Quarterly &#8211; C items: Annually<\/p>\n<h3 id=\"misstag-3-ingen-best\u00e4llningspunkt\">Mistake 3: No reorder point<\/h3>\n<p>Items are ordered reactively when they have run out.<\/p>\n<p>Result: Stockouts and lost sales.<\/p>\n<p><strong>Solution:<\/strong> Set the reorder point based on lead time and average usage.<\/p>\n<h3 id=\"misstag-4-gl\u00f6mmer-ledtid\">Mistake 4: Forgetting lead time<\/h3>\n<p>The reorder point is set to 10 pcs, but the supplier delivers after 30 days, during which you sell 40 pcs.<\/p>\n<p>Result: Stockout despite having placed an order.<\/p>\n<p><strong>Solution:<\/strong> Reorder point = (Average daily usage \u00d7 Lead time in days) + Safety stock<\/p>\n<h3 id=\"misstag-5-ingen-backup\">Mistake 5: No backup<\/h3>\n<p>Inventory data is deleted by mistake.<\/p>\n<p><strong>Solution:<\/strong> Automatic backup via OneDrive or a manual weekly copy.<\/p>\n<h2 id=\"fr\u00e5n-excel-till-dedikerat-lagersystem\">From Excel to a dedicated inventory system<\/h2>\n<p>Excel-based inventory management works very well for many small businesses. But certain signs show that it is time to upgrade.<\/p>\n<h3 id=\"tecknen\">The signs<\/h3>\n<p><strong>Volume is growing:<\/strong> If you reach 1,000+ items or 50+ transactions per day, Excel becomes slow.<\/p>\n<p><strong>Complexity is increasing:<\/strong> Multi-level bill of materials, manufacturing, quality control \u2013 Excel becomes too limited.<\/p>\n<p><strong>Several simultaneous users:<\/strong> If 10 people need to update the stock in real time, Excel cannot handle the load.<\/p>\n<p><strong>Integration is required:<\/strong> Inventory needs to talk to e-commerce, invoicing and production in real time.<\/p>\n<h3 id=\"uppgraderingsv\u00e4gar\">Upgrade paths<\/h3>\n<p><strong>For small businesses (500-2,000 items):<\/strong> &#8211; Ongoing WMS &#8211; VISMA Lager &#8211; Fortnox Lager<\/p>\n<p><strong>For medium-sized companies:<\/strong> &#8211; Microsoft Dynamics &#8211; NetSuite &#8211; SAP Business One<\/p>\n<p><strong>For e-commerce:<\/strong> &#8211; Ongoing Warehouse &#8211; Ongoing WMS &#8211; Integrated with Shopify\/WooCommerce<\/p>\n<p>The upside? Excel-based inventory management teaches you exactly which features you need before you invest in an expensive system.<\/p>\n<h2 id=\"vanliga-fr\u00e5gor-om-lagerhantering-i-excel\">Common questions about inventory management in Excel<\/h2>\n<p><strong>How often should stock levels be updated?<\/strong> Every goods-in or goods-out movement should be logged immediately. Recommended: a daily check that the transaction log is complete.<\/p>\n<p><strong>Can I use barcode scanners with Excel?<\/strong> Yes, with VBA programming you can integrate USB barcode scanners. It requires intermediate skills or help from an <a href=\"https:\/\/www.exceldepartment.com\/en\/excel-consultant\/\">Excel consultant<\/a>.<\/p>\n<p><strong>How do I handle serial numbers or batch numbers?<\/strong> 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.<\/p>\n<p><strong>What is the optimal stock level for my items?<\/strong> It depends on lead time, sales velocity and costs. Rule of thumb: 2-4 weeks of consumption + safety stock.<\/p>\n<p><strong>Can several people work in the inventory system at the same time?<\/strong> SharePoint\/OneDrive provides basic collaboration, but not real-time updating. For 5+ simultaneous users, a dedicated system is recommended.<\/p>\n<h2 id=\"n\u00e4sta-steg-bygg-ditt-lagersystem-idag\">Next step: Build your inventory system today<\/h2>\n<p>Inventory control gives you insight into your most valuable asset after your staff. It takes 3-4 hours to set up the first time.<\/p>\n<p><strong>Start here:<\/strong><\/p>\n<ol type=\"1\">\n<li>List your 20 most important items<\/li>\n<li>Create the Excel sheets according to the guide<\/li>\n<li>Fill in the item data<\/li>\n<li>Log today&#8217;s transactions<\/li>\n<li>Evaluate after a week and adjust<\/li>\n<\/ol>\n<p><strong>Need help?<\/strong><\/p>\n<p>If your inventory is complex or you want a professional solution:<\/p>\n<ul>\n<li>Tailored inventory templates for your industry<\/li>\n<li>Integration with existing systems<\/li>\n<li>Training for warehouse staff<\/li>\n<li>Migration from manual processes<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.exceldepartment.com\/en\/contact-us\/\">Book a free consultation<\/a><\/p>\n<p><strong>Read on:<\/strong> &#8211; <a href=\"https:\/\/www.exceldepartment.com\/en\/templates\/\">Excel templates for businesses: A complete guide<\/a> &#8211; <a href=\"#\">Excel vs dedicated tools: When is a template enough?<\/a> &#8211; <a href=\"https:\/\/www.exceldepartment.com\/en\/excel-automation-vba-complete-ebook\/\">Automating Excel processes with VBA<\/a><\/p>\n<p>Inventory control starts with structure. Build your system today.<\/p>\n<hr \/>\n<p><em>Written by Lukas Lilja, Excel Department. We have helped hundreds of small businesses build effective inventory systems, from Excel to advanced WMS solutions.<\/em><\/p>\n<\/div>\n<div class=\"xld-cta\" style=\"margin:30px 0;padding:18px 20px;background:#f5f4f4;border-top:3px solid #a0ce4e\">\n<p style=\"margin:0 0 8px\"><strong>Need help with this?<\/strong> Our <a href=\"https:\/\/www.exceldepartment.com\/en\/templates\/\">Excel templates<\/a> help you \u2013 the first consultation is free of charge.<\/p>\n<p style=\"margin:0\"><a href=\"https:\/\/www.exceldepartment.com\/en\/contact-us\/\">Book a free call<\/a> or call <a href=\"tel:+46102642020\">010-264 20 20<\/a>.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Free inventory management template for Excel. Track stock levels, reorder points and inventory value. Step-by-step guide for small businesses with 50-500 items.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-1989","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1989","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/comments?post=1989"}],"version-history":[{"count":0,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/posts\/1989\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/media?parent=1989"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/categories?post=1989"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.exceldepartment.com\/en\/wp-json\/wp\/v2\/tags?post=1989"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}