Consolidation template for Excel
By the Analistable team · Updated · 3 min read
A general-purpose workbook: paste rows from every source (team, site, file, entity) into the Data sheet with a Source and Category column, and the Consolidated sheet builds a category-by-source grid with SUMIFS, row and column totals, and a check that the grid equals the raw data.
Part of our guide: How to consolidate data in Excel
What the template does
Most consolidation jobs have the same shape: several sources report the same categories, and you need one grid that adds them up. This template is deliberately generic. Sources can be teams, branches, files or entities; categories can be products, cost lines or anything else. For a period-by-period month-end pack or a group P&L with eliminations, use a purpose-built template; this one is for everything else.
It uses one long Data table rather than one sheet per source, which avoids the fragile 3D references and the Consolidate command that does not update. The background is in the consolidating data in Excel guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Data | Source, Category, Period, Amount | Yes |
| Consolidated | Category, one column per source, Total | Headers only |
How to fill it in
- Paste each source's rows on Data and fill the Source column for its block. Use the same category names in every source.
- On Consolidated, type your source names across row 1 (B1, C1, D1…) and your categories down column A.
- Copy the SUMIFS formula across and down the grid. Totals and the check row are already in place for three sources and four categories; insert rows or columns inside the ranges to extend them.
How the formulas work
One formula fills the whole grid, because the source comes from the header row and the category from column A:
=SUMIFS(Data!$D$2:$D$1000,Data!$A$2:$A$1000,B$1,Data!$B$2:$B$1000,$A2)The check row compares the grid total with the raw data total:
=SUM(Data!D2:D1000)-E6If the check is not 0, some Data rows use a source or category that is not in the grid, usually a typo. It is the same safeguard described in totals double after consolidating.
Worked example
| Category | North | South | West | Total |
|---|---|---|---|---|
| Product A | 12000 | 9800 | 6400 | 28200 |
| Product B | 8500 | 7100 | 9900 | 25500 |
| Services | 4200 | 5600 | 3100 | 12900 |
| Other | 600 | 400 | 900 | 1900 |
| Total | 25300 | 22900 | 20300 | 68500 |
| Check vs Data | 0 |
Limits and when to move on
- Pasting sources in by hand is fine for a handful. For a folder of files, Power Query's From Folder or the free consolidate tool stacks them for you.
- Add a Period condition to the SUMIFS if you need the grid by month.
- When the question is not a fixed grid (for example which category grew fastest in each source), Analistable can load the source files and answer in plain English. Compare the classic consolidation worksheet if you prefer one tab per source.
Frequently asked questions
- How is this different from Excel's Consolidate command?
- Consolidate produces static numbers (or links) that do not pick up new categories. This template is plain formulas over one table, so it updates when data changes and shows a check.
- Can sources have different categories?
- Yes. A source with no rows for a category shows 0. Every category used anywhere must be listed in column A, or the check will catch it.
- Does it work in Google Sheets?
- Yes. Upload the file to Drive and open it in Google Sheets; the formulas need no changes.