Quick Data Type Check In Excel
Microsoft Excel is a great program. An Excel cell can handle many different
kinds of data. You can input text, dates, and numbers of various sorts like
percentages, integers, and floating point numerals.
The problem is that Excel does not require any one type of data in a column.
The first row of a column typically is text - the column title. The rows
following may contain integer data. Below the data (in the same column!) there
may be a spreadsheet summary containing the date of the information and say, an
average of the rows above which will be a floating point numeral. That’s OK!
Excel is programmed not to care what type of data is in what
cells. While this is very convenient, it can be very dangerous. Look at the
sample spreadsheet below, there are some none too obvious errors in it.
click the image for a video of this functionality
The first thing that stands out is that some cells are left justified and
some cells are right justified. You may be tempted to click on one of the
justification icons and force the cells into the same justification. But Excel
is trying to tell you something. Text is left justified and numbers are right
justified by default. What Excel is trying to tell you is that the first ‘date’
and the last ‘date’ in column A are really just text. The first two numbers in
column B are also text. Look carefully at the date that is supposed to be April 4. It
actually says A-P-R-I and the number 1. The last supposed date is April 31 -
there are only 30 days in April.
The numbers formatted as text are another problem. Numbers can become
formatted as text in different ways. The most common cause is that they were
copied from another document and pasted into Excel. Whatever the cause, you can
see some obvious problems trying to add, subtract, or divide by text. What is
fore divided by ate plus won?? The square root of your SSN?
If you have a large spreadsheet and you want to do a quick check,
change the formatting of the column. For instance, use a format like mm/dd/yy
instead of the current formatting in column A above. Values in column A which
are not real dates will retain their current look, real dates will take on the
new format. The same goes for column B. Change the format to currency. If a
number is formatted as text, it will not take on the new format. Take a look at
the video to see this in
action. Once you’ve had a quick look at the data you can undo your formatting
changes. To change numbers formatted as text back into numbers, see
Paste and Paste Special... in the
July 2003 newsletter.
This kind of data check is a quick fix, and is no substitute for proper data
validation. To see an article about data validation in Excel, see the article
"Block That Spreadsheet Error"
that was published in theJournal of Accountancy. For complete data validation, you should store
your data in a database and bring it into Excel only when you need to do some
work on it.
|
|