Wednesday, December 22, 2004

Correctly Entering and Deleting Data in Excel

One of my pet hates is poor spreadsheet use. Probably the single biggest cause of errors that I get asked to fix is incorrectly entered or deleted data.

A lot of people are used to word processing applications and tend to treat Excel as one beacuse it appears to mimic one. Nothing could be further from the truth.

Here are the most common causes of errors I find.

Spaces entered after a word or phrase typed into a cell.
A cell "deleted" by pressing the space bar and hitting enter or down arrow. Note this cell is not deleted - it now has a space character in it.
Cells formatted as dates when they should be values.
Cells formatted as text when they are supposed to be numbers or values.
Merged cells. (See separate entry about merged cells.)
Cells "deleted" by using the Edit Clear Contents (or Delete Key).
Hiding rows and columns and then deleting through them and deleting required data.

I will discuss the impact of the spaces errors listed above in two future posts on SUMIF / SUMPRODUCT and VLOOKUP / HLOOKUP.