How to combine branch inventory counts into one spreadsheet

What a company stock view needs

After a stock take, each branch usually sends back its own sheet: SKU, description, quantity counted, maybe a bin location. Head office, as in any Excel consolidation, wants the opposite cut: one row per SKU, one column per branch, and a total. Getting there is two moves — stack the counts into one long table, then pivot it — plus a clean-up of product codes in between.

This page covers combining counts across locations. Comparing the counted figures against what your stock system expected is a separate job, covered in reconciling a stock count with inventory records.

Step 1: stack the branch sheets

Every count sheet should share the same columns. A minimal set is:

• SKU — the product code, as text so leading zeros survive.
• Description — useful for spotting errors, but don't join on it.
• Counted qty — a number, with no "approx" or "see note" text.
• Count date — so late counts are visible.

1. Save one file per branch into a folder, named with the branch (Leeds.xlsx, Bristol.xlsx).
2. In Excel, Data → Get Data → From File → From Folder, then Combine & Transform.
3. Rename Source.Name to Branch and strip the .xlsx extension.
4. Set SKU to Text and Counted qty to Whole Number, then load.

If the branches are tabs in one workbook instead, =VSTACK(Leeds!A2:D500, Bristol!A2:D500) in Microsoft 365 stacks them, or use the Consolidate function if you only need totals by SKU and don't mind losing the per-branch split.

Step 2: clean SKUs before you pivot

The pivot groups on exact SKU text, so 00412, 412 and 412 (trailing space) become three products. Before pivoting, add a cleaned column:

=UPPER(TRIM(TEXT([@SKU], "@")))

Then check every cleaned SKU exists in the product master with a lookup; anything returning "not found" is a typo, a discontinued line or a product a branch created locally. Fix those in the source sheet so the next refresh stays clean. Leading zeros lost when a branch opened the file as numbers have to be restored by padding to the standard length, for example =TEXT([@SKU],"00000") for five-digit codes.

Worked example: three branches, four SKUs

With every branch in one view, the useful questions become obvious:

• Glasgow has no black desk lamps while Leeds holds 14 — a transfer rather than a purchase order.
• Bristol has no monitor arms; Leeds holds 22 of the 27 units.
• Cable tray stock is spread evenly, so no action is needed.

Pivot the long table with SKU in Rows, Branch in Columns and Sum of Counted qty in Values. Turn on grand totals for both rows and columns to get the Total column and row shown above.

Adding value and reorder levels

Counts alone show units. To see money tied up, join a unit cost from the product master and multiply. To flag reorders, join the minimum level per SKU and branch. Both are lookups onto the stacked table before you pivot — see joining two spreadsheets on a common column for the XLOOKUP and Power Query Merge options.

SELECT c.branch, SUM(c.counted_qty * p.unit_cost) AS stock_value
FROM counts c
JOIN products p ON p.sku = c.sku
GROUP BY c.branch
ORDER BY stock_value DESC;

In Analistable you can load the branch files and the product master together and ask "stock value by branch" or "SKUs with zero stock in any branch"; it writes and runs the join in your browser, and the count files themselves are not uploaded or changed.

Making the next count easier

• Send branches a template pre-filled with every active SKU and description, so they only enter quantities. Missing products become zeros rather than missing rows.
• Protect the SKU column so it can't be retyped.
• Ask for a count date and counter name per sheet, so a late or partial count is visible in the combined view.
• Keep each period's combined table so you can compare counts over time.

Partial counts and count dates

A company view is only as good as its least reliable branch. Branches rarely count on exactly the same day, and some count only part of the range (a cycle count of one aisle, say). If you combine those without marking them, a zero at a branch that simply didn't count that SKU looks like an empty shelf.

• Keep the Count date column from each sheet and show the earliest and latest date per branch in the summary.
• Distinguish "counted zero" from "not counted": a pre-filled template with every SKU makes a blank quantity mean not counted, while 0 means none found.
• For sales between count dates, either adjust to a common cut-off date using movements from the stock system, or report each branch at its own date and say so.

None of this needs a special tool, but it does need the extra columns to survive the combine step, which is a good reason to stack rather than copy totals across by hand.

Frequently asked questions

How do I combine stock counts when branches use different column names?

Rename the columns in Power Query's sample-file transform, or standardise the headers in each file before combining. Appending matches columns by name, so "Qty" and "Quantity" end up as two separate columns.

Why does the same product appear twice in my pivot?

Almost always because the SKU text differs: a leading zero lost, a trailing space, or lower-case letters. Pivot on a cleaned SKU column rather than the raw one.

Should I use the Consolidate function instead?

Consolidate can sum quantities by SKU across sheets, but it produces a static summary and you lose the branch-level detail unless you set it up carefully. Stacking into a long table and pivoting keeps both.

How do I tell a zero count from a product that wasn't counted?

Send a pre-filled template listing every SKU and ask counters to enter 0 for none found. A blank then means not counted, and you can filter blanks separately in the combined table.

Related guides

• How to reconcile a stock count with inventory records
• How to use the Consolidate function in Excel
• How to combine class and enrolment lists into one master roster
• How to combine franchise P&L and department budget files into one rollup
• How to combine monthly bank statements into one spreadsheet