How to convert scientific numbers to show their full amount; this method also allows you to retain the appearance of text in case you have a mixed list of text and numbers in scientific notation format.
There are a number of ways to do this and what you should use depends on your dataset.
Input a Number so it's Never Converted to Scientific Format
This method shouldn't disturb any text that might be in your list (read the bottom of this section for the exception).
This requires inputting a formula in a column next to the number.
=TRIM(cell_reference)
The TRIM() function removes extra spaces from the front and back of a cell in Excel. This will not harm or mess-up any text cells that you might have in your list when you copy the formula down unless those text cells are supposed to have extra spaces around them. If you need to retain extra spaces, you can use UPPER() or LOWER() or PROPER(), though each will change the case of the text.
This method keeps all of your data in the same place and will not harm any text that might be in your list.
This is a great method to use; it's simple, fast, and reliable.
If you are entering numbers by hand, you can use this quick tip to prevent them from converting to scientific format:
Type a single quotation mark/apostrophe in front of the number.
Notice the comma in front of the number in cell A5.
When I hit Enter, we still see the entire number.
The apostrophe will still be in the cell, but you will only see it when you go to edit the cell.
Unfortunately, Excel has limitations when it comes to numbers and sometimes Excel thinks it's helping us when, really, it is just being a pain in the neck. This tutorial is meant to show you a few methods for getting your numbers back.
Make sure to download the file attached to this tutorial to work with these examples in Excel.