Scientific notation in Excel

24
2014-04
  • Vojtech R.

    I need make Number Format like scientific notation, but without E nor e. Just classic like this:

    alt text

    (In latex its 2.3\times10^3)

    Maybe Excel doesn't support this format.

    (I have on mind Number Format - for hundreds numbers - not in math formula)

  • Answers
  • dkusleika

    You could use html. Type this into Notepad

    <html>2.34 x 10<sup>-5</sup> + 5.67 x 10<sup>-6</html>
    

    Copy it and, in Excel, Paste Special - Unicode. It will render whatever html you have. You won't be able to edit it in Excel though.

  • devuxer

    Excel doesn't support that number format.

    I think you need to use a formula, like this:

    =A1/(10^ROUND(LOG10(ABS(A1)),0))&" x 10^"&ROUND(LOG10(ABS(A1)),0)
    
  • Bradley Mountford

    Probably not the best location for this question, but these links might help:

    http://peltiertech.com/Excel/NumberFormats.html

    http://www.ozgrid.com/Excel/CustomFormats.htm

    I have used both sites as a reference when dealing with Excel stuff in the past.


  • Related Question

    How can I avoid Excel reformatting the scientific notation numbers I enter?
  • Diomidis Spinellis

    When I enter a number like 8230e12 into a Microsoft Excel 2000 cell, Excel changes the number I entered into 8230000000000000. (This is what I get when I press F2 to edit the cell's contents, not what Excel displays in the cell). How can I force Excel to keep the data in the format I typed it and still be able to format it and use it as a number? Displaying the cell in scientific notation is not enough, because the exponent is not the same one as the one I typed.


  • Related Answers
  • Lunatik

    If you wish Excel to keep the cell formatted exactly as entered, i.e. 8230e12, regardless of whether you are looking at it or editing it, then this can't be done whilst retaining the ability to treat the cell contents as a number.

    The only way I can see round this is to enter your data in cells that are formatted as text, and then have another cell, formatted as some kind of number, that has a formula of =VALUE(A1) or whatever so that calculations can be performed on this cell.

    Other than that you are looking at some VBA to manage this, overkill I would have thought.

  • Indrek

    I had success in solving what I think is a similar issue to yours. All I did was create a custom format of "'#" (note the apostrophe before the hash) and applied it to the column containing the numbers that needed to be viewed in their 12+ digit form. My values were still correct though as the columns were initially set to general.

  • FoleyIsGood

    Format the cell as custom number format and you can tweak the display to whatever you need. E.g. "0000E+12" will display your "8230e12" as "8230E+12"

  • Marko

    Try this ="8230e12". It worked for me just now, after I saw that nothing you guys suggested was useful, gave this a shot.

  • user194092

    I had this with list of long numbers, about 14000 entered into one column. I highlighted the column, Data -> Text to Columns -> Fixed length -> Don't create any break lines. Clear any that show up -> Select column data format text -> Finish. Worked like a charm.

  • random
    1. Highlight the range of cells or column
    2. Click Custom format
    3. Look for question marks like: "??/??"
    4. Click Enter