Consolidating Lists in Excel 2007

In this article, we’ll learn about Consolidate option in Microsoft Excel.

It is an efficient option to combine the data and summarize data from multiple files, workbooks or worksheets. The common methods for consolidating in Excel include consolidating by method, by formula, by the feature of Pivot table etc.

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

image 1

 

There are few important points which we need to take care of before consolidating lists:

1. Each data should be labeled with similar information

2. Add and layout each column-range to a separate worksheet. However, do not add the ranges to the master worksheet that you plan to consolidate

3. Highlight each range, and name them by picking the Formulas tab, followed by the arrow located near ‘Name a Range’. Enter a name for the range in the Name box.

 

Let’s take an example and understand:

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

January:

image 2

 

February:

image 3

 

March:

image 4

 

Consolidated file:

image 5

 

  • Select the cell, where we want to consolidate the values of all data
  • Data tab > Data tools > Consolidate
  • Consolidate dialog box will appear

image 6

 

  • In Consolidate option, we have function list

image 7

 

  • To return total value, select Sum from the list
  • Then click on reference, and select the range of number of sales in January sheet
  • Click on Add

image 8

 

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

image 9

 

  • To add the reference of February and March, follow same steps and then Click on OK

image 10

 

image 11

 

In this way, we can consolidate the list in Microsoft Excel. Apart from this, we have another option to consolidate the list.

We can do this by using a very simple technique.  And, if we have a range of cells that we need to sum, we can sum one cell and then drag the formula down or right or copy it to those ranges that need to be summed.

In the above example, we need to calculate total revenue. Follow the steps given below:

  • In Consolidated sheet, enter SUM function

image 12

 

  • =SUM and then click on the January sheet, hold the shift key on the keyboard and select the 3rd sheet – March

image 13

 

  • Select the cell C6 and press Enter on your keyboard
  • The formula will now show =SUM(Jan: Mar!C6)

image 14

 

You will get the sum of the cell C6 from January to March. Now, drag this formula to C15 and the values will be settled.

 

image 15

 

Note: In this example, we had values only in C6: C15.  However, if you have values horizontally, then you can drag the formula towards the right. Or copy the formula to other cells where you need to sum up the values from the corresponding sheets.

 

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 info@exceltip.com

 
 

Users are saying about us...

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube