# 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.

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:

February:

March:

Consolidated file:

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

• In Consolidate option, we have function list

• 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

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

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

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

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

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

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.

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.

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...

1. Consolidation is excellently discuss here.

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.