Excel tips for Accountants and Auditors

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. 

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)

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)

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. 

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

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

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.  

Nice Post Keep Sharing

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.

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. 

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

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. 

Today's tip -

/articles/errors-in-excel-17744.asp

Thanks for Sharing .....

can u pls share formula for converting amounts in firgures to words