How to Consolidate Data from Multiple Sheets in Excel

Method 1: 3D References (Identical Layouts)

If all sheets have the exact same structure (same data in the same cells), 3D references are the simplest solution.

=SUM(Jan:Dec!B5)

This sums cell B5 from all sheets named Jan through Dec. Updates automatically as source data changes.

Best for: Monthly/quarterly sheets with identical formats. Departmental sheets following a template.

Limitation: Sheets must be consecutively ordered. Inserting a sheet in the middle changes the range.

Method 2: The Consolidate Feature

Data tab → Consolidate. This tool combines ranges from multiple sheets.

1. Click where you want the consolidated data
2. Data tab → Consolidate
3. Choose a function (Sum, Average, Count, etc.)
4. Add each source range (can be from different sheets)
5. Check “Top row” and/or “Left column” if headers should match
6. Click OK

Best for: One-time consolidation. Sheets with different layouts but matching labels.

Limitation: Result is static — doesn't update when source data changes (unless you check “Create links to source data”).

Method 3: Power Query (Best for Automation)

Power Query can combine sheets dynamically, handling different layouts and automatically including new sheets.

1. Data tab → Get Data → From Other Sources → Blank Query
2. In the formula bar, enter: =Excel.CurrentWorkbook()
3. Expand the Content column
4. Filter to include only the sheets you want
5. Remove columns you don't need
6. Close & Load

Best for: Ongoing consolidation. Large datasets. Variable sheet structures.

Limitation: Requires Power Query knowledge. May seem complex for simple tasks.

Method 4: Manual Formula Building

For complex scenarios, you can build formulas that reference each sheet explicitly:

=Jan!B5 + Feb!B5 + Mar!B5

Or use SUMPRODUCT/INDIRECT combinations for more flexibility. This gives complete control but requires maintenance when sheets are added.

Choosing the Right Method

• Identical layouts, ongoing updates: 3D references
• Different layouts, matching labels, one-time: Consolidate feature
• Complex ongoing consolidation: Power Query
• Specific custom logic: Manual formulas

Official Resources

• Consolidate data — Microsoft consolidation guide
• 3D references — multi-sheet references