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


29699 Views 10 Likes Comment   Share Technology & Tools   Report


About the Author

CA, CMA FINAL and Advance Excel Trainer

Hi I am KumarMukesh, Chartered Accountant, line-height: 16px;">Payroll Audit Report Format  Internal Audit Report format-Production  Treasury and cash management work program  Accounts Payable Audit Programs   Payroll/Human Resources Review Work Program In Tally  Linking excel with tally In System ... Read more

Comments :

Related Articles


Loading


Popular Articles





CCI Pro

CCI Articles

submit article


Company
14 November 2026
Manager - Indirect Tax (GST)

Westbridge Consulting LLP

New Delhi

CA

View Details
Company
28 October 2026
Assistant Director/ Associate Director-Finance & Accounting

Krea University

Chennai

CA

View Details
Company
13 November 2026
Chartered Accountant - Fresher

Neeraj Bhagat & Co

New Delhi

CA

View Details
Company
Featured 27 October 2026
semi-qualified CA

Saini Pati Shah & Co LLP, Chartered Accountants

Mumbai

CA Final

View Details
Company
23 October 2026
Sr. Accountant

SANKARAN & KRISHNAN

New Delhi

B.Com

View Details
Company
10 November 2026
Direct Tax Manager

CAAQ CONSULTANCY PVT LTD

New Delhi

CA Final

View Details
Company
02 November 2026
Audit Executive

SRJP & Co LLP

Kochi

CA Inter

View Details
Company
ARTICLESHIP 16 November 2026
Audit Executives

Loonker Godawat And Company

Bengaluru

B.Com

View Details