Keep your stock figures in a spreadsheet, then sync them into Shopify. As a concept it sounds wonderfully simple, but once you actually start running it, you tend to hit little snags: for some reason only this one site is off, or the sync reports success yet the shop-floor quantity never moves. And most of the time the cause is not the sync settings at all — it is the single question of where the numbers are being sent.
Syncing inventory in Shopify is not the act of writing a number onto a product. The destination is always a combination: which item, at which location. In this article we will get you to the point where you understand locations properly and can explain, in your own words, exactly where one row of your sheet lands inside Shopify. We will also walk through the checks that keep your very first sync from going sideways.
A sync writes to a location, not to a product
The first thing to settle when designing an inventory sync is the destination of the numbers. Start syncing while that is still vague and you can prepare perfectly correct figures that arrive in completely the wrong place. So let us begin with the structure: how Shopify actually holds inventory.
An inventory item and an inventory level are different things
In Shopify you have products and their sellable variants, and behind those sits something called an inventory item. That is what your SKU is attached to. The inventory item itself, however, does not hold a quantity. The number lives on the inventory level, which is the inventory item crossed with a location.
In other words, inventory sync is not the job of updating products. It is the job of updating inventory levels — the numbers sitting at the intersection of item and location. Once that clicks, it makes perfect sense why a sync setup asks you to choose a location, and the fact that the same SKU carries different quantities at different sites stops feeling like an anomaly and starts feeling entirely natural.
Sending quantities to the wrong location happens silently
Because the destination is an intersection, getting one location wrong sends the whole set of numbers flowing into a different site. What makes it awkward is that this mistake never surfaces as an error. The sync completes normally and the log records a success. It is just that the thirty units meant for the Tokyo warehouse are now sitting in Osaka.
You usually find out from the shop floor rather than the dashboard. The store is sold out while the website still shows stock, or fulfilment requests fly off to a site you did not expect. When symptoms like that appear, before you start doubting the figures in your sheet, check which location those figures are configured to reach. It is remarkable how often the culprit is the sync target.
The trap of leaving it to the default location
While you only have one location, you barely need to think about where things are written. There is a single default, so everything arrives in the right place without you specifying anything. The problem arrives the moment you open a store or add a 3PL and end up with two locations or more.
The quiet assumption of just use the first location suddenly becomes the wrong assumption. Right after you add a location, always revisit which site your existing sync setups are pointing at. The usual shape of this problem is not the newly added side being misconfigured — it is the setup that has been quietly running all along being left behind.
Of the three numbers, your sheet should own only one
Inventory at a location is not a single number but a set of states. Build a sync while those are muddled together and you will never quite see why your sheet and Shopify disagree. Let us sort on hand, committed and available by the role each one plays. Available versus on hand, explained walks through all five states.
On hand is what your sheet is responsible for
On hand is the quantity physically present at that location. Think of it as the number closest to reality: count what is on the shelf and what is stacked in the back room, and that is your figure. It goes up when goods arrive, down when they ship, and gets reconciled against a physical count at stocktake. That rhythm maps directly onto the daily work of a warehouse or a store.
Which is exactly why on hand is the number your spreadsheet should own as the source of truth. Purchasing types in the receipts, the store enters the stocktake results, and the sheet holds one on-hand figure per location that flows straight into Shopify on the next sync. Set it up this way and responsibility for each number becomes obvious, so nobody has to wonder who is supposed to update what.
Committed is born from orders
Committed is the quantity that has been ordered but not yet shipped. The moment a customer buys, the goods are still sitting on your shelf, but they already belong to somebody else. Shopify calculates this number automatically from the state of your orders — it is not the kind of figure you write in from outside.
Get this backwards and send an on-hand figure that you have already reduced by the committed amount, and Shopify ends up double-deducting. Your sheet only needs to watch what is on the shelf; leave the movement of orders to Shopify. Holding that division of labour is the surest way to keep the numbers from drifting apart.
Available is a number that moves on its own
Available is what remains once committed and similar states are subtracted from on hand — the quantity still free to sell. It is the figure behind the stock shown on your online store and behind the decisions that prevent overselling. Because it is derived from on hand and committed, it drops by itself every time an order comes in.
So if you look at the admin right after a sync and feel that the displayed number disagrees with your sheet, first check whether you are looking at available. On hand can be exactly as specified while available shows less, simply because unfulfilled orders exist at that moment. That is not a fault — it is proof that everything is working correctly.
Deciding the shape of your sheet: what does one row mean?
Once the destination structure is clear, the next question is how to express it in the spreadsheet. The shape you choose here becomes the day-to-day ease of your operation. Nothing clever is required; holding to a single principle is enough to keep it tidy. One sheet, many locations shows the column-per-location layout in full.
One row equals one SKU at one location
Since an inventory level is an item crossed with a location, the honest design is to make one row of the sheet mean the same thing. SKU in column A, location in column B, on-hand quantity in column C. That is all it takes for the structure of your sheet to line up neatly with the structure of Shopify. If the same SKU exists at three sites, that product occupies three rows.
You get more rows, but the layout has real advantages. You can see at a glance where each row is headed, and adding a site is nothing more than adding rows. Because the columns never change, the work of everyone sharing the sheet stays exactly the same. If you are unsure where to start, this tall layout is the one we would recommend.
One column per location
The other option is to make one row mean one SKU and hold each location's on-hand quantity in its own column. SKUs appear once going down and sites spread out across, which makes it far easier to compare stock balance between locations. Plenty of people also find it a friendlier surface for entering stocktake results.
- Tall layout (one row = SKU x location): resilient as sites come and go, and it is obvious where each row is going
- Wide layout (one column per location): easy to compare stock across sites and pleasant for stocktake entry
- The tall layout grows in rows, the wide one in columns. Wide tends to suit a handful of sites; tall gets easier as the count rises
- Whichever you pick, the principle holds: one cell always means the on-hand quantity of one SKU at one site
Neither one is the correct answer. What matters is that a single cell in your sheet consistently means the on-hand quantity of one SKU at one site. Keep that consistency and either shape syncs just as well as the other. You can switch shapes later too, but when you do, always try a small batch before rolling it out across everything.
Bind to the location itself, not to its display name
Run an operation for a while and location display names change. Head Office Warehouse becomes East Japan DC; a store name gets rewritten after a move. These are names for humans to read, so change is entirely natural. The problem is that if your sync target relies only on that name as a string, it loses its destination the instant the name changes.
The safe approach is to bind the sync setup to the location itself — its identity rather than its label. On the sheet side, keeping a column for a stable identifier or internal code alongside the readable name preserves the mapping even when the name moves. And whenever you rename a location, open the sync settings and confirm the destination survived. That small habit prevents genuinely large accidents later.
Three things to check before your first sync
With the design settled, it is time for the first sync — though pushing your entire catalogue straight away is not something we would recommend. Inserting the three checks below removes almost every first-run stumble, and each of them takes only a few minutes.
Confirm the destination with a connection test
Before running a sync, run a connection test and confirm that your sheet and Shopify are properly linked. What you are looking at is not merely whether a connection succeeds. The real subject is the destination: which store, and which location, is this pointing at?
Insert this check without fail if you keep both a development and a production store, or immediately after adding a location. If numbers you meant as a test overwrite live inventory, putting things back is far more work than you would expect. A connection test takes on that risk for you in a matter of seconds. Running a connection test first describes exactly what it reveals.
Check that inventory tracking is enabled
Shopify lets you decide per product whether inventory is tracked. A product with tracking switched off has no concept of a quantity in the first place, so no amount of syncing will put per-location numbers into it. A large share of the sync succeeds but nothing shows up questions come down to exactly this setting.
Even with tracking enabled, if the product is not set up to be stocked at that location, the numbers still will not land. When you add a new site, you need to stock the relevant products at that location. Checking tracking settings and location assignments together before the first sync saves a lot of puzzlement.
Start small and inspect the difference
Once you are ready, sync a small batch of five to ten rows first. Push everything at once and, if something is off, isolating the cause becomes dramatically harder. With a small batch you can verify each one by eye in the admin.
- 01Pick five to ten rows that include the cases you want to prove out, such as SKUs spanning sites and products at zero
- 02Run the connection test and confirm the destination store and location are what you intended
- 03Sync only that small batch, then check the per-location on-hand quantities in the admin by eye
- 04If the sheet values match Shopify's on hand, widen the scope and move on to the full sync
When you check, make sure you are looking at the on-hand field. Available is affected by unfulfilled orders and may legitimately differ from your sheet. If on hand matches what you specified, the sync is working correctly. Once you have confirmed that, widen the scope, run the whole set, and from there put it on a schedule.
Boiled down, inventory sync is the work of delivering the right number to the right intersection. Spreadsheets are excellent at the part where you prepare the numbers, and choosing the right intersection is straightforward once you understand the unit called a location. With this much in hand, growing from two sites to five changes nothing about the way you think.