LIVE GST Certification Course by CA Arun Chhajer begins 27th April. Register Now!!

Excel tips for Accountants and Auditors

CA Suraj Lakhotia (IndigoLearn) (4898 Points)

14 June 2013

Spreadsheet 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.

Reply
Follow
Share

19 Replies

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)

1 Like

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.

2 Like

(Guest)

Thanx Sir 4 Sharing........

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).

1 Like

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.

1 Like

CA.Vinita Gupta (Deputy Manager- Finance) (2918 Points)
Replied 16 June 2013

Nice Post Keep Sharing

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.

Attached File : 63389 1188200 special.doc downloaded: 351 times

1 Like

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.

1 Like

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

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