Excel for Auditors: Audit Spreadsheets Using Excel 97 through Excel 2007

✍ Scribed by Bill Jelen, Dwayne K. Dowell

• Publisher: Holy Macro! Books
• Year: 2006
• Tongue: English
• Leaves: 226
• Series: Excel for Professionals series
• Category: Library

✦ Synopsis

Providing the tools and techniques necessary for finding errors and fraud in audits, this guide for auditors looking to better validate their Microsoft Excel spreadsheets provides techniques for performing a risk assessment and gathering spreadsheet and other data from company systems. Performing audit data analysis using data and analytical management functions and pinpointing the common errors in spreadsheets with focused Excel tests is discussed, as are the best practices for error and fraud prevention when developing spreadsheets. This reference is fully updated to reflect Excel 12.

✦ Table of Contents

Table of Contents......Page 4
Dwayne K. Dowell......Page 10
Here’s What to Do......Page 0
Excel Details......Page 14
Here’s What to Do......Page 16
Gotcha......Page 18
Excel Details......Page 19
Here’s What to Do......Page 20
Excel Details......Page 24
Here’s the Situation......Page 26
Here’s What to Do......Page 27
Adding a Second Level of Subtotals......Page 32
Here’s What to Do......Page 34
Here’s the Situation......Page 40
Here’s What to Do......Page 42
Here’s What to Do......Page 44
Excel Details......Page 45
Here’s What to Do......Page 46
Using Conditional Formatting in Excel 2007......Page 47
Finding Transactions from the Last Week......Page 48
Using Icon Sets to Mark Values......Page 50
Excel Secrets: Applying Icons to Only the Top 10%......Page 54
Using Conditional Formatting in Excel 2003......Page 57
Here’s What to Do......Page 60
Better in Excel 2007......Page 62
Excel Details......Page 63
Here’s the Situation......Page 64
Here’s What to Do......Page 65
Here’s the Situation......Page 68
Here’s What to Do......Page 69
Copying the VLOOKUP Formula to Get Region and District......Page 71
Dealing with #N/A for Missing Values......Page 72
When You Know There Is a Match but Excel Cannot Find It......Page 73
Getting Good Records from Bad Data......Page 75
VLOOKUPs Take a Long Time to Calculate......Page 77
Sorting Data with One Click......Page 80
Sorting in a Custom Sequence......Page 83
Excel Details......Page 87
Better in Excel 2007 – Sorting by Color......Page 88
Calculating with Dates......Page 92
Preparing Your Data......Page 96
Creating a Summary with a Pivot Table......Page 98
Creating Your First Pivot Table in Excel 97-2003......Page 99
Creating Your First PivotTable in Excel 2007......Page 104
Changing the Pivot Table Using the “Add To” Button in Excel 97-2003......Page 107
Changing the Pivot Table in Excel 2007......Page 109
Eliminating Blank Cells from the Data Section......Page 111
Changing a Pivot Table by Dragging Fields......Page 112
Showing Two or More Fields in the Data Area......Page 115
Grouping Date Fields by Year......Page 116
Grouping Date Fields by Month......Page 118
Building an Ad-Hoc Report Using Page Fields or Report Filter......Page 120
Showing Top 10 Customers......Page 121
Drilling Down To See Detail......Page 125
Pivot Tables and Recalculation......Page 126
Limitations on Using Pivot Tables......Page 127
Reporting Percentage of Row......Page 128
Filling in Weekdays......Page 132
Filling in Conditional Counts......Page 134
Further Analysis – Sorting......Page 135
Further Analysis – Charting......Page 136
Further Analysis – Stratification by Weekday......Page 138
Here’s What to Do......Page 140
Gotcha......Page 141
Here’s the Situation......Page 142
Here’s What to Do......Page 143
Copying Only Filtered Records......Page 144
Still More AutoFilter Options......Page 145
Locating All Formulas with Show Formulas Mode......Page 146
Highlighting All Formulas with Go To Special......Page 149
Evaluating a Formula in Slow Motion......Page 152
Finding Dependents Using Formula Auditing Arrows......Page 155
Finding Precedents Using Formula Auditing Arrows......Page 158
Numeric Headings Included in AutoSum Totals......Page 159
Ignoring Order of Operations......Page 161
Beware of Reset Error Indicators......Page 162
Here’s the Situation......Page 166
Building a Match Function......Page 167
Preventing #N/A Errors......Page 170
Retrieving the Date Returned Using VLOOKUP......Page 172
Better in Excel 2007 – Replacing #N/A......Page 173
Finding Duplicates in Excel 2007......Page 176
Using a Formula to Isolate Unique Values......Page 182
Using Advanced Filter to Find Unique Records......Page 183
Using a Pivot Table to Find Unique Records......Page 184
Using Remove Duplicates in Excel 2007 to Find Unique Records......Page 187
Finding the Gaps......Page 195
Using AutoFilter in Excel 97-2003......Page 196
Better in Excel 2007 – Using AutoFilter......Page 197
Next Steps......Page 198
Here’s What to Do......Page 200
Inserting a VBA Module in Your Workbook......Page 201
Writing and Running a Macro......Page 202
Creating a Macro to Loop Through All Records......Page 205
Referring to a Rectangular Range Using CELLS......Page 207
Macro to Color Every Third Row Red......Page 208
Making Decisions Based on Values in the Row......Page 209
Special Handling When Deleting Rows......Page 210
Handling an Unknown Number of Rows......Page 211
Working with Other Worksheets......Page 212
Using the Macro Recorder to Learn Other Code......Page 213
A Final Note About Recording Macros......Page 216
Conclusion......Page 218
Index......Page 220