Analistable

How to build a consolidation worksheet template in Excel

By the Analistable team · Updated · 5 min read

A reliable consolidation template has identical input sheets (one per entity, month or department), two empty marker sheets called Start and End around them, and a Summary sheet whose cells use 3D formulas such as =SUM(Start:End!C5). Add a check row that compares the summary total with the sum of each sheet's own total, so a broken link shows immediately. Want it ready-made? Download the free consolidation template.

Part of our guide: How to consolidate data in Excel

The structure

Sheets in the template, in tab order
SheetPurpose
SummaryConsolidated figures (formulas only)
ChecksControl totals and error flags
StartEmpty marker — first sheet in the 3D range
Input sheets (e.g. North, South, West)Same layout, values typed or pasted
EndEmpty marker — last sheet in the 3D range
ListsAccount or product codes, entity names

Step 1: design one input sheet and copy it

  1. Build the input layout once: labels in column B, months or categories across row 4, numbers in C5:N40.
  2. Lock the layout: select the input cells, Format Cells → Protection → untick Locked, then Review → Protect Sheet. Users can type numbers but not move rows.
  3. Add a total row (row 41) with =SUM(C5:C40) across.
  4. Copy the sheet for each entity (Ctrl-drag the tab) and rename the copies.

Identical layouts are what make 3D formulas safe: C5 means the same line on every sheet.

Step 2: the Summary sheet

Copy the input layout to Summary, delete the numbers, and in C5 enter:

=SUM(Start:End!C5)

Fill across and down. Every input sheet placed between Start and End is included. To add a new entity, copy an input sheet and drop it between the markers.

Step 3: checks

Summary total:        =Summary!C41
Sum of sheet totals:  =SUM(Start:End!C41)
Difference:           =ROUND(C2-C3, 2)
Status:               =IF(C4=0, "OK", "CHECK")

Add a conditional format that turns Status red when it isn't OK. A difference appears when a formula on the Summary was overwritten or a row was inserted in only one input sheet.

When labels differ between entities

If entities report different line items, positional 3D formulas won't work. Use Data → Consolidate by label (see the Consolidate function), or switch to a long-format input (one row per entity, account and month) and summarise with a pivot table. For criteria-based totals across sheets, see SUMIFS across multiple sheets.

If the inputs arrive as separate files every month, stack them with Power Query From Folder instead of pasting into a template — see merging Excel files.

Worked example: three entities

North, South and West sit between Start and End. Their row 41 totals for January are 12,400, 9,850 and 7,300. The Summary's C41 should show 29,550, and the Checks sheet compares it with =SUM(Start:End!C41).

Checks sheet, January
CheckValue
Summary total29550
Sum of sheet totals29550
Difference0
StatusOK

Now suppose someone inserts a row above row 20 in West only. West's total moves to row 42, and row 41 on that sheet now holds the last account line. The sum of sheet totals no longer matches the Summary, Status changes to CHECK, and you know which kind of mistake to look for.

Add per-entity checks

A single overall check tells you something is wrong but not where. Add one line per entity on the Checks sheet that compares each sheet's total row with a SUM of its own detail rows, for example =North!C41-SUM(North!C5:C40). Any non-zero result points to the sheet with the problem. It's also worth a count check, =COUNT(North!C5:N40), to spot an entity that hasn't filled in its numbers yet.

Rolling the template forward each month

  1. Save a copy of last month's file with the new month in the name.
  2. Clear the inputs: select C5:N40 on all input sheets at once (click the first input tab, Shift-click the last) and press Delete. Formulas and labels stay because only the unlocked input cells are cleared.
  3. Ungroup the sheets by clicking the Summary tab — typing while sheets are grouped changes all of them.
  4. Paste or type the new numbers and confirm every Status cell reads OK before sending.

Template mistakes to avoid

  • Placing a sheet outside Start and End, so it's silently left out of every total.
  • Hard-typing a number into a Summary cell to fix a difference — the check then hides the real error.
  • Merging cells in the input area, which stops users pasting blocks of numbers.
  • Letting entities add their own rows. Give them an "Other" line instead, or the positions stop matching.
  • Using different sign conventions (costs as negatives on one sheet, positives on another).

When entities can't keep an identical layout, a long table is easier to consolidate and to question across files; reconciling data in Excel covers matching the results back to source reports.

Version differences

3D references such as Start:End!C5 work in every desktop version of Excel on Windows and Mac, and in Excel for the web. They don't exist in Google Sheets, so a template moved there has to list each sheet in the formula, for example =North!C5+South!C5+West!C5, or stack the ranges with braces. Sheet protection also behaves slightly differently between apps, so test the locked layout in whichever app your entities use before you send the template out.

Document the template

Add a short Notes sheet that lists who fills in each input sheet, the deadline, the sign convention and what each check means. A template passed between people is only reliable if the rules travel with it, and the notes save explaining the same points every month. Date the notes and update them whenever the input layout or the list of entities changes.

Frequently asked questions

How do I make a consolidation template in Excel?
Create one locked input sheet, copy it for each entity, place the copies between Start and End marker sheets, and use =SUM(Start:End!cell) formulas on a Summary sheet.
How do I add a new sheet to a 3D SUM?
Move or copy the new sheet so it sits between the first and last sheets named in the 3D reference.
What if each entity has different accounts?
Use Data → Consolidate by label, or a long-format table with a pivot table, instead of positional 3D formulas.

Related guides