Do you know how to use CONSOLIDATE in excel yet?

Along with the Pivot Table, the SUBTOTAL function, CONSOLIDATE is one of the very effective data statistics functions in Excel. Consolidate helps us calculate extremely effectively when integrated allowing quick performance of sum (sum), average (evergage), max, min . in the spreadsheet. To understand more and better about the use of CONSOLIDATE, you refer to the article information below

1. The function of CONSOLIDATE in excel

In fact, we often have to process data from a large excel file from sales software or data from HR software transferred for example.The work will be faster and more efficient if we combine the built-in tools of excel, Consolidate is a typical.

Consolidate can generate a summary report of all selected data fields in a few simple steps.In this article, we will sum using the Consolidate tool in excel 2010 (excel 2007, 2013, 2016 is similar)

2. Specific examples of using CONSOLIDATE in excel

Calculate the total quantity and the total amount for each corresponding code / item.

In the above worksheet, there are many duplicate and duplicate items, and our task is to sum them up for each item code, ie create an aggregate report.

Do you know how to use CONSOLIDATE in excel yet? Picture 1

First, place the mouse cursor at the position where you want to create the report (in any sheet or sheet), click on the Data menu and select Consolidate .

Do you know how to use CONSOLIDATE in excel yet? Picture 2

The Consolidate dialog box appears and our task is to fill the appropriate values ​​in the corresponding boxes:

In the Function box we select Sum to sum (there are many other functions such as average (evergage), max, min . you can learn more).

In the Reference box, type in the reference area, select the range for Consolidate to calculate by clicking on the Reference box and then dragging to select the data block.

Note the headline (Top row) is the first row of the selection block (our Top row is the second row: the name of the goods, the amount, the amount).

The column heading (Left column) is the first column of the selection block (Left column we are choosing is column C: Name of goods);If the column heading is "SKU: You must adjust the selection to column B in the Reference box.

Next, we click Add to add the reference address in the box All references to take effect.

Do you know how to use CONSOLIDATE in excel yet? Picture 3

Do not forget to select the check mark in the Top row and Left column items in the Use labels area to report the current row and column headings (if you do not check the two items, the column will be hidden but only the figures).

Finally, click OK to view the report:

Do you know how to use CONSOLIDATE in excel yet? Picture 4

Consolidate results will add up the exact amount and amount of the items, of course, the cumulative unit price column in this case for us does not make sense.

If you choose to add a checkmark in the Create links to soure data section before clicking OK, the report will have more details (similar to the Subtotal feature) when clicking the + sign on the left.