Be Aware: Excel can Change Your Data

Just in case you might be wondering, Excel does this just about each time you enter some data. Let me explain, and you'll eventually see that this can result in some unexpected changes.

Suppose you enter 10 in A1. In actually, your keybaord enters "10" in the cell. Notice that there are QUOTES, meaning that you entered the CHARACTERS from your keyboard.

Excel 'looks' at the characters that you have ENTERed and immediately guesses that your intention was to enter a NUMBER, not just CHARACTERS, so Excel CONVERTS "10" to the value 10. Now it is not possible to 'see' the value 10 on your screen. The values is stored in the computer as 00001010 which is not printable, so Excel DISPLAYS the NUMBER as displayable CHARACTERS.

Well that's pretty obvious, although we hardly ever think about it. But there are other conversion that take place that are not so apparent.

Date/Time:

1) Excel looks at the string that you entered and guesses that it's a Date or Time (m/d/yy or m-d-yyyy for instance). This can be a blessing or it can be frustrating if you are really NOT entering a Date or Time.

2) Using your Regional Settings for Date & Time, Excel parses the string into year, month, day, hour, minute, second. Excel makes other assumptions if you do not enter a complete Date/Time string such as 3/2. For instance, in the USA, Excel assumes that 3/2 is month 3, day 2, current year and 17: is assumed to be 5:00:00 PM.

3) Using the parsed values, Excel CONVERTS the values to a Date/Time Serial Value. For instance, if I entered 2/3 18:, Excel converts this to 38386.75 which can be formatted 2/3/2005 18:00

Scientific Notation:

1) Excel looks at the string that you entered and guesses that it's Scientific Notation (aEb for instance). This can be a blessing or it can be frustrating if you are really NOT entering a number, like some sort of identification code (I have Machine IDs like 171E2 and THAT is what I want!).

2) Using the rule that Scientific Notation is based on a + 10^b where the exponent b is an integer, and the coefficient a is any real number, Excel takes the CHARACTERS that you entered, parses the exponent and coeficient, and CONVERTS these values into a floating point number.

In ALL of these instances, Excel actually CHANGES the entered value to something entirely different. So to insure that you data is NOT converted, you must do one of several things.

1) Format the cells as TEXT, BEFORE you enter any data. Once your data is entered and converted, format does NOTHING to fix a problem.

2) Enter values that you want to be stored as TEXT, with a leading APOSTROPHY. Excel interprets a leading APOSTROPHY as an identifier for a LABEL or TEXT.

3) If your data is in a text file, like a .csv, DO NOT OPEN THESE FILES AS A WORKBOOK!!! Rather, use Data > Get External Data > IMPORT Text File... where you can EXPLICITLY define the type of data in any column that has DATE/TIME values or columns having identifier data that is numeric.

Remember this rule of thumb: Formatting changes NOTHING! The underlying data remains UNCHANGED!

faq68-5827
faq68-6659