Excel Guides

Excel Applies Scientific Notation to Imported Data

If you import data into Microsoft Excel that contains very large or very small numbers, Excel may apply scientific notation to the data. Scientific notation is a way of writing numbers that are too large or too small to be conveniently written in decimal form. In scientific notation, all numbers are written as a coefficient and an exponent. The coefficient is always greater than or equal to 1, and less than 10. The exponent indicates how many places the decimal point must be moved in order for the number to be written in scientific notation. For example, the number 500 can be written as 5 x 102. This means that the decimal point must be moved 2 places to the left in order to get the number 5.0. Similarly, the number 0.005 can be written as 5 x 10-3. This means that the decimal point must be moved 3 places to the right in order to get the number 0.005.

If you have data in Excel that is in scientific notation, there are a few ways that you can convert it back to regular numbers. One way is to use the TEXT function. The TEXT function allows you to format a number as text. To convert a number from scientific notation to regular number using the TEXT function, you need to use a format code that will display the number without exponential notation. The format code for this is "0". So, if you have a cell with the value 1 x 105, you would use the following formula:

=TEXT(A1,"0")

This would return 100000 in the cell.

Another way to convert a number from scientific notation to regular number is to use FORMAT. The FORMAT function also allows you to format a number as text. However, FORMAT has more options for formatting numbers than TEXT does. To convert a number from scientific notation to regular number using FORMAT, you need to use a format code that will display the number without exponential notation. The format code for this is "#". So, if you have a cell with the value 1 x 105, you would use the following formula:

=FORMAT(A1,"#")

This would also return 100000 in the cell.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.