Once you run several warehouses or shops, grasping the whole picture of ‟what is where, and how many” suddenly gets hard. Shopify’s admin does show inventory per location, but as your catalog grows the overview fades, and seeing across locations which one is running low becomes a chore.
So this time we focus on the reporting side rather than data entry. Using pivots and formulas, we will walk through how to build a report from your Google Sheets inventory master that visualizes on-hand stock per location and helps you spot low stock and lopsided inventory.
Building a Report That Shows Per-Location Stock at a Glance
First, the groundwork: a Shopify location is a place that stocks, fulfills, and sells inventory. Inventory is recorded per location as states (available, committed, on hand, and so on), and even for the same SKU the numbers at warehouse A move separately from store B. The point of a report is to line those separate numbers up in a single table so you can read the overall situation quickly.
The ideal layout is a matrix: SKUs down the rows, locations across the columns, and on-hand counts in the cells. Add a total column on the far right and a total row along the bottom, and you can read each SKU’s grand total and each location’s grand total at once. If your master sheet is one row per SKU with per-location quantity columns, it already forms the foundation of the report.
Aggregating SKU by Location with a Pivot Table
If your master is in a tall format — one row per location with SKU, location name, and quantity in three columns — a pivot table is the quickest way to aggregate. Put SKU in rows, location name in columns, and the sum of on-hand quantity as the value, and a SKU-by-location matrix builds itself. As the source data grows, widen the range and refresh to bring it in.
- 01Select the master range and choose ‟Insert pivot table” from the menu
- 02Put SKU (or product name) in rows
- 03Put location name in columns
- 04Put on-hand quantity in values, with ‟SUM” as the summary
- 05If useful, add a second value to view total and available side by side
Viewing Total Stock and Available Stock Separately
An easy thing to confuse in a report is the difference between ‟total (on hand)” and ‟available (sellable).” On hand is what physically sits in the warehouse; available is what remains after committed quantities and buffers are subtracted. Place the two as separate columns and you start to see locations where ‟physical stock exists but the sellable count is low.” That can be a sign of heavy commitments or an over-thick buffer.
Stockout and Low-Stock Alerts by Location
Once the matrix exists, the next move is to make the cells that need attention stand out. With Google Sheets conditional formatting — ‟red if on hand is 0, yellow if at or below a threshold” — you can see at a glance which SKU at which location is at risk. The threshold can vary per location: set it higher for a fast-shipping warehouse and lower for a slowly moving store to match reality.
- Shade cells where on hand is 0 in red to catch stockouts instantly
- Mark cells at or below a threshold (say, under 5) in yellow to surface restock candidates
- Use COUNTIF on a side table to tally ‟number of out-of-stock SKUs” per location
- Use FILTER to auto-extract a list of low-stock SKUs and their locations
Beyond color coding, a list that pulls out only the low-stock rows is handy too. Auto-extract ‟rows where on hand is below the threshold” elsewhere with FILTER, and the restock owner can simply work down that list from the top. Just remember to include the location name alongside each one.
Metrics That Reveal Inventory Imbalance
A common trap with multiple locations is lopsided stock. The grand total is fine, yet it piles up in one warehouse while a high-demand location runs dry — and looking only at totals, you never notice. Per SKU, take the gap between ‟highest location stock” and ‟lowest location stock,” or compute a location’s share (its slice of that SKU’s grand total), and the imbalance becomes visible as numbers.
For example, ‟90 percent of a SKU sits in warehouse A while the best-selling store B is nearly empty” is a clear sign to consider a transfer or restock. Adding just one imbalance-metric column to the report makes prioritizing counts and orders far easier. No tricky formulas needed — a combination of MAX, MIN, and SUM is plenty.
A Setup That Keeps the Report Current
A report is not done once built; its value comes from continuing to reflect the latest stock. A report assembled from pivots and formulas recalculates automatically when the underlying master is refreshed. The real question is how to keep that master itself current.
Sync Master supports multiple locations and treats Google Sheets as the single source of truth, writing on-hand quantities into each Shopify location automatically. Set up scheduled sync and the figures in your sheet and the stock on the Shopify side line up regularly, so a report built from the sheet stays close to reality. Before a real sync, its connection test lets you confirm the column-to-location mapping, so you can also check up front that the columns your report references point to the right locations.
A per-location inventory report is perfectly achievable with nothing but Google Sheets pivots and conditional formatting — no special tool required. Start with stockout color coding and a total column, then add imbalance metrics as you get comfortable. The more you visualize, the faster you can judge where to restock and where to move stock from, cutting both stockouts and overselling.