Microsoft Excel logo on a green background

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.

RELATED: How to Find the Function You Need in Microsoft Excel

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.

Select a cell for the combined data

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.”

Choose a Function

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.

Select the sheet and cells

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

Add the Reference

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.

Add more References

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.

All References added

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.

Add labels or links

You should then see the data consolidated into your sheet.

Combined data in Excel

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.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »