Consolidate Tool In Excel

In this article, we’ll learn how to use different functions to consolidate lists.

We use Consolidate to summarize and consolidate data from separate worksheets into a master worksheet. The worksheets can be in the same workbook or in different workbooks. When we consolidate data, it means we are assembling data to update it easily.

Consolidate option has 11 different functions that we can use while consolidating the worksheets or workbook.

Let’s take an example and understand:

Excel Worksheet Merge Using Consolidate & Power Query. Details: Follow the below steps to consolidate worksheets. Step 1: Create a new worksheet and name it as a “Consolidated Sheet.”. Step 2: Place your cursor in the first cell of the worksheet, go to the DATA tab, and click on the “Consolidate” option. For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the. Re: Consolidate Not Working. Hello, the numbers in column B are text, not real numbers. Therefore, there is no numeric data to consolidate. Convert the text into numbers this way: Select B4 to B122, click Data Text To Columns Next Next Finish. Now the data is numeric and you can consolidate it. Click Data Consolidate (in the Data Tools group). In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM. Select your data. Next, in the Reference box, click the Collapse button to shrink the panel and select the data in the worksheet.

We have agent-wise number of sales data. On the other hand, we have revenue generation data month-wise. We want to return Total no. of sales, Total revenue, average sales, and average revenue for every agent by using the Consolidate option.

January:

February:

March:

How To Use Consolidate Excel

Consolidated file:

To calculate the total values, we will use SUM function in Consolidate option, follow below given steps:-

We need to enter total value in consolidated sheet
Select the cell B6 of consolidated sheet
Go to Data tab > Data tools group > click on Consolidate

Consolidate dialog box will appear
Select SUM function from the function’s drop down list

Click on Reference, and go to January sheet and select the range B6:B15
Click on Add button. Reference will appear in All reference

Add the reference for the month of February and March by following the same process

Click on OK
Total number of sales will be evaluated

Now, we’ll calculate the total revenue. Follow the steps given below to get the consolidated data value.

We will use Average function to consolidate lists. Follow the steps given below: