4 years ago, struggling to add the numbers in 4 cells, I belonged to the group of people who would rather be imprisoned than have to go cross-eyed, looking at the columns and rows in a spreadsheet. I would throw my hands up and tell myself that Excel just wasn’t for me!
That’s when CA-Articleship happened to me! Where we meet people singing the praises of Excel all day. We have no choice, but to agree that excel is an incredibly powerful tool to find exactly what we need and get things done in a jiffy — only if we know how to use it, correctly.
All of us have solved various practical problems using Excel. Many a times, after doing something that took us lesser time than it took others, we have felt as if we have ascended the Mount Everest. We have done so much in Excel, that, at the completion of articleship, while filling the “details of training received” in Form 108, we search for “Excel” in the “Particulars” column – only to be disappointed, because ironically, Excel doesn’t find a place in the list.
One thing almost every Excel user has in common is – Not knowing enough.
Starting this week, we will be going through some of the tips and tricks for effectively using Excel.
Put down from a CA’s point of view, I believe, that this series of Excel with Excel‘ will make your life simpler.
Chapter 1: CONSOLIDATE!
Excel’s ‘Consolidate’ feature helps in consolidating data in worksheets (located in one workbook or multiple workbooks) into one worksheet. Even though this is predominantly used at the managerial level (say, for aggregating various financial figures from multiple branches), it can come handy to the Chartered Accountants as well.
“Examples are better than precept” and so, let us take an example to understand how it works.
Using Consolidate Feature for Opening balance verification:
Determining whether the prior period’s closing balances have been correctly brought forward to the current period is called ‘Opening balance verification’ in the common parlance. Some of us have manually verified the balances and some of us have used the ‘vlookup’ function. Today, we will try it with the Consolidate feature.
♦ Input data: 2 sets of Trial Balances (‘TB’) –
1. Closing TB as at 31.03.2019 (Generally, as per the audited financial statements) and
2. Opening TB as at 01.04.2019 (As per the books of account)
–
♦ Points to be noted:
1. Make sure you have removed any blank cell in the left-most column because excel will ignore a row if the left most cell in the selected reference is blank.
2. The columns which is to be compared shall have different headings. Here, they are “31.03.2019” and “01.04.2019”
Procedure:
1. Open the workbook containing the data under comparison. Also, open a new worksheet.
2. On the Data tab, in the Data Tools group, click Consolidate
3. Choose the Sum function.
4. Click in the Reference box, select the range containing the data (one data set at a time), i.e. $B$5:$C$14 and click “Add”
5. Repeat step 4 for other data set(s).
In our case, $B$21:$C$31
6. Check “Top row”, “Left column” and “Create links to source data”
Note: If you don’t check “Top row” and “Left column”, Excel will sum all cells that have the same position in the references list. For example, cell B6 and cell B22 will be added. Because our worksheets are not identical, we want Excel to sum cells that have the same labels. If you check “Create links to source data”, Excel creates a link to your source data (your consolidated data will be updated if your source data changes) and creates an outline (grouped data).
7. Click OK.
♦ Result:
We can now compare the figures as at 31.03.2019 with 01.04.2019, pertaining to various accounts in column B. The differences can be easily identified by subtracting Column C figures from Column D figures. This way, we can prepare a list of accounts having discrepancies in balances, that require further examination — Like this:
The above excel feature can be best used for reconciliations, including Bank Reconciliation.
Until next week, SEEYA!
Author Bio
