Application Management Services

Using Excel as an Audit Software

CA Aditya Singhal M.Com, FCA, DISA(ICAI)

Join Excel professional group for regular Excel update: http://tech.groups.yahoo.com/group/Excel-Professional/

Confidential | March 2004

© 2004 IBM

Confidential |

Corporation

© 2005 IBM Corporation

Using Excel as an Audit Software

HIT


Application Management Services

1

CLASSIFICATION OF EXCEL COMMANDS

▪ 1. Split windows and freeze panes

▪ 2. Hide and Unhide rows & columns

▪ 3. Moving Ctrl, Shift, around and Arrow a spreadsheet keys

with

▪ 4. Name cells/ranges

▪ 5. Toggling absolute references

among relational and

▪ 6. Fill down and fill right commands

▪ 7. Insert Function command

▪ 8. Paste Special command

▪ 9. Change the case of text

▪ 10. Editing simultaneously

multiple work sheets

▪ 11. Customize tool bars

▪ 12 Shorts cuts Keys

▪ 13. Auditing features

▪ 14. Sort command

▪ 15. If function

▪ 16. Sum & If Sum

▪ 17. And & Or functions

▪ 18. Protecting worksheets

cells and

▪ 19. Group your spreadsheet

and Ungroup

▪ 20. Subtotal function

▪ 21. Sum Product function

▪ 22. Auto filter command

▪ 23. Conditional formatting

▪ 24. Count function

▪ 25. Round, Round down Roundup,

functions

▪ 26. Vlookup & Hlookup

▪ 27. Statistics

▪ 28. Pivot table

▪ 29. Attach file in Excel sheet

▪ 30. NPV Functions

© 2005 IBM Corporation


Application Management Services

2

▪ Why you need to know this

▪ How you use this feature

▪ Exercise

1. SPLIT WINDOWS AND FREEZE PANES

- Splitting a window allows you to work on multiple parts of a large spreadsheet simultaneously - Freezing the pane allows you to always keep one part of the spreadsheet (e.g., column or row labels) visible

- Drag the split horizontal and split vertical icons to the desires positions - Click on the freeze pane icon from the tool bar to freeze the panes

- Split the screen so that: The row with column labels shows up in the top pane The column with store names show up in the left pane - Freeze the panes

© 2005 IBM Corporation

Split screen icons

Freeze pane icon

Why yo need to

know this

How you use this feature

- Exercise


Application Management Services

3

▪ How you use this feature

▪ Exercise

▪ Why you need to know this

2. HIDE AND UNHIDE COMMAND

- Allows you hide and unhide particular rows or columns Simplifies working with the spreadsheet Prevent certain information from being seen

- Select the row(s) or column(s) to be hidden/unhidden - Select Format : Row : Hide/Unhide or Format : Column : Hide/Unhide

- Hide the Avg Sale/Ticket column

© 2005 IBM Corporation

I Why you

need to V nmnw this

How you use this

‘Jeatu re

- Exercise


3. MOVING Application AROUND Management A Services

SPREADSHEET WITH CTRL, SHIFT, AND ARROW KEYS

4

▪ How you use this feature

▪ Exercise

▪ Why you need to know this

- Save you lots of time - Move the first or last cell of a contiguous data block without scrolling

- Ctrl-Arrow : Move to the first/last data cell in the arrow direction - Ctrl-Shift-Arrow : Selects the cells between the current cell and the first/last data cell

- Select all cells with data using the Ctrl, Shift, and Arrow keys

© 2005 IBM Corporation

I Why you

need to V nmnw this

How you use this

‘Jeatu re

- Exercise


Application Management Services

5

▪ How you use this feature

▪ Why you need to know this

▪ Exercise

4. NAME CELLS/RANGES

- Allows specific cells or cell ranges to be referred to by name - Allows you to write equations such as = Quantity*Cost instead of =$B$12*$C$4

- Select the cell or cell range - Select Insert : Name : Define from the menu bar

- Define cells A2:A125 as “Sequence”

© 2005 IBM Corporation

Why yo need to

ow this

How you use tEjis feature

- Exercise


Application Management Services

6

▪ How you use this feature

▪ Why you need to know this

5. TOGGLING AMONG RELATIONAL AND ABSOLUTE REFERENCES

- Saves you lots of time

- F4 key toggles through the different options

© 2005 IBM Corporation

Why you need to

know this

How you use this

‘Jeatu re


Application Management Services

7

▪ How you use this feature

▪ Exercise

▪ Caution!!

▪ Why you need to know this

6. FILL DOWN AND FILL RIGHT COMMANDS

- Saves you lots of time - Allows for copying of cell content to contiguous cells with a single keystroke

- Select the cell with the content to be copied and drag to select the cells to which the content should be copied - Ctrl-R to fill right - Ctrl-D to fill down

- Double-check your formulas for absolute vs. relative references!!

- Calculate the total daily sales for each store

© 2005 IBM Corporation

Why you need to

know this

How you use this

Ueatu re

Caution!!

Exercise


Application Management Services

8

▪ How you use this feature

▪ Exercise

▪ Why you need to know this

7. INSERT FUNCTION COMMAND

- What do you do if you do not know what functions are available or how to enter the arguments for a function?

- Select the cell - Select Insert : Function from the menu bar

- Calculate the median daily ticket count for all the stores

© 2005 IBM Corporation

I Why you

need to V nmnw this

How you usemhis

Ueatu re

Exercise

Function gategury:

Function game:

Adds all the numbers in a range of cells.


Application Management Services

9

▪ How you use this feature

▪ Exercise

▪ Why you need to know this

8. PASTE SPECIAL COMMAND

- Saves you lots of time Retyping formulas Converts formulas into values Reformatting cells Transposing cells (i.e., convert row-entered data blocks into

column-entered ones) - Copy the cells of interest - Place the cursor where you want to past the information - Select Edit : Paste Special from the menu bar - Select the appropriate options from the dialog box that appears

- Convert the Rounded Avg Sale/Ticket calculations into values (i.e., get rid of the formulas) - Copy and paste the entire dataset into a new spreadsheet in transposed manner

© 2005 IBM Corporation

Why you need to

How you use thUs feature

Exercise

mow this

Paste

5' Q Qomments Q Eormulas Q Validating Q lalues Q All egcept borders Q Formags

Operation 7

Q Ngne Q Multiply

Q Skip Uanks H Transposg