Mastering Data Merge Techniques: Integrating Multiple Spreadsheet Sources in Excel

Quick Links

• Consolidate Spreadsheet Data in Excel

Have you ever been tasked with combining spreadsheet data? Maybe you have your team’s expense sheets or sales counts that need to be consolidated into one. Here, we’ll show you how to combine data from spreadsheets in Microsoft Excel.

Consolidate Spreadsheet Data in Excel

Microsoft Excel offers a Consolidate feature that can tackle this task. It allows you to combine data from different sheets into one spreadsheet. Plus, you can choose the function you want to use from options like SUM , AVERAGE , PRODUCT, and COUNT .

To demonstrate the process, we’ll combine expense sheets from three employees into one final sheet that totals all expenses. As you’ll see, each sheet is set up the same way which makes the consolidation much simpler.

Go to the sheet where you want to pull in the data from the other sheets. Select a cell to insert the combined data. If you have a cell range, you can choose the upper-left cell.

Head to the Data tab and the Data Tools section of the ribbon. Click “Consolidate.”

When the Consolidate window opens, start by selecting the Function you want to use from the drop-down list. For our example of adding expenses, we choose “Sum.”

Next, use the Reference box to obtain the first sheet and cell range. You can simply move to that sheet and select the cells . The Consolidate window remains open as you do this. For our example, we go to Joe’s sheet and select cells A1 through B5.

When the cell reference appears, click “Add.” This places it into the All References list.

Click inside the Reference box and then go to the next sheet you want to add and select the cells from it. Then, click “Add” to place it into the list.

Continue this same process for all sheets and cell data. For our example, we gathered data from the sheets labeled Joe, Jane, and Jim into our Final spreadsheet.

Before you finish, you can check the boxes at the bottom for using the labels and creating links to the source data if you like. Click “OK.” To make our job easier, we’ll use labels for the top row and left column.

You should then see the data consolidated into your sheet.

Need to combine other types of things in Excel? Take a look at how to combine text from multiple cells into one or combine or group pie charts .

Related: How to Combine First and Last Names in Microsoft Excel

Also read: