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
CAclubindia
