We’ve all had to combine data from spreadsheets in Microsoft Excel at some point , whether it’s team expenses or sales count, these need to be consolidated into one. Microsoft’s office suite has a simple option, but ideal for this kind of situation.
Excel is a considerably useful program in different situations, not only work. It allows us to name a table in such a way that it is totally personalized; insert today’s date and keep it updated automatically; calculate the average, etc.
We can say that Excel is one of the best programs that the Microsoft office suite has and one of the most interesting things that it offers us is the possibility of combining the data from several spreadsheets into a single one. Do you want to know how to achieve it? We’ll show you a bit more below.
How to Combine Spreadsheet Data in Excel
Microsoft Excel offers us a consolidation function that allows us to cover this task. In this way we can combine data from different sheets in a single spreadsheet.
To serve as an example, we are going to combine the expense sheets of three employees into a final sheet that adds up all the data. Each sheet is configured in the same way, which will greatly simplify consolidation.
The first thing we will do is go to the sheet in which we want to extract the data from the other sheets. We choose a cell to insert the combined data. If we have a range of cells, we can choose the top left cell.
Next, we go to the “Data” tab and in the options, we are going to click on the “Consolidate” icon.
A new “Consolidate” window will open, we will start by choosing the function we want to use from the dropdown list. In our example of adding expenses, we will choose “Sum”.
Read Also: How to use the STDEV (standard deviation) function in Excel
Now we will have to use the Reference box to get the first sheet and cell range. Here we can go to that sheet and select the cells. The Consolidate window will stay open while we do this and they will auto-select themselves. In this example we go to Joe’s sheet at the bottom and select cells A1 through B5.
When we see the cell reference, we will have to click on “Add”. This will list all references.
We are going to click inside the Reference box and then we will go to the next sheet that we want to add, we will select the cells in the same way and then we will click on “Add” so that they appear in the list.
Obviously, we will continue this process for all sheets and cell data. For our example, as you can see, we collected the data from Joe, Jane, and Jim’s sheets for the final spreadsheet.
To finish, we can check the boxes at the bottom to use the labels and create links to the source data, if that is what we want. Now we will click on “OK”.
As a final result, we should see the consolidated data in the What do you price? spreadsheet. It’s too easy and fast.