Sunday, October 02, 2005

Testing your work in Excel


I have just sat down with a nice tall glass of iced coffee to finalise delivery of a job to a client.
I thought I would write some notes about checking your work in Excel before delivering it to the client, boss, customer etc.

I have in the past been caught out many times by not properly checking work. (You would think I would learn!) I will qualify that by saying its the user's fault - always!

Seriously though it is easier to check something three times then to hand it in and get egg on your face when the customer asks why 40 + 30 + 20 = 70.

Some of the most common errors to look for when proofing your work are:
  • Sums that do not include the entire range. Make sure that row and column totals are right.

  • Spelling mistakes - use F7 to check the spelling.

  • Incorrect decimal places. If using divisors or multipliers check that the decimal point is in the right place. The data may mean nothing to you but the client might have kittens if the decimal place is out!

  • Make sure you set up print areas, margins, headers and footers and other print settings for reports. Test them. They may look OK on the screen but may print differently. Check on their printers also.

  • Users often misunderstand what you think is obvious. Check that the data entry areas are tested thoroughly. When creating large data entry areas test them ALL. I use a formula like =RAND()*1000 to generate a random number and then copy that over all the data entry areas.

  • Enter a space or text in a number data area and see what happens. Change values to zero. Do you get #REF! or #DIV/0! or #N/A! errors?

  • Make sure that if you don't protect formulas and calculation areas you don't want users to touch that you hide them or change the format etc so that they are obviously not data entry areas.



    • There are lots more things to check but thats a goood start.


    Posted by: Jethro | 8:08 PM |