Using Different Functions to Consolidate Lists

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:

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:

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:

Select cell D6
Go to Data tab > Data tools group > Consolidate
Consolidate dialog box will appear
Select Average function from the function list

Select the range of total number of sales for the month of January. Click on Add button

When you will click on Add button, selected reference will appear in All reference

Click on OK. We can see monthly average calculation for every agent

Now, we want to return average for revenue as well, you can follow the steps above.

In this way, we can use different functions to consolidate lists in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com