Data Consolidation in Microsoft Excel Part-2 (Advance Excel)

Before you like to proceed with this article I like to request to you please go through with my previous article on this Microsoft Excel Function, if you still have not read click here "Data Consolidation in Microsoft Excel"

In previous article we have learned how to consolidate data in Microsoft Excel in just 4 easily steps. But, frankly that article was not based on Advance Excel, it was created after applying basic understanding of Microsoft Excel.

In advance excel data consolidation process doesn’t take more than 3 easily steps, in fact as per my understanding these steps are called Advance Excel.

Let see again how we can reduce our workload by taking previous example. ("Download Pratical Example File") 

Suppose we have four plants which are situated in Eastern, Western, Northern and southern regions respectively. And our goal to consolidate the cost statement of these plants in single sheet to know the cost statement of the as a whole.

Step: 1 (Same as Earlier) Copy the entire sheet (any one from four) and paste over on new sheet then name it as consolidation and last remove all figures. The purpose is to maintain the same kind of formatting for consolidated sheet also.

Step: 2 Press Ctr+F3 (Name Manager will appear) and then press Alt+N put name as Consolidation in Name Box and fill the formula =Sum(Eastern:Northern!C4) in “Refer to” Box. Then Ok and closed (Why we are using this formula I have already discuss in my previous article)

Step: 3 Select range from C4:G17 and then press F3 (Paste name will appear) by selecting “Consolidation” finally hit enter. After Hitting Enter again hit enter by holding Ctr key.

Instantly your sheet will get updated.

Now let's check is it Advance Excel Function? Go on "Formula Tab" and then "Show Formulas"

You will see only =consolidation in all over no formula will appear anywhere, in fact all formula is saved in memory of Microsoft Excel and worked from Memory.

Normally if someone doesn’t have any knowledge of Advance Excel, he will not able to understand what formula actually worked for =Consolidation function..

Now again press show formula tab for normalization.

Finally I like to read your comments on this article about your like or dislike and always feel free to ask any query relating to Microsoft Excel.

The author can also be reached at Camukeshkumar@consultant.com