It’s no secret that I love Excel. I’ve said it before, I’ll say it now and I’m sure I’ll hum this tune again next week.
Today I’m tackling a common problem that’s plagued office heroes for years. How do you merge multiple Excel worksheets into one master worksheet, automatically, and without macros or VBA? Down the line I’ll discuss other methods to merge and consolidate data in Excel, but today I’m focusing on this particular situation: .
Lucky for you, Excel has a neat little Consolidate feature that’s powerful, flexible and very easy to use. Best of all, it requires no formulas, macros or VBA code. Here are some typical cases where this feature comes in handy:
- Roll up expenses from multiple offices, branches, employees, children, etc.
- Roll up sales from multiple customers, products, months, quarters, years, and so on.
- Merge worksheets that were previously distributed among colleagues or other groups of people for updating back into one master worksheet.
The file I’m working with, which you can download here or at the end of this post, is for a fake used car dealership that sells the Honda Accord, Honda Civic, Toyota Corolla, Toyota Matrix and Toyota Camry. There are 30 employees and three separate worksheets (one for January, February and March), each containing the total cars sold that month per employee, per car.
Let’s get started.
How to do it
- Ensure that the worksheets you intend to merge all have the same columns and rows in the same order.
- For example, notice that the January, February, and March worksheets all have the same columns and rows in the same layout.
- The only difference between the worksheets to merge is the data contained within these columns and rows.
- Create the Merged Master worksheet. This worksheet should look identical to the other worksheets (same columns, rows and layout) except the space to contain the merged data should be blank.
- Right click on the January worksheet and select Move or Copy…
- Double-click on the newly created January (2) worksheet, type “Merged Master” and press Enter.
- Select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35). Press Delete.
- Select cell B5 again. Navigate to Data → Consolidate.
- In the Consolidate dialog box that appears, under Function, choose the summary function that you want Excel to use to consolidate your data.
- We’ll leave the default,
Sum, since we’re looking to summarize the sales from January, February and March, but I suggest trying out different functions (
Min, etc.) to see what they do.
- We’ll leave the default,
- Under Reference click the small button, then select the January worksheet, select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35) and press Enter.
- You’ll see the range you just selected now appears in the Reference box. Click Add to include this range in the All References box.
- Click the small button again. This time when you click the February worksheet you’ll notice the same range appears in the dialog box, except it’s been updated to February instead of January. Press Enter, then click Add.
- Repeat step six for March. Your Consolidate dialog box should now look like this:
- When you click OK Excel will complete the merge, and the Merged Master worksheet will now contain the consolidated first quarter sales for each employee. Viola
A few notes
- If you select the Create links to source data option, Excel will automatically update the newly consolidated data on the Merged Master worksheet whenever you update any of the source values from the other worksheets.
- If the data you want to consolidate is contained in separate workbooks, you can simply select the Browse… button to find that workbook, then click OK to close the Browse dialog box. Excel automatically includes the file path in the Reference box followed by an exclamation point.
- It’s important to remember that the space to contain the merged data should be blank, since Excel will automatically overwrite anything it finds in that space when it completes the merge.
The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!