How to use CONSOLIDATE to statistic, combine data in Excel

CONSOLIDATE is one of Excel's useful data statistics tools. CONSOLIDATE allows you to quickly perform sum, average, maximum value, minimum value . Software Tips invite you to refer to the following article information to better understand the syntax and usage CONSOLIDATE tool!

How to use CONSOLIDATE to statistic, combine data in Excel Picture 1

In the spreadsheet below, a detailed list of data by location and time, your task is to synthesize into summary reports according to each report criteria.

How to use CONSOLIDATE to statistic, combine data in Excel Picture 2

Step 1: Put your cursor in a cell where you want to return the report. For example, cell J2 . Select the Data tab (1) => Click on the icon of CONSOLIDATE (2) .

How to use CONSOLIDATE to statistic, combine data in Excel Picture 3

Step 2: The CONSOLIDATE window appears.

Inside:

1. Function: Select the function to perform calculations like Sum (sum), Count (count data), Average (average), Max, Min .

How to use CONSOLIDATE to statistic, combine data in Excel Picture 4

1. Reference : The position of the data area to be referenced. To select a range of calculations, click the Reference box and then drag to select the range.

How to use CONSOLIDATE to statistic, combine data in Excel Picture 5

1. After scanning the Reference area, click the Add button to include the reference address in the All Reference .

How to use CONSOLIDATE to statistic, combine data in Excel Picture 6

1. Top row: Headline. If the first line is the title you choose this check box.
2. Left column: In the column header, if the first column of the data range is the header you select this check box.
   In this step, Software Tips will select the Sum function, the reference region from B2: G12, and all choose the headline and column header products.

How to use CONSOLIDATE to statistic, combine data in Excel Picture 7

Step 3: Click the OK button to view the summary report. The resulting report will return starting in cell J2 as you selected in Step 1 .

How to use CONSOLIDATE to statistic, combine data in Excel Picture 8

The CONSOLIDATE result adds up the column cells in the data table according to the criteria of the first column you select as the header column. In the Quarter and Products columns cannot add up, so the result is equal to 0. The Unit Price column has no meaning so you can delete it.

Above Software Tips guide you to use the CONSOLIDATE tool to collect statistics in Excel. Good luck!