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.

Richard Lanza is the president of Cash Recovery Partners, LLC and a director at CPA2Biz, where he focuses on using audit and assurance software. He recently chaired the first-ever audit and assurance software expo and is a certified member of both the AICPA and PMI. He lives in Lake Hopatcong, New Jersey.

About the Authors

Bill Jelen

Dwayne K. Dowell

Copying a Worksheet

1

(4)

Here's the Situation

1

(1)

Here's What to Do

1

(2)

Excel Details

3

(2)

Showing Numbers in Thousands

5

(4)

Here's the Situation

5

(1)

Here's What to Do

5

(2)

Gotcha

7

(1)

Excel Details

8

(1)

Quickly Seeing Sum or Average

9

(6)

Here's the Situation

9

(1)

Here's What to Do

9

(3)

Gotcha

12

(1)

Better in Excel 2007

12

(2)

Excel Details

14

(1)

Excel Details

14

(1)

Adding Subtotals

15

(8)

Here's the Situation

15

(1)

Here's What to Do

16

(5)

Excel Details

21

(2)

Removing Subtotals

21

(1)

Adding a Second Level of Subtotals

21

(2)

Quickly Filling a Series

23

(6)

Here's the Situation

23

(1)

Here's What to Do

23

(6)

Using a Fixed Value in Your Formula

29

(4)

Here's the Situation

29

(2)

Here's What to Do

31

(1)

Excel Details

32

(1)

Replacing a Thousand Formulas with One

33

(2)

Here's the Situation

33

(1)

Here's What to Do

33

(1)

Excel Details

34

(1)

Highlighting Outliers

35

(14)

Here's the Situation

35

(1)

Here's What to Do

35

(14)

Using Conditional Formatting in Excel 2007

36

(1)

Finding Transactions from the Last Week

37

(2)

Using Icon Sets to Mark Values

39

(4)

Excel Secrets: Applying Icons to Only the Top 10%

43

(3)

Using Conditional Formatting in Excel 2003

46

(3)

Turning Your Data on Its Side with Transpose

49

(4)

Here's the Situation

49

(1)

Here's What to Do

49

(2)

Better in Excel 2007

51

(1)

Excel Details

52

(1)

Joining Text

53

(4)

Here's the Situation

53

(1)

Here's What to Do

54

(3)

Looking up Data

57

(12)

Here's the Situation

57

(1)

Here's What to Do

58

(11)

Copying the VLOOKUP Formula to Get Region and District

60

(1)

Dealing with #N/A for Missing Values

61

(1)

When You Know There Is a Match but Excel Cannot Find It

62

(2)

Retrieving Many Columns from the Lookup Range

64

(1)

Getting Good Records from Bad Data

64

(2)

VLOOKUPs Take a Long Time to Calculate

66

(3)

Sorting Your Data

69

(12)

Here's the Situation

69

(1)

Here's What to Do

69

(3)

Sorting Data with One Click

69

(3)

Gotcha

72

(1)

Here's the Situation

72

(1)

Here's What to Do

72

(4)

Sorting in a Custom Sequence

72

(4)

Additional Details

76

(1)

Gotcha

76

(1)

Excel Details

76

(1)

Better in Excel 2007 -- Sorting by Color

77

(4)

Dealing with Dates

81

(4)

Here's the Situation

81

(1)

Here's What to Do

81

(4)

Calculating with Dates

81

(2)

Converting Text That Looks Like a Date to a Real Date

83

(1)

Fiscal Responsibility (Fiscal Years NOT Ending on 12/31)

83

(2)

Analyzing Data with Pivot Tables

85

(36)

Here's the Situation

85

(1)

Here's What to Do

85

(36)

Preparing Your Data

85

(2)

Creating a Summary with a Pivot Table

87

(1)

Creating Your First Pivot Table in Excel 97-2003

88

(5)

Creating Your First PivotTable in Excel 2007

93

(3)

Changing the Pivot Table Using the ``Add To'' Button in Excel 97-2003

96

(2)

Changing the Pivot Table in Excel 2007

98

(2)

Eliminating Blank Cells from the Data Section

100

(1)

Changing a Pivot Table by Dragging Fields

101

(3)

Showing Two or More Fields in the Data Area

104

(1)

Grouping Date Fields by Year

105

(2)

Grouping Date Fields by Month

107

(2)

Removing One of Many Data Items

109

(1)

Building an Ad-Hoc Report Using Page Fields or Report Filter

109

(1)

Showing Top 10 Customers

110

(4)

Drilling Down To See Detail

114

(1)

Pivot Tables and Recalculation

115

(1)

Limitations on Using Pivot Tables

116

(1)

Reporting Percentage of Row

117

(4)

Analyzing Results by Date

121

(8)

Here's the Situation

121

(1)

Here's What to Do

121

(8)

Filling in Weekdays

121

(2)

Filling in Conditional Counts

123

(1)

Further Analysis -- Sorting

124

(1)

Further Analysis -- Charting

125

(2)

Further Analysis -- Stratification by Weekday

127

(2)

Creating a Random Sample from a Dataset

129

(2)

Here's the Situation

129

(1)

Here's What to Do

129

(1)

Gotcha

130

(1)

Finding and Analyzing Records Using AutoFilter

131

(4)

Here's the Situation

131

(1)

Here's What to Do

132

(1)

Excel Details

133

(2)

Copying Only Filtered Records

133

(1)

Still More AutoFilter Options

134

(1)

Formula Auditing

135

(20)

Here's the Situation

135

(1)

Here's What to Do

135

(20)

Locating All Formulas with Show Formulas Mode

135

(3)

Highlighting All Formulas with Go To Special

138

(3)

Evaluating a Formula with the F9 Key

141

(1)

Evaluating a Formula in Slow Motion

141

(3)

Tracing Precedents or Dependents

144

(1)

Finding Dependents Using Formula Auditing Arrows

144

(3)

Finding Precedents Using Formula Auditing Arrows

147

(1)

Formula Auditing Tips

148

(1)

Numeric Headings Included in AutoSum Totals

148

(2)

Ignoring Order of Operations

150

(1)

Beware of Reset Error Indicators

151

(4)

Matching Two Lists

155

(10)

Here's the Situation

155

(1)

Here's What to Do

156

(6)

Viewing Two Files Side-by-Side

156

(1)

Building a Match Function

156

(3)

Allowing for More Names to Be Added

159

(1)

Preventing #N/A Errors

159

(2)

Retrieving the Date Returned Using VLOOKUP

161

(1)

Better in Excel 2007 -- Replacing #N/A

162

(3)

Finding Duplicates or Unique Values

165

(14)

Here's the Situation

165

(1)

Here's What to Do

165

(14)

Finding Duplicates

165

(1)

Finding Duplicates in Excel 2007

165

(4)

Finding Duplicates in Excel 97-2003

169

(2)

Finding Unique Values

171

(1)

Using a Formula to Isolate Unique Values

171

(1)

Using Advanced Filter to Find Unique Records

172

(1)

Using a Pivot Table to Find Unique Records

173

(3)

Using Remove Duplicates in Excel 2007 to Find Unique Records

176

(3)

Finding Missing Dates in Data

179

(10)

Here's the Situation

179

(1)

Here's What to Do

179

(10)

Using NETWORKDAYS

179

(5)

Finding the Gaps

184

(1)

Using AutoFilter in Excel 97-2003

185

(1)

Better in Excel 2007 -- Using AutoFilter

186

(1)

Next Steps

187

(2)

Automating Excel with VBA

189

(18)

Here's the Situation

189

(1)

Here's What to Do

189

(18)

Allowing VBA to Run On Your Computer

190

(1)

Inserting a VBA Module in Your Workbook

190

(1)

Writing and Running a Macro

191

(3)

Creating a Macro to Loop Through All Records

194

(2)

Referring to Cells in the Loop

196

(1)

Referring to an Entire Row Using CELLS

196

(1)

Referring to a Rectangular Range Using CELLS

196

(1)

Macro to Color Every Third Row Red

197

(1)

Making Decisions Based on Values in the Row

198

(1)

Special Handling When Deleting Rows

199

(1)

Handling an Unknown Number of Rows

200

(1)

Working with Other Worksheets

201

(1)

Looping through all Worksheets

202

(1)

Using the Macro Recorder to Learn Other Code

202

(3)

A Final Note About Recording Macros

205

(2)

Conclusion

207

(2)

Index

209

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Amazon no longer offers textbook rentals. We do!

Amazon no longer offers textbook rentals. We do!

We're the #1 textbook rental company. Let us show you why.

Excel for Auditors Audit Spreadsheets Using Excel 97 through Excel 2007

9781932802160

1932802169

Supplemental Materials

Summary

Author Biography

Table of Contents

Supplemental Materials

Rewards Program