How to Use CONSOLIDATE to Statistic, Combine Data in Excel

This guide explains how to use CONSOLIDATE to Statistic, Combine Data in Excel, with clear steps, practical tips, and important checks before you begin.

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.

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) .

Step 2: The CONSOLIDATE window appears.

Inside:

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

• 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.

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

• Top row: Headline. If the first line is the title you choose this check box.
• 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.

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 .

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!