All of us have ever had to combine data from spreadsheets in Microsoft Excel , be it equipment expenses or sales count, these must be consolidated into one. Microsoft's office suite has a simple option, but it is ideal for these kinds of situations..
Excel is a considerably useful program in different situations, not just work. It allows us to name a table so 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 it offers us is the possibility of combining the data from several spreadsheets into one. Do you want to know how to achieve it? We will teach you a little more below..
Combine data from spreadsheets 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 worksheet.
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 window "Consolidate" will open, we will start by choosing the function we want to use from the drop-down list. In our example of adding expenses, we are going to choose “Sum”.
Now we will have to use the Reference box to get the first sheet and the range of cells. Here we can go to that sheet and select the cells. The Consolidate window will remain open while we do it and they will self-select. 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 the 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 later 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 in order 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 is too easy and fast.