A blank Excel file is a spreadsheet. A well-built one behaves like inventory software: it totals stock, blocks bad entries, merges marketplace orders, and flags low stock. Here is how to build each feature for a Shopee PH and Lazada PH shop, and where the sheet stops.
What Is Inventory Software in Excel?
Inventory software in Excel is a workbook you build to do what stock software does: record every stock change, calculate what is on hand, and warn you when an item runs low. Excel ships with none of this. You build each part, and you feed it data yourself.
A workable build uses three tabs:
| Tab | What it holds | Key columns |
|---|---|---|
| Products | One row per SKU | SKU, product name, unit cost (₱), reorder point, supplier |
| Movements | Stock changes you enter by hand | Date, SKU, channel, movement type, quantity |
| Orders | Marketplace sales, imported from exports | Order number, SKU, quantity, order status, channel |
Stock on hand is never typed into a cell. It is calculated from Movements and Orders. That one rule is what turns a list into a stock system.
Use one SKU code everywhere: in this workbook, on Shopee PH, and on Lazada PH. Our SKU generator builds consistent codes. For the full build order, see our guide to an inventory management system in Excel. For what a spreadsheet cannot do, read what Excel can and cannot do as inventory software. This guide covers the four features that make the workbook feel like software.
Why Build Your Stock Log as an Excel Table?
Format each tab as an Excel Table: select the range and press Ctrl+T. A table grows when you add a row, and a formula entered in one cell fills the whole column. That removes a common spreadsheet failure, where a formula stops one row short of your newest data.
Per Microsoft’s overview of Excel tables, tables also support calculated columns and structured references, which let a formula use table and column names instead of cell addresses.
Stock on hand for each SKU becomes one readable formula in the Products table:
=SUMIFS(Movements[Quantity],Movements[SKU],[@SKU])-SUMIFS(Orders[Quantity],Orders[SKU],[@SKU])
Read it left to right. Add up the Movements quantity for this row’s SKU, then subtract the units sold in Orders for the same SKU. Deliveries are positive numbers in Movements. Damage and manual sales are negative. Microsoft’s SUMIFS guide covers the function itself.
Never delete rows from Movements. When a count is wrong, add a correcting row with the reason. The log is your audit trail.
How Do Drop-Down Lists Keep Stock Data Clean?
Bad entries break totals quietly. “Shopee PH” typed three different ways splits your channel report, and a SKU with a trailing space matches nothing, so the stock never moves. Data validation fixes this by forcing a pick from a list.
Set a drop-down on three Movements columns:
- SKU. Source it from the Products table, so a code that does not exist cannot be entered.
- Channel. Values such as Shopee PH, Lazada PH, TikTok Shop, Facebook or Messenger, and Walk-in.
- Movement type. Values such as Received, Manual sale, Damaged, Return to sender, and Count adjustment.
According to Microsoft’s drop-down list guide, a drop-down based on a table updates on its own when you add or remove items. Add a new SKU to Products and it appears in the list.
Give failed deliveries their own movement type. A cash-on-delivery parcel that a courier such as J&T Express or LBC brings back should add stock only once it is physically on your shelf and checked, not when the tracking page says it is on its way.
A barcode scanner fits into this setup. Most USB handheld scanners type the scanned code into the selected cell, as if it came from a keyboard. Click the SKU cell, scan, and the code lands there. The barcode must carry the same SKU your Products table uses, or the drop-down rule will reject it. Our pick and pack guide lists typical scanner costs in PHP.
How Can Power Query Merge Shopee PH and Lazada PH Orders?
Power Query is the feature that gets Excel closest to software. It records your clean-up steps once and repeats them on every new file. Microsoft describes it as a data preparation engine that saves your steps as a repeatable query you can refresh. Check that a Get Data button appears on your Data tab, because availability depends on your Excel version.
The routine, step by step:
- Make one folder per marketplace, for example “Shopee PH orders” and “Lazada PH orders”. Export orders from each Seller Centre and save every new file into its folder. Export options change, so check what your account offers.
- Load each folder. Go to Data, Get Data, From File, From Folder. Choose Combine & Transform Data, which Microsoft’s folder connector page lists as the easiest way to combine files in a folder.
- Clean each query once. Keep order number, SKU, quantity, and order status. Rename them to the same names in both queries. Add a Channel column with a fixed value.
- Stack the two queries. Use Home, Append Queries as New. Per Microsoft’s append documentation, Power Query matches columns by header name, so mismatched names leave empty columns.
- Load the result as the Orders table.
- Refresh. Drop new exports into the folders, then choose Data, Refresh All.
Three traps to plan for:
- Overlapping exports. If each export covers the 1st of the month to today, the same orders load twice. Remove duplicates on order number plus SKU inside the query.
- Cancelled and failed orders. Pick one rule. Either the query drops them and you log nothing in Movements, or the query keeps them and you log the return. Mixing both counts the stock twice.
- No live link. Refresh reads your saved files. It does not connect to Shopee PH or Lazada PH, so stock is only as current as your latest export.
How Do You Build Low-Stock Alerts in Excel?
Add a Reorder point column and a Status column to Products. The Status formula compares what is on hand with the reorder point:
=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")
Then add a conditional formatting rule that shades the row when Status reads “Reorder”. Put =COUNTIF(Products[Status],"Reorder") at the top of the sheet so one number tells you how many SKUs need attention.
The reorder point is daily sales times supplier lead time, plus a safety cushion. Here are made-up numbers, so swap in your own. A phone-case SKU sells about 3 a day, your supplier takes 10 days, and you want a cushion of 9. That gives 3 × 10 + 9 = 39 units. At ₱120 a unit, stock sitting at the reorder line is worth ₱4,680. When on hand reaches 39 or lower, the row is shaded red. Our reorder point calculator does the sum for your real SKUs.
One limit matters here. Excel does not message you on its own. The flag shows only when someone opens and refreshes the file.
Where Does the Workbook Stop and Inventory Software Start?
The workbook stops at the edge of your own exports. Everything above makes your data cleaner and your routine faster. None of it connects to your stores.
Move to a dedicated tool when one of these becomes routine:
- Campaign days outrun your exports. On 9.9, 11.11, and 12.12, orders arrive faster than you can export and refresh.
- More than one person edits stock. Shared files help, but overlapping edits on the same row still lose entries.
- You upload stock by hand. Excel can prepare a stock file, but you still upload it to each marketplace.
- Variants multiply. A shirt in 5 sizes and 4 colours is 20 rows from one product.
Based on our research, tools such as Ginee and Sellercraft connect to Shopee and Lazada stores and update stock across them. Pricing varies by tool, order volume, and store count, and changes often, so check each vendor’s current page. Our inventory management software comparison lays out the options for Philippine sellers. Many sellers keep the workbook for cost sheets and month-end reports after they switch.
Frequently Asked Questions
Is there free inventory software in Excel?
The build above costs nothing beyond Excel. Free templates also exist. Before you trust one, check that it separates stock changes from the summary, has a channel column, and uses PHP for costs. A template with one editable “stock” cell keeps no history.
Do I need Power Query, or can I paste exports by hand?
Pasting works if you update once or twice a week. Power Query pays off when you refresh often, because it repeats your clean-up steps for you. If Get Data is missing from your Excel version, paste both exports into the Orders table and keep the same column names.
Can I use Google Sheets instead of Excel?
Google Sheets can run the same SUMIFS logic and drop-down lists. Power Query is an Excel feature, so the import routine above does not carry over as is. Choose the tool your team already opens every day.
Can Excel update Shopee PH or Lazada PH stock automatically?
Based on our research, no. Neither Seller Centre connects to Excel by itself. Anything automatic needs a connector built on the marketplace’s platform interface, which dedicated inventory tools provide. Check each Seller Centre’s current bulk-update options for uploading a stock file by hand.
Keep Reading
- Inventory Management Software in Excel: What It Can and Cannot Do for PH Sellers
- Inventory Management System in Excel: A Philippine Seller’s Complete Guide
- Inventory Tracking System in Excel: What It Tracks and When to Upgrade
- Reorder Point Calculator
- Ecommerce Inventory Management for Philippine Sellers