Data Consolidation in Microsoft Excel (Advance Excel)



DATA CONSOLIDATION IN MICROSOFT EXCEL

We all are finance professionals or students of respective professional institute. In which ever field we are workings, no one can afford to be untouchable with Microsoft Excel. In most of cases, more than half of our office life is consumed around this software.  Some may say it’s because of office workload. Is it true? I don’t think so.

From witnessing people holding different positions in organizations and working on Microsoft Excel, I can say that many of us have only basic knowledge of this software. A task in excel which generally consume more than half an hour can be completed within few seconds, if we have detailed and advance knowledge of Excel functionality. And after guiding those people I realized that most of us are unaware about Importance of knowledge of Advance Excel.

Therefore, I decided to write articles on Microsoft Excel Functionalities in best suitable manner to share my knowledge to those who needed.

In this article, I will explain how to consolidate data in excel. (Download the basic file for Practice Data Consolidation in Microsoft Excel)

Let see how we can reduce our workload in this area.

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.

Having concern to this type of information one may choose to go with traditional method of consolidation by liking all sheets. But is not right way to doing comsolidation, in this way chances of errors and ommissions is high as compared to the standard practice. And if error(s) are idenfied by boss then ….

Now let see what is the Standard Practice by which possibilities of error and omission can be minimize.

Step: 1 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 On cell C4 put the following formula =Sum(Eastern:Northern!C4). This formula is basically worked upon to sum the all figures on C4 of sheets laying between Eastern and Northern sheets including these two.

Step: 3 Copy and paste this formula on the entire range.

Instantly all sheet within range get consolidated automatically.

Step: 4 After pasting this formula the formatting may be changed, in that case copy the content of eastern region (one can choose other region also) by selecting range from C4:D17

And paste on cell C4 of consolidated sheet as formatting by pressing short combination of ALT+E+S+T keys and hit enter.

Now you will see that consolidated sheet have same kind of formatting and this process take just 4 easily steps or one say less than a minute is required to console the data on different sheets.

Now after this one can ask that what happened if another sheet (Foreign Plant) is also required to consolidate with this consolidated sheet, should I go through all 4 steps once again.

Answer is No “You need to put that sheet in between the range of Eastern Northern sheets” the consolidated will automatically get update.

For example we need to consolidate the cost sheet of our foreign plant located in X territory with exiting consolidated sheet.

Put this file with in anywhere between Eastern: Northern sheets range

Now you will see that consolidated sheet will automatically get updated.

Note: 1 To console n numbers of sheets you need to just mention in =sum(beginning sheet name : end sheet name ! Required cell Number

Note: 2 It compulsory for all sheets required to be consolidated must have same kind of data presentation i.e. all similar information must have same cell number for all sheets.

Finally like to say special thanks to CA Nilesh Jain who had once requested for an article on Data Consolidation.

I like to read to your comments about your like, dislike or any query relating to Microsoft Excel.

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


41416 Views 8 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
30 October 2026
Asst Manager Accounts

Goodwin Pumps India Pvt Ltd

Chengalpattu

CA Inter

View Details
Company
ARTICLESHIP 29 October 2026
Article Assistant (CA Inter / CMA Inter) - Thrissur

Varma Raja & Associates

Thrissur

CA Inter

View Details
Company
ARTICLESHIP 17 November 2026
Vacancy for Articleship

Promptitude Accounting Services Private limited

Bengaluru

B.Com

View Details
Company
ARTICLESHIP 28 October 2026
Article assistants

Shyam S Gupta and Company

New Delhi

CA Inter

View Details
Company
13 November 2026
Accountant

New Mobile India

Muzaffarpur

B.Com

View Details
Company
16 November 2026
Assistant Article

ZAK & Co. Chartered Accountants

Bengaluru

CA Inter

View Details
Company
14 November 2026
Manager - Indirect Tax (GST)

Westbridge Consulting LLP

New Delhi

CA

View Details
Company
12 November 2026
Tax Product Specialist

BharatX.ai

Gurgaon

B.Com

View Details