Once you hold stock at more than one location, the first thing you agonise over is how many sheets to keep. Splitting into one file per warehouse looks tidy at first glance, but viewed through the lens of Shopify inventory sync, operations get heavier with every extra file. In this article we lay out all your locations as columns in a single sheet, and walk through the column design that syncs stock into Shopify — from the thinking behind it to the day-to-day running of it.
The subject here is inventory sync, always. The spreadsheet is not a calculation tool: it is the single source of truth that decides Shopify's quantities. Once it is clear which column holds which location's on-hand figure, sync behaves astonishingly obediently. Put the other way round: a good share of syncs that go wrong are traceable to the layout of the sheet itself. Deciding how the columns are arranged looks unglamorous, but it is the preparation that most determines whether inventory sync succeeds. Before learning any difficult settings, getting the shape of the sheet right is the shorter road.
Why put everything on one sheet
If you have three sites, surely three sheets are easier to manage? That is the natural first instinct. Yet once you actually start operating, the extra effort caused by the split is what stands out. Let us look at the benefits of consolidating into one sheet from three angles. Handling multi-location inventory in Google Sheets gives the wider view first.
The right answer lives in one place
The scariest state in inventory sync is not knowing which number is correct. With a file per warehouse, information about the same SKU is scattered across several places, each carrying a slightly different last-updated time. One file counted yesterday, another still sitting at last week's figures — that drift will appear sooner or later, without anyone noticing.
Consolidate into a single sheet and there is always exactly one place to open when you want to know a SKU's stock. The sync configuration gets simpler too — one spreadsheet, one sheet name — so you never have to recall which file it is reading. When something goes wrong, the investigation is dramatically faster, because the search area is limited to a single sheet.
On the Shopify side, inventory is likewise tracked tied to each location. One row per SKU, with each column on that row holding one location's on-hand quantity, maps directly onto the way Shopify holds stock. Lining up the shape of your data is the shortest route to a stable sync.
Site-by-site differences sit side by side
The second benefit of consolidating is that imbalances between sites become obvious at a glance. Simply reading across a row shows you that Tokyo has 30 while Osaka is at 0. With separate files, that comparison means hopping between two windows every time, so the habit of comparing never sticks in the first place.
When the table is laid out horizontally, observations like these emerge naturally in the course of everyday work.
- SKUs that are extremely high, or extremely low, at one location only
- Products with no stock at any site, which should be pulled from sale
- Cells left blank because someone forgot to enter them
- Suspicious rows unchanged since last time, despite a fresh stock count
Those observations feed straight into the quality of your inventory sync. If you catch an odd number while it is still in the sheet, you can fix it before it reaches Shopify. Sync is a mechanism that faithfully carries whatever is written, so whether you notice before it lands is what makes the difference.
Fewer handoffs between people
The third benefit is operational. When files are split by warehouse, you tend to get a handoff: somebody updates the file they own, and somebody else consolidates it. Each handoff generates its own round of chasing — has it arrived yet, which copy is the latest? With a couple of sites it barely registers, but it quietly compounds as locations multiply.
If everyone shares a single sheet, each person's job is done the moment they write a number into their own column. The consolidation step disappears entirely, and whatever has been written by the time the sync runs is what reaches Shopify. The more people involved, the wider that gap becomes. The edit history also stays in one place, so you can check later when and by whom a number was written — which helps when you need to verify the result of a sync.
A column layout that syncs easily
Now for the main subject: column design. Sheets whose sync runs well share a common shape. The key column at the far left, the per-location quantity columns beside it, and the human-only columns set well apart from those. Decide that arrangement once at the start and the rest is just filling it in day by day.
Pin the key column at the left
First, put the key column that identifies each row at the far left. Normally that is the SKU. Sync works in the shape of 'set the quantity for this SKU at this location to this value', so nothing can begin if the key cannot be read. Sitting at the left, it stays in view both when a person is scanning down the sheet and when they scroll sideways.
The rules to observe in the key column are few. No blanks, no duplicates, and no stray whitespace before or after the value. Just those three. Leading and trailing spaces in particular are invisible to the eye, which makes them a classic cause of what you think is the same SKU being treated as something else. Freeze the header row and the key column and you will never lose track of which product you are editing, however far down you scroll. If you use another identifier such as a barcode as the key, the thinking is exactly the same. What matters is that a value which reliably ties one row to one SKU always sits in the same place. The three-layer idea comes from inventory sheet column design.
One column per location
Next, give every Shopify location exactly one quantity column. Name the heading so the location is unambiguous, and put nothing in it but the on-hand figure. Never let a single column carry two meanings — that is the most important principle in multi-location inventory sync.
Writing down the ground rules for these columns in advance saves a lot of hesitation later.
- One column, one location — never a shared or dual-purpose column
- Name headings so they can be matched to a Shopify location
- Whole-number quantities only — no units, no words such as 'pcs'
- When a new site opens, append the column at the end rather than wedging it between existing ones
- For a column you no longer use, remove it from the sync scope before deleting it
Settle the difference between blank and zero at this stage too. Standardise on zero as a clear statement that stock at this site is nil, and blank as meaning it has not been counted yet. Say it out loud within the team and the interpretation will hold even after the person responsible changes.
Keep the human-only columns apart
A sheet inevitably picks up information that has nothing to do with sync: product names, suppliers, bin numbers, staff notes, expected delivery dates. All of it matters to the business, but none of it is a value to send to Shopify. That is exactly why it belongs in a block clearly separated from the quantity columns.
Our recommendation is to gather the key column and the quantity columns on the left of the sheet, leave a single empty column, and place the human block to the right of it. That one blank column at the boundary is enough to give everyone a shared understanding that nothing to the right is synced. Words like 'notes' or 'reference' in the headings help too.
This separation is not only about preventing accidents. With the quantity columns in one contiguous block, selecting a range, setting up conditional formatting and totalling figures all get easier. When the layout is straightforward, every part of running the sheet gets lighter.
Sync settled values, not formulas
In the pursuit of efficiency it is tempting to put formulas in the quantity columns — last count plus receipts minus shipments, and so on. The impulse is completely understandable, but for the cells that sync actually reads, we recommend aiming for a state where a settled, final number is sitting there.
The reason is simple: formulas move. Delete a referenced row, re-sort the sheet, update data on another tab — and the value in the quantity column changes with it. In an operation where sync runs while nobody is watching, nobody can say afterwards which moment's calculation was actually sent.
You do not have to give up calculating. Keep the working columns over on the human side, check the result, and paste it into the quantity column as a value. That one small extra step turns the numbers handed to sync into figures a person checked and committed to. It also makes ownership of each number clear, which helps enormously when you look back later.
How to operate this arrangement
Deciding the layout is not enough — without operating rules, the sheet will drift back into its original chaos within six months. Preserving the column design takes three things: a clear division of who edits what, a mechanism that protects the layout, and a regular review.
Decide who edits which column
Since everyone is using a single sheet, the first thing to settle is who edits what. The underlying idea is simple: each location's quantity column is written by the person accountable for stock at that site, and everybody else only reads it.
When you sit down to decide, working through it in this order leaves no gaps.
- 01List the locations on the Shopify side
- 02Assign one person, or one team, as editor of each quantity column
- 03Separately decide who is allowed to touch the key column and the header row
- 04Open up the human-only columns as an area anyone may write in
- 05Record what you decided at the edge of the sheet or in an operations document
With ownership settled, you immediately know who to ask when a number looks wrong. This is not about assigning blame; it is a mechanism for finishing the check quickly. Inventory sync problems widen the longer it takes to isolate the cause, so this division pays off more than you would expect.
Protect ranges and validate input to hold the layout
Do not leave the rules you have agreed resting on goodwill alone. Google Sheets can protect a sheet or a range and limit who may edit it. Simply protecting the header row and the key column prevents almost every accident in which the layout falls apart.
Alongside that, set data validation on the quantity columns. Accept only whole numbers of zero or more and full-width digits, negative values and accidental text get rejected on the spot. Stopping a mistake the moment it is typed is far easier than chasing an error after the sync has run. Attach a help message to the validation rule and the person sees on the spot why the entry was rejected, and can fix it themselves. Having the sheet explain the rule is more reliable than expecting everyone to memorise it.
Protection and validation effectively move the blueprint of your layout off paper and onto the sheet itself. What you configure survives a change of staff. Everything you used to explain verbally at each handover becomes a property of the sheet.
Pair a scheduled sync with a scheduled review
With all that in place, hand the sync over to a schedule. Running it by hand slides down the list precisely on the busiest days, and the stock gap widens as a result. With a mechanism that runs at a fixed time even once a day, whatever is written in the sheet will always reach Shopify. Set the run time for after the stock count and goods-in work are finished, and it is the day's latest figures that get reflected.
When you set it up for the first time, or after adding a column, run a connection test before the real thing. Confirming once, against real data, which column is read as which location lets you hand the job to automation with confidence.
And it is precisely after automating that reviews are needed. Once a month is plenty — just run your eye over the following.
- Whether the column order still matches the original design (nothing inserted or deleted)
- Whether each synced column still corresponds to the right Shopify location
- Whether any cells have been left blank and forgotten
- Whether the ownership list matches who is actually writing the numbers
Syncing inventory for many locations from a single sheet is not a feat of technology; you get there simply by settling how the columns are arranged. Key column at the left, per-location quantity columns beside it, human columns set apart. Then assign ownership, apply protection, and keep a scheduled sync and a scheduled review turning. Reach that shape and, however many sites you add, the right answer for your stock is always inside the same single sheet. Build the review report with reporting on-hand inventory by location.