Inventory Management in Excel: How to Build an In-Out-Balance File

Inventory management is make-or-break for any trading operation, from a home-based online shop to a small or medium business. Before you have the budget for dedicated warehouse management software (WMS), Microsoft Excel or Google Sheets is the most flexible, affordable and accessible way to start.

But a hastily built, non-standard spreadsheet easily leads to wrong figures, broken formulas and lost stock. This article walks you step by step through building a proper Excel inventory file with smart automation formulas, and helps you recognise when your business needs to move to software or to professional storage space.

1. What Is Inventory Management in Excel?

Inventory management in Excel means using a spreadsheet to record all goods received and issued, track actual stock on hand and classify product information. It suits online shops, household businesses and small companies with a moderate number of SKUs that want to keep initial costs down while still following the flow of goods.

Benefits of managing inventory in Excel

A spreadsheet brings several practical advantages to a small-scale business:

• Minimal upfront cost: You use office software already on your computer, with no monthly licence fee for a third-party system.
• Fully customisable tables: You can add columns, delete rows, change colours and lay the sheet out to match how your shop actually works.
• Clear visibility of stock movements: It is easy to track goods in, goods out and stock on hand by SKU and by storage location.
• Support for purchase planning: Weekly or monthly in-out-balance figures let you forecast your next purchase accurately.

Limitations of a spreadsheet

Convenient as it is, Excel still has technical limits a manager should anticipate:

• Prone to manual-entry errors: Research by Professor Raymond Panko of the University of Hawaii found that up to 88% of spreadsheets contain errors arising from mistyped data or overwriting that breaks a formula.
• Hard to work on as a team: When several people open and edit one Excel file kept on a personal computer, version conflicts and lost data happen easily.
• No real-time multi-channel stock syncing: Excel cannot deduct stock automatically when a new order lands on Shopee, TikTok Shop or Lazada; each one has to be typed in by hand.
• Slows down with large data: Once the catalogue reaches thousands of SKUs or the sheet holds tens of thousands of transaction rows, the spreadsheet lags and processes slowly.

When should and shouldn’t you use Excel?

The choice of tool should rest on your actual stock volume and transaction flow:

• When it works well: A newly launched online shop, selling from home, fewer than 100 SKUs, under 30 to 50 orders a day, and one or two people handling it directly.
• When it does not, or needs replacing: A business with warehouses in several districts, selling across channels with hundreds of orders a day, several staff working at once, or trading in fresh goods that demand strict expiry control.

2. How to Build an Excel Inventory File

A professional Excel inventory file should be split into 4 separate sheets: Product catalogue, Goods received, Goods issued and Stock summary. The crucial point is that every sheet must share one unique product code (SKU), which is the bridge that links the automatic formulas between them.

Step 1: Build the product catalogue

The Product catalogue sheet is the master database holding all identifying information for each product:

• Essential columns: SKU, Product name, Category, Unit of measure (piece, box, set), Cost price, Supplier and storage location in the warehouse (for example: Rack A1, Level 2).
• A safety stock column: Set a Minimum stock column for each product so the system flags a warning automatically when stock is running low.
• SKU naming standard: Every SKU must be unique and written without spaces or accents, and can combine letters and numbers to express product attributes (for example: AO-POLO-DEN-L).

Step 2: Build the goods-received sheet

The Goods received sheet records each delivery from suppliers or production workshops in detail:

• The columns you need: Date received, Receipt number, SKU, Product name, Unit, Quantity in, Unit cost, Line total and Notes on the source.
• Use auto-fill formulas: You only type the SKU, and the Product name and Unit columns fill in automatically via a lookup formula, saving time and avoiding typos.
• An automatic line-total column: The Line total column is calculated automatically as quantity received multiplied by the corresponding unit cost.

Step 3: Build the goods-issued sheet

The Goods issued sheet reflects every movement of goods out of the warehouse to customers or internal transfers:

• The important fields: Date issued, Issue note or Waybill number, SKU, Product name, Quantity out, Reason (retail, wholesale, faulty return) and Sales channel (Shopee, TikTok, in store).
• Separate promotional items and returns: Clearly note goods issued as free gifts or samples so your cost of goods is not distorted.
• Who handled it: Record the packer or the person responsible for the issue so accountability is traceable if anything goes missing.

Step 4: Build the stock summary sheet

The Stock summary sheet is the control centre that gives you the full picture of what you hold:

• The structure of the summary: Columns for SKU, Product name, Unit, Opening balance, Total in, Total out, Closing balance, Minimum stock level and Warning status.
• How the calculation works: The Total in and Total out columns pull data automatically from the Goods received and Goods issued sheets through conditional sum formulas.
• A visual status column: Use Conditional Formatting to colour products red automatically when their closing balance falls below the minimum stock level.

3. Automation Formulas for Inventory Management

To turn your Excel file from a simple logbook into a smart, semi-automated management tool, you need to master how to combine basic functions such as SUMIF, XLOOKUP, IF and IFERROR. Setting these formulas up correctly removes mental arithmetic entirely and keeps the figures reliable.

The stock calculation formula

The unchanging rule in warehouse management practice is expressed by this balance equation:

Closing balance = Opening balance + Total received − Total issued

Here, the Opening balance is the quantity physically counted at the start of the tracking cycle; Total received is the full quantity of that SKU added up from the Goods received sheet; Total issued is the full quantity of that SKU added up from the Goods issued sheet in the same period.

Common Excel functions for inventory

The table below sets out what each of the most important Excel functions does in inventory management and how to write it:

Setting up low-stock alerts

An automatic alert system means a shop owner is never caught out by an order when the shelf is already empty:

• Use IF to classify the status: In the Warning status column of the Stock summary sheet, enter a formula that checks whether current stock is at or below the minimum level; if so the function shows "Reorder now", otherwise "In stock".
• Apply Conditional Formatting: Select the whole Status column, choose Conditional Formatting and create a Highlight Cells Rule for text containing "Reorder now" with a light red fill and dark red text. As soon as one SKU hits the threshold, that whole product row changes colour and catches your eye.

Build automatic in-out-balance reports

To see in-out reports by week, month or quarter without rewriting formulas, PivotTable is the most powerful helper:

• Create a PivotTable from the goods-issued data: Select the whole data range in the Goods issued sheet, go to Insert and choose PivotTable.
• Drag the fields into place: Drag Date issued into Rows (the system groups by Month and Quarter automatically), drag SKU or Category into Rows, and drag Quantity out and Line total into Values.
• A clear analytical result: In seconds you get a detailed report showing which SKU sold best this month and which channel delivered the highest volume, so you can adjust your plan in time. The result also helps you classify stock into A, B and C groups and prioritise storage locations sensibly.

4. Keeping the File Accurate

However smart the formulas, an Excel file is worthless if the input data is not recorded diligently. Building operating discipline, setting a rule for when data is entered, and counting stock physically on a regular basis are the preconditions for keeping the spreadsheet perfectly in step with the goods on the shelf.

Set up a regular stocktaking routine

A gap between the books and reality always appears, caused by breakage, damage, packing mistakes or shrinkage:

• Cycle count weekly: Rather than waiting until year end, split the catalogue and physically count 10 to 20 SKUs every weekend afternoon.
• Count independently of the figures: Whoever counts the shelves should not carry the Excel stock figures with them, to avoid the temptation of glancing at the number and ticking it off.
• Find the cause before correcting the number: When a discrepancy appears, review the packing camera and the most recent in and out documents to establish why there is a surplus or shortfall before adjusting the opening balance.

Common mistakes and how to fix them

Excel users regularly make basic mistakes that throw the whole calculation out:

• A stray space in the SKU: "SP01" and "SP01 " look identical to the eye, but Excel treats them as two entirely different codes, so SUMIF cannot total them. The fix: use Data Validation to create a fixed drop-down list for the SKU column.
• Formula ranges that do not follow the data: When you insert new rows in the in and out sheets but the SUMIF formula in the summary keeps its old range, new data is missed. The fix: convert the in and out sheets to Excel Tables with Ctrl + T so formulas expand with new rows automatically.
• Multiple file versions floating around: Staff copy the file to their own computer and edit it separately, so nobody knows which figures are authoritative. The fix: keep the file centrally in the cloud and grant edit rights only to those directly responsible.

Tips for running the file well

To protect the structure of the spreadsheet from staff accidentally deleting a formula, apply these techniques:

• Lock the formula cells: Unlock only the cells that need manual entry such as Date, Quantity and Unit price; all columns holding SUMIF and XLOOKUP formulas should be locked and the sheet protected with a password.
• Use smart shortcuts: Press Ctrl + Shift + L to toggle filters quickly, and Ctrl + ; to drop in today’s date instantly when recording goods in or out.
• Schedule regular backups: Set a weekly automatic backup of the Excel file to a separate folder in case the original is hit by a virus or becomes corrupted.

5. When to Switch Software or Rent Storage

Excel is a perfect launchpad for the early stage, but it cannot keep pace forever as a business grows. Recognising the moment the spreadsheet hits its ceiling lets a shop owner move to professional management tools in time, free up labour and keep the flow of goods safe.

Signs Excel no longer copes

A business should consider switching tools straight away when it meets any of these:

• Order volume jumps: Hundreds of orders a day arrive across several online channels, and staff spend the whole day simply retyping figures into Excel.
• Cancellations from stock mismatches climb: The marketplace shows stock available when the shelf emptied long ago, and the shop loses operational points and ratings.
• Capital tied up through missed expiry dates: Cosmetics and food items start expiring without the manager spotting it in an ordinary spreadsheet.

Excel versus warehouse management software

The table below makes clear the difference between keeping a manual spreadsheet and using professional software:

When the real problem is storage space

In many cases the bottleneck in inventory management comes not from the spreadsheet but from a genuine shortage of storage space:

Many online sellers keep refining their Excel file while the stockroom at home is packed with cardboard boxes up to the ceiling, blocking walkways and causing breakages. When goods are crammed in, finding one SKU to pack can take 15 minutes and delay the courier handover.

At that point the real fix is not more expensive software but separating the business space from the family’s living space. For small and medium shops, renting a whole large industrial warehouse is too costly and wastes floor area, while mini self-storage has become the leading choice. Our analysis of renting storage versus keeping things at home will help you weigh the total cost before deciding.

MyStorage: smart self-storage for SMEs

To help online shop owners in Ho Chi Minh City solve the space shortage while keeping operating costs down, MyStorage offers modern self-storage built to international standards:

MyStorage self-storage offers a range of flexible sizes, from mini units of just a few cubic metres through to private rooms from 10m² to over 50m² at well-connected locations across Ho Chi Minh City.

• Climate control to standard: The air-conditioned storage area holds a steady temperature below 25°C and humidity under 60%, protecting sensitive goods such as cosmetics, leather, fine clothing and electronics.
• Multi-layer security with personal access: Each customer gets their own lock code to come and go from their unit freely; the whole site is covered by CCTV 24/7 with an approved automatic fire-safety system.
• Highly flexible rental terms: Shop owners can rent month by month, scale up the room during peak shopping season and scale back once the selling season ends.
• Facilities that help you pack: Desk space, high-speed Wi-Fi, trolleys and a heavy-duty goods lift are all on site, so you can check your records and pack orders right at your unit.

Not sure how many cubic metres your shop needs? Photograph your stock and let MyStorage's AI Size Estimator work it out:

A clear note on scope of service:

MyStorage self-storage focuses on providing independent, well-equipped and secure storage space. The model does not include order picking, automated packing or dispatch to marketplaces by default unless the customer signs up for the relevant support packages.

Reference Storage Rental Prices

To help businesses and online shop owners budget for extra space, here are the reference price ranges for storage options in Ho Chi Minh City:

Please note: the prices above are indicative for the Ho Chi Minh City market in late 2026. Actual storage costs vary with the specific location, the amenities included and the contract term you choose.

Pick a storage type, size and temperature option to see today's MyStorage price instantly and book with an offer:

Optimise Your Management to Grow Revenue

Managing inventory in Excel is an important stepping stone that trains you to organise data, control cash flow and build discipline in trading. By splitting the sheets sensibly, standardising SKUs and applying automatic formulas, you can run your stockroom professionally without spending much.

That said, do not let a cramped home or an overloaded manual spreadsheet hold your shop back. As your stock grows, switching tools and expanding storage space for an online shop outside the home becomes a solid lever for growing sales.

If you are looking for secure self-storage with proper air conditioning and flexible sizes in Ho Chi Minh City, get in touch with MyStorage for advice on the unit size that best fits your business.

This article was written by the MyStorage team, drawing on our experience running storage services in Ho Chi Minh City; last updated October 2026. The prices given here are indicative at the time of writing.

Frequently Asked Questions