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.
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:
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 firstname.lastname@example.org
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.