One Sheet, Many Locations: Driving Quantities Column by Column

The horizontal layout — one SKU per row, with locations spread across columns — is appealing because it lets you take in every site at a glance on a single sheet. But ‘easy to read’ and ‘pushes in correctly’ are two different things. In this article we walk step by step through the implementation tricks for reliably mapping each location column to a real Shopify location and pushing quantities in safely.

As a baseline: in Shopify, inventory is tracked per location as states (available, committed, on hand, and so on). A product can only be assigned a quantity at a location once it is set to stock inventory there. It helps to frame the sheet as the source of the ‘on hand’ figure you push in — a clear division of roles.

How column headers map to locations

The heart of a horizontal sheet is how each column header string ties to a location name on the Shopify side. If you have three columns — Tokyo Warehouse, Osaka Warehouse, Shibuya Store — each must correspond precisely to a same-named location registered in Shopify. Get this misaligned and you are one step from an accident where Tokyo’s numbers flow into Osaka.

There are broadly two ways to do the mapping. One is to make the header string match the Shopify location name exactly. The other is to set an explicit mapping on the sync side that says ‘this column is this location’. With the latter you can keep your in-house nicknames as the sheet headings while quietly tying them to the official Shopify names behind the scenes.

Building the header row, and what to watch for

The header row is the sync app’s only clue to ‘which column means what’. At minimum, get a column that identifies the SKU (or barcode) and a quantity column for each location sitting cleanly in row one. Following these points makes the later setup much smoother.

• Always put headers in row one, with no blank or title rows above them
• Make each location heading a unique, non-duplicated name within your store
• Arrange things so quantity columns are easy to tell apart from notes, cost, bin numbers, and the like
• Do not use merged cells; merges easily break column reading

Handling the difference between an empty cell and zero

It is easy to overlook, but an empty cell and a zero mean entirely different things. A zero is a clear instruction — ‘inventory at this location is zero’ — and pushing it in updates the quantity to 0. An empty cell, by contrast, means ‘no value’, and whether you treat it the same as zero or leave it out of scope and keep the current figure changes operations dramatically.

Our recommendation is to write an explicit 0 whenever you intentionally want to zero out stock, and leave cells blank when you have not counted them yet or do not want to touch them. On top of that, know in advance how your sync interprets a blank cell — does it skip it, or overwrite it as 0? Run things while misunderstanding this and you risk a heavy accident: zeroing out, all at once, stock you simply had not counted.

Limiting the sync to quantity columns only

A single sheet tends to hold all sorts of information beyond quantities — product names, cost, supplier, notes. The only things you want the push to touch are the location quantity columns, so narrow the synced columns explicitly. That prevents accidents where a notes field is mistaken for a quantity.

1. 01Designate one key column (SKU or barcode)
2. 02Explicitly select, per location, the columns to read as quantities
3. 03Exclude reference columns such as cost and notes from the sync
4. 04Use a connection test to confirm the chosen columns read as intended

Sync Master includes a connection test that validates your mapping before any real push. Seeing once, on real data, which column is read as which location’s quantity lets you hand the job over to scheduled, automatic sync with confidence.

Adding or removing location columns safely

A new warehouse opens; a store closes — such changes show up in the sheet as added or removed columns. This is precisely the weak point of the horizontal layout: touching columns easily knocks the existing mapping out of line. To proceed safely, the cardinal rule is to keep to this order.

1. 01First add or deactivate the location on the Shopify side
2. 02Append the new column at the end of the sheet; do not wedge it between existing columns
3. 03In the sync mapping, tie the new column to the new location
4. 04Confirm the read with a connection test before running a real sync

A pre-flight totals check before pushing

Finally, take the small extra step of a sanity check before you push. On a horizontal sheet, summing each row’s location columns across gives you that SKU’s total stock. Simply glancing at whether that total is wildly off from your stock-count gut feel or last time’s number catches a surprising number of typos and misplaced digits.

• Add one row-total column to surface total stock per SKU
• Use a column total to check whether each location’s grand total is realistic
• Use conditional formatting to colour out any stray negatives or blanks

Driving quantities into locations column by column, with a single sheet as the source of truth, runs astonishingly stably once you firm up the header design and the mapping. Decide how empties and zeros behave, narrow the target columns, and make the totals check a habit. Nail those three and you can comfortably automate inventory across all your sites.