Excel: How to consolidate data


Excel consolitadate: YOUR OBJECTIVE

In an Excel workbook, you created three worksheets that contain your company’s first-quarter sales figures for as many business locations. To maintain a clear overview, you recorded the sales numbers for each business location in a separate table, on a separate worksheet. That is, there is a worksheet for Berlin, another for Frankfurt and a third for Munich. The tables are in different positions on each worksheet; some contain different ice cream flavors.

Now, on a fourth worksheet, you want to develop a summary that displays consolidated data for the first calendar quarter.

Because the data on the three tables is in different positions and the contents of the three tables are not entirely identical, you will have to use Excel’s Consolidate function to summarize them. 

 

In this learning object, you will be shown how to use the excel Consolidate function to consolidate data from multiple worksheets, even when data positions and contents vary.

 

HOW DOES  EXCEL CONSOLIDATE FUNCTION WORK?

Begin by opening the worksheet (in this example: Quarter 1) that is to contain the consolidation table. Now select the cell that is to receive the first consoldiated value. In this case, select cell A4 on the Quarter 1 worksheet.

 

The excel Consolidate button is located on the Data tab, in the Data Tools group.

 

Click the excel Consolidate button. The excel Consolidate dialog box with its several input fields will open:

 

Here you can select a consolidation function from a list of mathematical and statistical functions. By default, the Sum function is preselected.

This box is where you will be identifying cell ranges that are to be consolidated.

In this box, the ranges identified in the reference box above will be collected. The buttons Add and Delete are used to add or delete such ranges.

This area provides options that control row and column headers. It also allows you to specify that links to the source data should be created in the consolidation table.

In our current example, you will leave the Sum function unchanged in the Function: box. Now, to identify the first cell range to be consolidated, click in the Reference: input box. Next, click the Berlin worksheet’s tab. In that worksheet, select the entire table (B4:E9). As a result, the corresponding cell range – identified as: Berlin!$B$4:$E$9 – will be displayed in the Reference: input box. Now click the Add button. The cell range you just identified will be copied into the All References: area (as shown below): 

 

In a similar fashion, you can select the second consolidation cell range from the Frankfurt worksheet. Before selecting the cell range and using the Add button to copy it into the All References: box, make sure your cursor is again positioned in the Reference: box.

Finally, follow the same procedure for the Munich worksheet. When done, all three cell-range references should be listed in the All References: box.(

 

Now, to insert the consolidated table into the Quarter 1 worksheet with correct column and row headers, you must activate the Top row and Left column options in the Use labels in area.

 

If you now click OK, the consolidated table will be inserted.

 

CAUTION

In order to correctly merge data from columns and rows that contain the data to be consolidated, Excel requires that identical headings be spelled identically. If one row heading were spelled Chocolate, for example, and another were spelled Chocolat, Excel would consider them to be two different items.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen