CA Suraj Lakhotia (IndigoLearn) (4898 Points)
14 June 2013Spreadsheet has become and indispensable tool in contemporary accounting and auditing. With business moving to ERP softwares, data is available in spreadsheets. The need of the hour is to gain expertise in Spreadsheet and be able to play with data. In the following threads, I would be sharing few tips which I have learnt over the years. Hope these are helpful to all.
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 14 June 2013
Get rid of the mouse
You dont need Mortein Rat kill to do this.
Reduce the usage of Mouse if you want to gain speed. The more you use your keyboard for Excel operations, the better will be your speed. From simple selection of Cells to moving workbooks, reaching a particular cell etc, moving your hand to and from mouse reduces speed.
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 14 June 2013
How to minimise use of mouse?
Use the "Alt' key on the keyboard. Once this key is pressed letters are highlighted on the menu and tool bar. Press the key to go to the desired menu. For instance if you want to go to Insert - Press "Alt" followed by N.
Once in the insert menu, you will again see lot of letters being highlighted. Initially you may find it time consuming and slow. Once you are used to key strokes, it will save a lot of time.
(Note - You can also use 2003 key strokes in 2007 - For instance
To create a filter - you can use Alt+d+f+f (2003) or Alt+a+t (2007)
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 14 June 2013
Some useful Shortcut Keys
Ctrl + R = Copies data from the left cell to the current cell.
Ctrl + D = Copies data from cell above to the current cell.
Though Ctrl+C and Ctrl+V could be used to copy the data. If data is in adjacent cell, it would be save time.
F4 - Repeat last action. For example if you had last highlighted a cell with Yellow Color, Pressing F4 would highlight current cell (or a selection of cell) with yellow color. Or if you had last formatted cells (border, bold, or whatever), pressing F4 would repeat same.
[Saves time, doesn't it?]
Alt + i + r (From 2003 works in 2007 as well) - Inserts a blank row or rows.
Alt + i + c - Inserts a blank column or columns
(Saves time required drag the mouse to to header column, right click and then use insert)
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 14 June 2013
Formula
Len
This formula counts the number of text string in a cell. For example if you want to validate the number of characters in a column which has PAN, you just have to put the following formula -
=LEN(cell reference)
It would return the number of characters in the cell. 10 would be valid and all other numbers would be invalid.
Same thing can be used to check integrity of bank number, IFSC codes, MICR codes, GL codes, Invoice number.
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 15 June 2013
Mid, Left and Right
Mid - If you want to extract a particular string or set of strings (Character/s) from a cell, this formula is very useful .
The formula would be =MID(Cell Reference,Start Number, Number of characters to be extracted)
For example if you want to extract 4th Character from a PAN data, then you can use =MID(Cell,4,1)
If you require 4th and 5th character, use MID(cell,4,2) and so on.
Similarly you can extract characters from the left, you can use =LEFT(Cell,Num of characters) and you can extract characters from the right using =Right(Cell,Num of characters).
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 16 June 2013
Convert Text to numbers
Quite often it so happens that data exported from some of the accounting softwares appear in Text Format even though the cells have numbers stored in them. The top-left corner is highlighted with green color and if you hover the mouse you get the message - Text stored as a number. The issue with this type of data is mathematical operations would not be possible till such text is converted into numbers.
If you click on the error message, it gives you the option of converting text to number. But if you have huge data this becomes a cubersome process.
An Alternative Approace
Type the digit 1 in any blank cell and copy the cell. Select the column which needs to be converted, Right click and select "Paste Special". Select the option multiply and press enter.
The entire text in the column is now in number format.
The Multiply/divide/add/subtract option available in the paste special command is also useful if you want to divide/multiply a set of cells with a common number - e.g. dividing the entire data /part of data with 1000.
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 17 June 2013
The "Go To" Function
We deal with huge data running into thousands of rows and may be hundreds of column. Say for example an auditee gave you a workbook with huge data with lots of formulae. As an auditor you want to check if any cell contains manual input instead of a formula?
Here is how this function helps.
Select the data you want to test/check.
Press "F5" or Ctrl+G, A GoTo dialogue box opens. Select Special - A new dialogue box would open with lots of options.
If you select constant - It would highlight all cells which do not have formulas.
If you select formulas - It would highlight cells with formulas.
If you select formulas and error option under forumla - It would highlight errors in forumlas like -#N/A, #Name? etc.
If you select blanks, It would highlight only blank cells
Once the cells are highlighted, you can fill color in the selected cells. Once a color is filled you can use the filter command and filter only colored cells.
The dialogure box which appears is attached.
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 17 June 2013
Some Shortcuts
CTRL+SPACEBAR - selects an entire column in a worksheet.
SHIFT+SPACEBAR - selects an entire row in a worksheet.
No need to keep dragging the cursor using mouse to select a row or column. Just use ctrl/shift and space bar.
Anu sharma
(Intermediate IPC)
(240 Points)
Replied 17 June 2013
Hi, This 63389 1188200 special.doc file is corrupt please send me file at my email Id
sekhar_fhr @ rediffmail.com
Thanks
Read more at: /forum/excel-tips-for-accountants-and-auditors-252703.asp
CA Suraj Lakhotia
(IndigoLearn)
(4898 Points)
Replied 17 June 2013
| Originally posted by : Sekhar Mandal | ||
 |
Hi, This 63389 1188200 special.doc file is corrupt please send me file at my email Id sekhar_fhr @ rediffmail.com Thanks Read more at: /forum/excel-tips-for-accountants-and-auditors-252703.asp |
 |
Hi
It just contains a screen shot of "Go To" dialogue box.
You get the same when you press "F5" on the keyboard.
Sajitha
(Student)
(223 Points)
Replied 18 June 2013
Thanks for Sharing .....
can u pls share formula for converting amounts in firgures to words
CAclubindia
