Excel Consolidation & Data Merging

When you work with multiple spreadsheets or datasets, manually copying and pasting data is time-consuming and error-prone. Excel's consolidation features help you merge data from multiple sources automatically.

What is Data Consolidation?

Consolidation combines data from multiple ranges or sheets into one summary.

Common scenarios:

• Merging sales data from different regions
• Combining monthly reports into yearly summaries
• Aggregating data from multiple teams

Method 1: Consolidate Tool

Excel has a built-in Consolidate tool perfect for summarizing data.

Steps:

1. Click on your summary sheet
2. Go to Data tab → Consolidate
3. Choose function (SUM, AVERAGE, COUNT)
4. Click Reference → Select first range → Add
5. Repeat for each range
6. Check "Top row" and "Left column" if you have headers
7. Click OK

Method 2: Power Query (Modern Approach)

Power Query is the most powerful consolidation tool.

Steps:

1. Select data → Data → From Table/Range
2. In Power Query: Home → Append Queries
3. Select "Three or more tables"
4. Add all your tables → OK
5. Click Close & Load

Advantages:

• Auto-refreshes when source changes
• Handles different column orders
• Can merge hundreds of files
• Remembers steps for reuse

Method 3: VLOOKUP for Matching Data

When merging data from different sources:

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Or use modern XLOOKUP:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)

Summary

• Use Consolidate Tool for quick one-time summaries
• Use Power Query for recurring consolidations
• Use VLOOKUP/XLOOKUP for matching and merging data

Master these and handle any data consolidation task! 💪