×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

Be Aware: Excel can Change Your Data by SkipVought
Posted: 16 Aug 10

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 × 10b 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: Understanding Dates and Times & why they seem to be so much trouble?
FAQ68-6659: When is a NUMBER not a NUMBER?

 

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close