Score! Merge data from multiple worksheets

The Decatur Golden Gators soccer team is hitting the pitch (that’s a field for you uninitiated folks) to perfect their skills and beat their better-funded rivals. The coach is excited and fiscally worried. Lining up mini-vans, buying snacks, and replacing torn jerseys is not cheap!

Rolling these expenses into one worksheet is also pushing the limits of his Excel skills. He has different worksheets for transportation, treats, website hosting — he cuts and pastes numbers from each into his overall budget. Inevitably, Cntl + C leads to errors.

The Excel Consolidate feature provides an easy way for the coach to merge his data from different worksheets into a main worksheet, allowing him a more complete view of his expenses (in fact, he can merge up to 256 worksheets!). By using the Consolidate feature,  the beleaguered coach can get a handle on team expenses and better focus on motivating the squad.

The steps for merging, or consolidating, data are pretty straightforward. Here’s how to do it.

Set up your workbook

You can use our sample workbook or if you want to learn by creating your own workbook, be sure to do the following:

• The workbook should contain multiple worksheets (or else there isn’t much point in using the Consolidate feature)
• The workbook should contain a main worksheet for consolidating the data you are analyzing
• The main worksheet must use the same names for the column and row headers

Merge data from worksheets

1. Create a main worksheet sheet in your workbook that uses the same column and row header names as the other worksheets. For this scenario, we will call the main page Total Expenses.

 
2. On the Total Expenses worksheet, select the cells where you want to consolidate the data from your other worksheets.

3. Click on the Data tab, and then click the Consolidate button in the Data Tools tab.

,

4. On the Consolidate dialog box, click the highlighted button next to the References field.

5. Click the Transportation worksheet tab to display it.

6. On the Transportation worksheet, select all the data that you want to merge into the Total Expenses worksheet. For this exercise, just select the Costs data.

 7. Click the button at the right edge of the Consolidate – Reference box to return to the Consolidate dialog box.

 8. On the Consolidate dialog box, click the Add button to add the Transportation data you just selected. This step ensures that the Transportation data you just selected will be merged into the Total Expenses worksheet.

9. Repeat Steps 4-8 outlined above to consolidate the other worksheets into the Total Expenses worksheet.

10. Once you have added all the worksheets you want to merge into the Total Expenses worksheet, click the OK button on the Consolidate dialog.

Booyah!

The hapless coach followed the above steps, and now his Total Expenses worksheet looks like this:

 Better budgeting means more Beiber!