+3 votes
2 views
How to combine data from spreadsheets in Excel

ago in Guides by (474k points)
reopened ago | 2 views

1 Answer

+4 votes
Best answer

Combine data from spreadsheets in Excel

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

image

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.

image

Next, we go to the "Data" tab and in the options, we are going to click on the "Consolidate" icon.

image

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

image

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.

image

When we see the cell reference, we will have to click on "Add". This will list all the references.

image

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.

image

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.

image

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.



ago by (3m points)
edited ago

Related questions

+4 votes
1 answer
asked Nov 19, 2021 in Guides by backtothefuture (474k points) | 6 views
+3 votes
1 answer
asked Oct 4, 2019 in Office by backtothefuture (474k points) | 136 views
+5 votes
1 answer
asked Nov 3, 2019 in Office by backtothefuture (474k points) | 150 views
+4 votes
1 answer
asked Dec 31, 2021 in Guides by backtothefuture (474k points) | 6 views
+3 votes
1 answer
asked Dec 20, 2021 in Guides by backtothefuture (474k points) | 3 views
9,093 questions
9,196 answers
417 comments
2 users