Training Index Power Courses index

Excel for financial staff and auditors- Handling information

Excel is a common tool used by external/ internal auditors and financial accountants. This course is focused on the tools and features that will assist auditors in performing their tasks.

There are two focus points of the tutorial. This one shows you tools to use when you receive an excel file from a client and need to turn it into useful information. You can look at the second tutorial to see how to build spreadsheets for your own purposes (coming soon).

Be aware that these video clips use Windows Media Player. Click on the hyperlink and, depending on your connection speed, you may need to wait a few moments.

Title		Video Clip (Windows Media Player)
Receiving a spreadsheet from a client- understanding it!
The Auditing Toolbar As auditors, it is critical that at the very least you are aware of the features in Excel which make it easier to understand what you have received from a client. The auditing toolbar is fundamental to this knowledge as it allows you to interrogate a spreadsheet and understand how the various cells are being used.		Auditing Toolbar (5 min)
GoTo Special GoTo Special has a number of features which make it easier to understand a spreadsheet, especially if you have received it from some one else. Use it to identify (and color) all the inputs, to identify rows or columns of formula which are inconsistent, or any of its other features.		GoTo Special Command (7 min)
External Links The bane of an auditors life can be spreadsheets that are linked to other spreadsheets. Learn how to identify all the links and some methods to handle them.		External Links (5 min)
Seeing the underlying formula in all cells A quick and easy way to see what all the cells in a spreadsheet you receive have in them including the full formula.		See all formula (1 min)
Working with large chunks of data
Viewing multiple workbooks (or worksheets) No matter how hard you try, you will still need to look at different spreadsheets to compare information or search for anomalies. Learn how to see it all on one screen instead of having to click between screens.		View Multiple Windows (4 min)
Data Auto Filter Audit clients often provide information to auditors from their main IT systems in a spreadsheet format. This tool will show you how to perform some investigation into the underlying information such as identifying the top ten items.		Data AutoFilter (6 min)
Cleaning up data using the Data Auto Filter More often then not, information provided by clients is not in the correct format. Headers and footers are left in and subtotals and other items are included, which makes it difficult to investigate the data. This shows a nice method to get the information into an appropriate format for further work.		Data Cleanup with the AutoFilter (7 min)
Filling in the blank cells quickly and easily After using the Autofilter to clean the data, you sometimes need to get the data into a format on which you can use features like Pivot Tables. This shows a method to incorporate all the relevant information for a row of data on the same row.		Fill in blanks (3 min)
Paste Special Most people are aware of the use of the Paste Special command in order to copy formula and paste them as values. However, there are a number of other tools which are extremely useful to the auditor. These include changing the sign on all cells in a column or row, or transposing information from rows to columns or the other way.		Paste Special (11 min)
Pivot Tables No auditor, junior or senior, should try to understand information without making use of Pivot Tables. Cut and dice your data any way you want. Instantly produce reports that would normally take hours to rebuild. Learn as much as you can about Pivot Tables.		Pivot Tables (9 min)

Other Odds and Sods

Transpose
Clients give you information in columns but your audit senior likes to see information in rows. You can copy and paste to your hearts content or use these 2 simple methods to convert rows to columns or columns to rows.

Transpose

(3 min)

Find/ Replace
The find/ replace feature can be used in many ways by the successful auditor. Search for particular bits of information in a large data set, find stray links in a spreadsheet or replace sensitive information in a dataset with xxx.

Find/ Replace

(9 min)

Data Sort
Your need to sort the data in a particular way to keep the audit partner happy. Learn some new tricks with the Data Sort feature including how to customize your sort order.

Sort data

(5 min)

Countif/ Sumif
You have received a ton of data from your client. Use the countif and sumif commands to perform some tests of the data, add up common invoices etc.

Sumif & Countif

(4 min)

Next Step
As auditors you will also need to build some of your own models. Learn about some of the key concepts by looking at Auditors Building Excel Spreadsheets (Coming Soon- Bookmark this site).

Quote of the page: I like thinking big. If you're going to be thinking anything, you might as well think big.- Donald Trump

Do you know about Spreadsheet Professional?

Visit the Spreadsheet Professional page to find out more.

Do you like what you see. Tell your friends. If you have a training co-ordinator at your organisation tell them that there is free excel training available at this site.

Don't like what you see. Tell us at training@AuditExcel.co.za .

Do you have suggestions of what you would like to see. Tell us at training@AuditExcel.co.za .