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.

How to build a consolidation worksheet template in Excel

By the Analistable team · Updated 2026-10-07 · 2 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.

Part of our guide: How to consolidate data in Excel

The structure

Sheets in the template, in tab order
Sheet	Purpose
Summary	Consolidated figures (formulas only)
Checks	Control totals and error flags
Start	Empty marker — first sheet in the 3D range
Input sheets (e.g. North, South, West)	Same layout, values typed or pasted
End	Empty marker — last sheet in the 3D range
Lists	Account or product codes, entity names

Step 1: design one input sheet and copy it

Build the input layout once: labels in column B, months or categories across row 4, numbers in C5:N40.
Lock the layout: select the input cells, Format Cells → Protection → untick Locked, then Review → Protect Sheet. Users can type numbers but not move rows.
Add a total row (row 41) with =SUM(C5:C40) across.
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.

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.