Inventory Tracking System in Excel: Build a Stock Ledger That Stays Accurate

Your sheet says 40 units. The shelf holds 31.

Nobody knows where the other nine went. That happens when stock is a number someone types over. An inventory tracking system in Excel fixes it by logging every movement, so your count is always the sum of what actually happened.

What Is an Inventory Tracking System in Excel?

An inventory tracking system in Excel is a workbook that logs every stock movement as its own row (purchase, sale, return, adjustment) and calculates quantity on hand from those rows. Excel worksheets hold 1,048,576 rows, per Microsoft’s specifications, so a Malaysian seller’s log can run for years before it fills.

Most sellers start with a column called “Stock” and type over it after each sale. That works until the number is wrong. Then you cannot see when it went wrong, or why.

A tracking system does not store the count. It stores events: stock arrived, stock sold, stock came back, stock was corrected. The count is calculated from those events, so nobody can overwrite it by accident and every change leaves a trail.

That trail matters when one shelf feeds Shopee MY, Lazada MY, and TikTok Shop. Each marketplace shows its own number. Your ledger is the one you trust when they disagree.

Tracking is narrower than management. It answers two questions: how many do I have, and why did it change? For reorder rules and costing, see our guide to inventory management software in Excel.

What Should an Excel Inventory Tracking System Record?

Each ledger row should record seven things: date, SKU, movement type, signed quantity, channel, reference number, and a short note. Four movement types cover almost every case: purchase, sale, return, and adjustment. Sales are negative, purchases and returns are positive, and adjustments go either way after a stock count.

Here is the layout:

A few days of rows look like this. The SKUs and order numbers are made up.

The SKU column is where most trackers rot. One person types TSH-BLK-M, another types TSH BLK M, and the sheet now thinks you stock two products. Pick SKUs from a dropdown fed by your product list, and use the same code as the seller SKU on every marketplace listing. If you have no naming pattern yet, the SKU generator builds a prompt you can paste into an AI tool to draft consistent codes.

Print the SKU on the shelf or bin too, so whoever counts knows which variant they hold.

The Reference column is your audit trail. A file’s version history shows that a cell changed. It does not say why. A row reading “SP-1001, return, resellable” does.

How Do You Set Up the Three Tabs?

Build the system on three tabs: Products (one row per SKU), Ledger (one row per movement), and Stock (one row per SKU, calculated). Stock uses SUMIFS to add up the Ledger, so nobody types a stock count. Microsoft documents SUMIFS as the function that sums values meeting several conditions.

Set it up in this order:

1. Products tab. Columns: SKU, Product, Cost (RM), Reorder point, Supplier, Lead time (days). One row per variant, so a T-shirt in three sizes takes three rows.
2. Ledger tab. Add the seven columns above, then press Ctrl+T to turn the range into an Excel Table. New rows then inherit formulas and dropdowns.
3. Dropdowns. Use data validation so Type accepts only Purchase, Sale, Return, or Adjust, and SKU accepts only codes from the Products tab.
4. Stock tab. List every SKU once, then add the formulas below.
5. Lock it. Protect the Stock tab (Review, then Protect Sheet) so nobody types over a formula.

Assume Ledger columns A to G in the order above and Products columns A to F in the order in step 1. Put these in row 2 of the Stock tab, with the SKU in A2, then fill down:

B2  On hand     =SUMIFS(Ledger!D:D, Ledger!B:B, A2)
C2  Reorder pt  =XLOOKUP(A2, Products!A:A, Products!D:D)
D2  Status      =IF(B2<=C2, "Reorder", "OK")
E2  Value (RM)  =B2*XLOOKUP(A2, Products!A:A, Products!C:C)

XLOOKUP works in Excel 2021, Microsoft 365, and Google Sheets. On an older Excel, use VLOOKUP instead.

Add conditional formatting so any “Reorder” cell turns red. Set each reorder point as average daily sales multiplied by supplier lead time in days, plus a safety buffer. The reorder point calculator does the maths for you.

Not sure what goes in the Reorder point column? Work it out with the free reorder point calculator. Free. No signup.

How Do Malaysian Sellers Keep the Ledger Current Every Day?

Pick one cut-off time each day, export the orders you packed from Shopee MY, Lazada MY, and TikTok Shop, and paste them into the Ledger as negative rows. Log deliveries and returns the same day. One person owns the sheet. A single-seller shop can usually keep this up until several channels run at once.

The daily routine:

1. Set the cut-off. Choose a time after your courier (Pos Laju, Ninja Van, J&T Express) collects parcels. Everything packed before it goes into that day’s log.
2. Export packed orders from the Shopee MY, Lazada MY, and TikTok Shop seller centres.
3. Match the codes. Fill the seller SKU field on every listing with your master code, so each export already speaks your ledger’s language.
4. Paste as Sale rows. Quantity negative. Channel and order number go in Channel and Reference.
5. Log deliveries. Each supplier delivery is a Purchase row, with the invoice number as the reference.
6. Log returns after inspection. A returned parcel is not stock until someone opens it. Resellable items become a Return row. Damaged items get an Adjust row with a note.
7. Read the Stock tab. Anything marked Reorder goes on today’s purchase list.

Log a sale when you pack the parcel, not when the order arrives. Orders cancelled before packing never touch the ledger, so you never reverse them.

Campaign days break the routine. During sales like 9.9, 11.11, and 12.12, orders arrive faster than one daily update can absorb. Add a second cut-off, and list a few units fewer than the shelf holds. That buffer covers the lag between a sale and your next entry.

How Do You Check the Ledger Against the Shelf?

Count what is physically on the shelf, compare it with the Stock tab, and record every difference as an Adjust row with a reason. Count fast-moving SKUs often and slow ones less often. The gap exposes shrinkage, packing mistakes, and unlogged returns that a single typed stock number would hide.

A ledger is only as honest as its last count. Parcels get packed with the wrong variant. Returns sit unopened in a box. Deliveries arrive short. The count is how you find out.

Run it in five steps:

1. Hide the On hand column, so you count the shelf without seeing the answer.
2. Count one SKU at a time and write the number down.
3. Unhide the column and compare.
4. For each difference, add an Adjust row. The difference is the quantity, and the reason goes in Note.
5. Read the pattern before you move on.

The pattern tells you what to fix:

Where Does an Excel Tracking System Stop Being Enough?

Excel stops being enough when stock must update itself. It has no live link to Shopee MY, Lazada MY, or TikTok Shop, so every sale is a manual entry. Overselling tends to start once you sell on two or more channels, or once several people edit the same file.

Sharing a file fixes part of the problem. Microsoft 365 and Google Sheets both let several people edit at once, so the last save no longer wipes out someone else’s rows. Neither connects to a marketplace, though. Someone still types every sale.

Consider moving to software when:

• You oversold the same unit twice in one month.
• You spend more time entering orders than counting stock.
• A second person needs write access to the ledger.
• You are adding a third or fourth sales channel.

Tools such as Ginee and Sellercraft advertise stock sync for Shopee and Lazada. Check each vendor’s own page for TikTok Shop support and current pricing before you commit. Most tools accept a product list imported from a spreadsheet, so a clean Products tab saves setup time. For a side-by-side view, read our inventory management software comparison for Malaysia.

Frequently Asked Questions

What is an inventory tracking system in Excel?

An inventory tracking system in Excel is a workbook that records every stock movement (purchase, sale, return, adjustment) as a separate row and adds them up with SUMIFS to give quantity on hand per SKU. Unlike a typed stock column, it keeps a history you can audit when Shopee MY and Lazada MY numbers disagree.

Can Excel update my stock automatically from Shopee MY and Lazada MY?

Not on its own. Excel has no built-in connection to Shopee MY, Lazada MY, or TikTok Shop, so each sale needs a manual entry from an order export. Automatic updates need marketplace-sync software such as Ginee or Sellercraft, or a custom import that you build and maintain yourself.

Should I use Excel or Google Sheets for inventory tracking?

Both run the same SUMIFS ledger, so the choice depends on how you work. Google Sheets is free, runs in a browser, and lets several people edit at once. Excel copes better with very large files. Neither connects to marketplaces, so you still log every sale by hand.

How often should I count stock against the ledger?

Count fast-moving SKUs every week and slow movers on a longer rotation, such as monthly. Add a count after a sale event like 11.11 or 12.12, when orders and returns spike. Record every difference as an Adjust row with a reason, so the pattern shows up over time.

Is there a free Excel inventory tracking template I can start from?

Yes. Microsoft publishes free inventory tracker templates that open in Excel. Check whether a template logs movements or only stores a stock number. If it only stores a number, add the Ledger tab from this guide so you keep a history of every change.

Keep Reading

• Ecommerce Inventory Management Hub, Malaysia
• Inventory Management Software in Excel: What It Can and Cannot Do
• Inventory Management Software in Malaysia
• Reorder Point Calculator
• SKU Generator