Excel for Auditors.pdf

Excel for Auditors

by Bill Jelen and Dwayne K. Dowell

Holy Macro! Books

Excel for Auditors

or by any means, electronic or mechanical, including photocopying, recording, or by any

information or storage retrieval system without permission from the publisher.

Every effort has been made to make this book as complete and accurate as possible, but

no warranty or fitness is implied. The information is provided on an “as is” basis. The

authors and the publisher shall have neither liability nor responsibility to any person

or entity with respect to any loss or damages arising from the information contained in

this book.

Written by:

Bill Jelen and Dwayne K. Dowell

Edited by:

Linda DeLonais

On the Cover:

Design by Shannon Mattiza, 6’4 Productions.

Published by:

Holy Macro! Books

PO Box 82

Uniontown, Ohio, USA 44685

Distributed by:

Independent Publishers Group

First printing:

September 2006.

Printed in the United States of America

Library of Congress Data

Excel for Auditors / Bill Jelen and Dwayne K. Dowell

Library of Congress Control Number: 2006931383

ISBN: 1-932802-16-9

Trademarks:

All brand names and product names used in this book are trade names, service marks,

trademarks, or registered trade marks of their respective owners. Holy Macro! Books is

not associated with any product or vendor mentioned in this book.

Table of Contents

About the Authors .. ..................................................................... a

Bill Jelen... ....................................................................................................................................... a

Dwayne K. Dowell... ....................................................................................................................... a

Copying a Worksheet ..................................................................1

Here’s the Situation ..................................................................................................................... 1

Here’s What to Do ........................................................................................................................ 1

Excel Details... ............................................................................................................................... 3

Showing Numbers in Thousands .. ................................................ 5

Here’s the Situation ... .................................................................................................................. 5

Here’s What to Do ... ..................................................................................................................... 5

Gotcha ... ........................................................................................................................................ 7

Excel Details... ............................................................................................................................... 8

Quickly Seeing Sum or Average .. ................................................. 9

Here’s the Situation ... .................................................................................................................. 9

Here’s What to Do ... ..................................................................................................................... 9

Gotcha ......................................................................................................................................... 12

Better in Excel 2007 .................................................................................................................. 12

Excel Details.... ............................................................................................................................ 14

Adding Subtotals ... ................................................................... 15

Here’s the Situation .... ............................................................................................................... 15

Here’s What to Do ... ................................................................................................................... 16

Excel Details.... ............................................................................................................................ 21

Removing Subtotals .... .................................................................................................. 21

Adding a Second Level of Subtotals.... ......................................................................... 21

Quickly Filling a Series ... .......................................................... 23

Here’s the Situation .... ............................................................................................................... 23

Here’s What to Do ... ................................................................................................................... 23

Using a Fixed Value in Your Formula ... ...................................... 29

Here’s the Situation .... ............................................................................................................... 29

Here’s What to Do ... ................................................................................................................... 31

Excel Details................................................................................................................................ 32

Replacing a Thousand Formulas with One... ............................... 33

Here’s the Situation .... ............................................................................................................... 33

Here’s What to Do ... ................................................................................................................... 33

Excel Details.... ............................................................................................................................ 34

Excel for Auditors

Table of Contents

Highlighting Outliers .... ............................................................. 35

Here’s the Situation .... ............................................................................................................... 35

Here’s What to Do ... ................................................................................................................... 35

Using Conditional Formatting in Excel 2007 .... ........................................................... 36

Finding Transactions from the Last Week ... ....................................................... 37

Using Icon Sets to Mark Values ... ........................................................................ 39

Excel Secrets: Applying Icons to Only the Top 10% .... ........................................ 43

Using Conditional Formatting in Excel 2003 .... ........................................................... 46

Turning Your Data on Its Side with Transpose... ......................... 49

Here’s the Situation .... ............................................................................................................... 49

Here’s What to Do ... ................................................................................................................... 49

Better in Excel 2007 .... .............................................................................................................. 51

Excel Details.... ............................................................................................................................ 52

Joining Text ... ........................................................................... 53

Here’s the Situation .... ............................................................................................................... 53

Here’s What to Do ... ................................................................................................................... 54

Looking up Data ... .................................................................... 57

Here’s the Situation .... ............................................................................................................... 57

Here’s What to Do ... ................................................................................................................... 58

Copying the VLOOKUP Formula to Get Region and District ... ..................................... 60

Dealing with #N/A for Missing Values... ....................................................................... 61

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

Retrieving Many Columns from the Lookup Range ... .................................................. 64

Getting Good Records from Bad Data... ....................................................................... 64

VLOOKUPs Take a Long Time to Calculate .... .............................................................. 66

Sorting Your Data ... .................................................................. 69

Here’s the Situation .... ............................................................................................................... 69

Here’s What to Do ... ................................................................................................................... 69

Sorting Data with One Click ... ....................................................................................... 69

Gotcha .... ..................................................................................................................................... 72

Here’s the Situation .... ............................................................................................................... 72

Here’s What to Do ... ................................................................................................................... 72

Sorting in a Custom Sequence .... ................................................................................. 72

Additional Details .... ................................................................................................................... 76

Gotcha .... ..................................................................................................................................... 76

Excel Details.... ............................................................................................................................ 76

Better in Excel 2007 – Sorting by Color .... ............................................................................. 77

Excel for Auditors

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: