Changing Scientific Notation to Standard Notation in Excel

This video shows Dr. Evan Matthews explaining how to change the format of a number from scientific notation to standard notation in Excel. Scientific notation is used in Excel by default when a very small or very large number results from an Excel function. However, most people find standard notation easier to read and comprehend.

Link to Dr. Evan Matthews website.

[aanchalsharma]

thank you so this question had been with me for so thank you

[NejinPokharel]

Hi if this video doesn't help, all you need to do is increase the width of your column

[kaushikumarihami]

This video has saved my biggest issue. Thank yiu very very much..good luck for all other videos too

[trangluu]

thank you so much, you save my assignment

[Jaypoc]

This doesn't always work as expected. I have a database with with IDs that consist of 16 digit numbers. If imported directly into Excel, they get converted to scientific notation, even if the source data was supplied as Text. For example: "1234512345123451" becomes "1.23451E+15"
which if you convert back to numeric using this method, or even to text, becomes "1234512345123450".
The solution we use to load the data into Excel is to Import using the Text Import Wizard (Data -> From Text). The third step allows you to change the formatting of the field on import so the numbers are never converted in the first place.

[miloka]

Hi! Is there a way to change it permanently?

I need to copy numbers into CSV multiple times a day and it's really annoying

Thanks

[ellianakyriacou]

how do you do it the other way, standard notation to scientific

[tiffanyann]

Hey, while this didn't work for me, I think I figured it out my issue; I had to go to Excel Options > Data > Show Legacy Data Import Wizards and check all of them off. Then I was able to go to Get Data > Legacy Wizards -> Import from Text/CSV -> Select Delimited -> And fix all the tracking numbers that were showing up in Scientific notation like I used to. Your method didn't work for me, but restoring legacy import wizards did. :)

[lseh]

I also was curious as to why when you “convert to text” (regarding upc numbers) so that I can do vlookups some covert fine (visually) and leading zeros are just removed and the others that don’t have a leading zero show visually as a this scientific format where you can’t tell what the number is unless you view it via the formula bar. Why does this happen and is there a way for it to show properly without affecting vlookup? All UPC’s are 12 digits, some start with zeros and some don’t.

[jevancashmore]

Hi, I have a dataset that I’m working on for a large retailer. The issue I’m having is that once I copy (copy & paste) any of the barcodes that are listed excel automatically changes it to scientific notation. The cells were not previously formatted, so I don’t understand why it’s not always showing up as scientific notations? The annoying part is that when I format the cell (numbers - no decimal place), it changes the location of the digits so they sit slightly to the right inside the cell.

I guess it’s not a big deal, but it makes the whole spreadsheet look messy. If I try and format all cells at once, only the cells that currently show scientific notations (which are the barcodes I have previously copied from), sit to the right of the cell. I was hoping that formatting all the cells at once would cause all of the numbers to sit to the right of the cell so it wouldn’t look so messy.

Any advice?

[faisalmalik]

I have a question.. In Notepad the number is 123456789898 and when I open this into excel 123456789800....it automatically removed last two original number which was "98" and added "00" at the end. how can be find out these issue can anyone help on this

[rudranibiswas]

Thank u sir. But do i have to do it one by one? For each number?

[solomonreggae]

thank you so much Sir. You help me complete my Project

[SpiritOfIndiaaa]

If save and reopen then it shows again scientific notation ...how to get it rid of it all the time

[shannoni]

How do you convert to number if you receive the file from someone and it's saved in scientific notation?

[osamabad]

How do you make calculations based on scientific notation in excel?

[marcmalecki]

You do t talk about if numbers are lost at the end like if you have fifty 0s followed by 123 and do your conversion, it will give you all the zeros and leave out the 123 at the end

[maiaadam]

how do you extend it to the rest of column

[realisticworld]

Try this formula
=trim(select data cell) hit enter

[brumiloliveros]

How to covert a normal text example 002E+07