How to use CONSOLIDATE to summarize and merge data in Excel

CONSOLIDATE is one of the most effective tools for summarizing and merging data in Excel. It helps to calculate more efficiently by allowing for quick calculations such as sum, average, max, min, etc. within a worksheet. In this article, we will show you how to use CONSOLIDATE to summarize and merge data in Excel.

How to use CONSOLIDATE

For example, suppose we have a data table as shown below. To summarize the data using the Consolidate tool, first place the mouse pointer where you want to create the new summary report. Next, select the Data tab on the ribbon, and then click Consolidate under Data Tools.

A Consolidate dialog box appears. Under the Function section, select the calculation function you want to perform such as Sum, Count, Average, Max, Min, etc.

Next, under the Reference section, enter the location of the data range by clicking the Reference box and selecting the data range with the mouse. In this case, we will enter a reference to the data table. After scanning the Reference range, click the Add button to add the reference address to All References.

Next, select the row or column headings:

• Top row: If the first row is a header, select this checkbox.
• Left column: If the first column on the left side of the data range is a header, select this checkbox.

After setting up the Consolidate table information, click OK for Excel to create the summary report. The result will be a summary table using the SUM function as selected quickly as shown in the image below.

Thus, this article has provided you with the steps to use CONSOLIDATE to summarize and merge data in Excel. We hope this article will be helpful to you in your work. Good luck!