Data consolidating in Excel with examples of usage

Performing a range of works, the user of Microsoft Excel may create to multiple similar tables in one file or in several books.

The data must be brought together in one report for getting the General idea. Tool Consolidation copes with this task.



How to do data consolidation in Excel

There are 4 sheets with the same structure - quarterly results of sales of furniture, for example.

You need to make the General report by using the «data consolidation». First check out to:

1. the layouts of all the tables will be the same;
2. the names of columns – are identical (it is allowed to rearrange of columns);
3. there are no empty rows and columns.

The ranges with the source value need to open.

For consolidated data you need to ascribe and open a new worksheet or a new workbook. Put the cursor in the first cell of the merged range.

Attention!!! To the right and below of this cell should be free. The team « Consolidation» will fill as many rows and columns as you need.

After that, go to the tab «DATA». In the group «Data Tools», click the button «Consolidate».

There dialog box «Consolidate» is opened:

The team «Consolidation» when working with data may be performed these types of calculations.

Then to choose «Amount» (the values in the source ranges will be added).

Proceed to filling of the next field «Link».

Put the cursor in the field and open the worksheet «1-st quarter», allocated the table together with the cap. In the «Link» field will be the first range of consolidating and click «Add».

Alternately open the second, the third and the fourth quarter – allocated ranges. Click «Add».

The tables for consolidation are shown in the field «The list of ranges».

To automatically make the header for the columns of the consolidated table, put a check next to « signature top of the line» - for summarizing of the team all of the values for each unique entry of the leftmost column opposite «the values of the left column». For automatic updating of the joint report when entering new data into the original tables – in front «create links to source data».

Attention!!! If you make in the original tables to the new values over the selected for consolidation range, they will not display in the merged report. To be able to enter value by manually, uncheck the «Create links to source data». For exiting from the «Consolidation» menu and creating the PivotTable, click OK.

The consolidated statement represents a structured table. Press the «+» in the left field – there will be values, on the basis of which are formed the totals by quantity and revenue.



The consolidating of data in Excel: practical work

The Microsoft Excel program allows you to perform different types of data consolidation:

1. On the location. Consolidated data have the same location and order from the source.
2. For the category. Value is organized according to different principles, but in the consolidated table uses the same titles of rows and columns.
3. According to the formula. Apply them in the absence of permanent categories. They are refer to the cells in other worksheets.
4. In the PivotTable report. Use the tool «The pivot table» instead of «The consolidation» one.

Consolidating data by location (position) implies that the source of the table is identical. The same not only the names of columns, but also the names of the strings (see the example above). If in the range of 1 «Ottoman» it is placed the sixth row, in the range of 2, 3 and 4 this value must also takes the sixth row.

This is the most correct way to combine the value, because the source ranges are ideal for consolidation. Join the tables that are in different workbooks.

The books are created: the Store 1, the Store 2 and the Store 3. The structure is the same and the location of the value is identical also. Combine them by positions.

1. Open all three books, plus a blank book, which will be placed a consolidated table. In the blank workbook, choose the upper-left corner of the blank slate. Open the tool menu «Consolidation».
2. Prepare the consolidated report, using the function «Average».
3. To show the way to books with the original ranges, put the cursor in the field «Link». On the tab «VIEW», click the «Switch to another window».
4. Select alternately the names of the files, allocated to ranges in open workbooks – click on «Add».

Links to files of other Workbooks Excel on the disk D:

• 'D:\[Store1.xlsx]Sheet1'!$A$1:$M$13
• 'D:\[Store2.xlsx]Sheet1'!$A$1:$M$13
• 'D:\[Store3.xlsx]Sheet1'!$A$1:$M$13
• 'D:\[Store4.xlsx]Sheet1'!$A$1:$M$13

The comment: show for the program the path to the source range you may with helping the «Browse» button, or by switching to the opening book.

The consolidated table:

The consolidating data by category is used when the source ranges have different structure. For example, in shops are implemented to different products. Some designations are repeated, and some ones are not.

1. To create of the merged range open «Consolidation» menu and select the function «Sum» (for example).
2. Add source ranges any of the method, which was above-described. We put the check boxes for «the values of the left column» and «the signatures top of the line».
3. Press OK.

Excel united to the information on three shops into categories. The report includes value on all goods, regardless of whether they are sold in one store or in all three.

The examples of data consolidation in Excel

On the sheet for the summary report, enter the names of the row and columns from the consolidation ranges. It will be easier to do by copying.

In the first cell for the values of the united table, enter the formula with references with the sources cells on each worksheet. In our example we`ll put it in the cell B2. The formula for sum is:

Copy the formula to the whole column:

The consolidating data with using formulas is convenient, when the merged value are in different cells on different sheets. For example, in the cell B5 in the worksheet «Store» in the cell E8 on the sheet «Store», etc.

Download all examples data consolidating in Excel

If in the book is included to automatic calculation of formulas, when you change the value in the source ranges the merged table will be updated automatically.