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.

image 1


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.


image 2



image 3



image 4


Consolidated file:

image 5


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

image 6


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

image 7


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

image 8


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

image 9


image 10


  • Click on OK
  • Total number of sales will be evaluated

image 11


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

image 12


image 13


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

image 14


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

image 15


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

image 16


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

image 17


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

image 18


image 19


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


image 48


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


Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.