This site uses cookies. By continuing, your consent is assumed. Learn more

132.4fm shares

Consolidating data from different workbooks for students

opinion

Data consolidation is useful in cases where you want to summarize data from more than one source. This source data could be within the same Excel workbook on different worksheets. This is super easy to do in Excel! Throughout this guide, you need a data set to practice. In fact, you need 4 different data sets — and a main file.

Consolidate data in Excel in the same workbook. Each of four regionsNorth, South, East, and West have sales figures for all the products.

What is Excel consolidate?

Now we can add a new worksheet tab and select the cell in which we would like our new consolidated table to start. Paste in the template for the table to match the column headers and row labels of the regional tables. There are several functions to choose from providing a lot of flexibility. Once there, we will go to our worksheet that contains the data for our North division. Then our selection will appear in that field. One important thing to note at this point.

It automatically selects the same range so long as each subsequent worksheet has the same layout "Consolidating data from different workbooks for students." At this point, it should look like the following figure:. In this, we got a single table that shows the sum for all tables being consolidated.

In this tutorial, you learn...

The drawback to this static table is that when data in the regional tables changes, this table will not be affected. If you want a consolidated table that updates if any changes are made to the reference table ranges, we must make one change to our approach….

Creating links to source data.

This inserts the column headers and row labels for us. Now you need to select Consolidating data from different workbooks for students range that includes the column headers and row labels. Consolidate data in Excel from different workbooks. Excel also gives you the opportunity to combine Excel files or consolidate data from other workbooks. This new worksheet is completely blank and without formatting.

We then select the first file that we want to merge. That said, there is a more efficient way to select our ranges. Now is the time to suggest a bit of extra skill to make our quest to merge multiple Excel files a bit simpler. Instead of selecting the range of cells we need from every workbook we are drawing our source data — we can do this one time in each file and name the range.

If you want to go even more advanced, read my guide to dynamic named ranges on Lifehacker. Furthermore, since each is a separate file, we can use the same name in each to make things even simpler. When you select it, the range is highlighted on our worksheet. Now that we have named Consolidating data from different workbooks for students for our source files, we can combine Excel files much more efficiently. The previous examples have shown you how to consolidate data in Excel with a very straight forward method.

Consolidation is quick and easy to use when summarizing data is the need at hand. With a little practice, this method of data consolidation becomes second-nature to you very soon! I've just released new, free, Excel training that I think fits you perfectly.

News feed