How to Turn Off Scientific Notation in Excel (5 Useful Methods)

If you are looking for how to turn off scientific notation in Excel, then you are in the right place. This article will first introduce you to the scientific number system and number precision definition. Then you will know about the highest and lowest number of limitations in Excel. After discussing that we have narrated how you can turn off / stop auto scientific notation in Excel.
Disclaimer: “Turn off scientific notation in Excel” – with this phrase we actually don’t mean we are going to TURN OFF the scientific notation in Excel. We are actually changing the way the numbers are displayed in Excel cells.


Download Practice Workbook


How Does Scientific Notation Work?

Sometimes, especially while using a calculator, you may end up with a very long number. For example, if you have a very large number like  1234567894578215153456789, this number has 25 digits. Or you may face a small number like 0.12345621345722156652231.

To use these types of numbers conveniently, you can express them in scientific notation.
Let’s take a smaller number in scientific notation, 7245 becomes 7.245E+3.
How? The decimal point moved 3 digits left. So, the scientific notation is 7.245E+3, +3 as the decimal point has moved left. So, you will express the movement with an E.
In scientific notation 183857.419 becomes 1.83857419E+5 as for this number, the decimal point has moved 5 digits left.
In scientific notation, this small number, 0.00007245 becomes 7.245E-5. As the decimal point has moved 5 digits right. In the same way, the number 0.0000000625431 will become 6.25431E-8, as the decimal point has moved 8 digits right.

scientific number system, Excel, E


What Is Number Precision?

Now let’s discuss number precision. The precision of a number is how many digits of a number are shown. Considering the same numbers 7.245E+3 number’s precision is 4 as it is showing that many digits.
1.83857419E+5 number’s precision is 9; as it’s showing 9 digits.
7.245E-5 number’s precision is 4 as it has 4 digits.
And lastly, 6.25431E-8 number’s precision is 6 as it is showing that many digits.


Similar Readings


What Can Excel Handle?

The largest positive number that you can store in a worksheet cell is 9.9E+307. It is 99 then three hundred and six zeros. It is an extraordinarily large number, of course.
The smallest negative number you can store in a worksheet cell is -9.9E-307. It is minus zero points, three hundred and six zeros, and then 99.


5 Methods to Turn Off Scientific Notation in Excel

Excel offers various ways to turn off scientific notation. We’ll try to discuss it effectively so that you can understand it easily. Suppose you have the following dataset with a column header With Scientific Notation and in this column numbers with scientific notation are included. You just need to make them Without Scientific Notation in the C Column.

turn off scientific notation in excel


1. Using Cell Formatting to Turn Off Scientific Notation in Excel

You can turn off scientific notation by using Cell Formatting. It just changes the aspect of a number without changing the number itself. For Numbers Excel uses the General format by default. You need to follow the steps below to turn off scientific notation.

Steps:

  • Firstly, copy the data from B5:B8 and paste them into the C5 cell.
  • Secondly, right-click on the pasted cells and choose Format Cells.

Using Cell Formatting to Turn Off Scientific Notation in Excel

  • Thirdly, go to Number > change the value in the Decimal places to 0.
  • Fourthly, click OK.

Using Cell Formatting to Turn Off Scientific Notation in Excel

  • Eventually, you’ll see that all the outputs are now without scientific notation.


2. Utilizing TRIM Function to Turn Off Scientific Notation in Excel

You can use different functions to remove scientific notation. The TRIM function is one of those to use. This function removes all spaces from text except for single spaces between words.

Steps:

  • Firstly, you need to write the following formula in the C5 cell like this.
=TRIM(B5)

Utilizing TRIM Function to Turn Off Scientific Notation in Excel

  • Secondly, press ENTER to get the output that doesn’t have scientific notation.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding the reference C5 cell at the right-bottom

Utilizing TRIM Function to Turn Off Scientific Notation in Excel

  • Consequently, you get your output like this.


3. Applying CONCATENATE Function

The CONCATENATE function is another function by which we can remove scientific notation easily. This function works the same way as the TRIM function and gives the same output. Though the CONCATENATE function mainly combines two or more strings in a cell, it can be used to remove scientific notation.

  • Firstly, write the following formula in the C5 cell like this.
=CONCATENATE(B5)

Applying CONCATENATE Function

  • Secondly, press ENTER and use Fill Handle to get all the outputs without scientific notation.


4. Using UPPER Function

You can use the UPPER function also to remove scientific notation. Basically, the UPPER function makes the entire text into all capital letters. But if you apply the function in removing the scientific notation, you’ll get the same output as found in the earlier methods. You just need to write the formula in the C5 cell like this.

=UPPER(B5)

Using UPPER Function

  • Similarly, as before, press ENTER to get the output and subsequently, use Fill Handle to get other outputs which are without scientific notation.


5. Adding Apostrophe

Adding apostrophe at the beginning of the numbers is a unique method to remove scientific notation.

Steps:

  • Firstly, copy and paste the numbers from Column B to Column C.
  • Secondly, double click on the C5 cell and put an apostrophee. (‘) at the beginning of the number.

Adding Apostrophe at the Beginning to Turn Off Scientific Notation in Excel

  • Thirdly, press ENTER.
  • Eventually, you’ll see that the scientific notation is removed.

Adding Apostrophe at the Beginning to Turn Off Scientific Notation in Excel

  • Fourthly, follow the same steps for other cells and get the output like this.


How to Remove Scientific Notation in Excel CSV/Text File

Also, you can remove the scientific notation from CSV or Text (.txt) files also. Here are the steps of creating a CSV file and removing scientific notation.

Steps:

  • Firstly, open Notepad and put a large number like the picture below.

How to Remove Scientific Notation in Excel CSV/Text File

  • Secondly, to save this file as CSV, click File > choose Save As.

How to Remove Scientific Notation in Excel CSV/Text File

  • Thirdly, give a CSV type name in the File Name In this case, it is Removing Scientific Notation.csv.
  • Fourthly, click Save.

How to Remove Scientific Notation in Excel CSV/Text File

  • Fifthly, go to an Excel file and click Data > choose From Text/CSV.

  • Now, select the CSV file from the specified location where you have saved it.
  • Sixthly, click Load.

  • Eventually, you’ll see that the value in the CSV file is transferred to the Excel file.
  • Importantly, the value is with scientific notation.

  • Now if you want to turn off scientific notation, use Cell Formatting or any of the functions mentioned in the previous section of this article to remove scientific notation and get the output thereby like this.


Things to Remember

  • If you input any enormous number in Excel, you’ll see the number with the scientific notation by default.
  • More importantly, TRIM, CONCATENATE, and UPPER functions don’t give output without scientific notation when the number is equal to or exceeds 20 decimal points.

Conclusion

You can remove the scientific notation from large numbers if you study this article carefully. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.


Read More: How to Use Excel EXP Function (5 Examples)


Further Readings

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

5 Comments
  1. Hi, I work with UPC codes, and Excel has this habit of converting them into scientific notation. It’s super annoying to say the least. This solution is just as others have provided, it stops that instance from happening but does not turn this “feature” off. How can I accomplish this?

    Thank you.

    Matt

  2. “Turn off scientific notation in Excel”

    You are not turning it off, you change the way the number is displayed, which isnt the same thing.
    You might want to change that statement, because it is misleading.

    Thank you,
    Mark

  3. I am disappointed at the click-bait on this site. As Mark mentioned, you are not turning off the scientific notation, and this does nothing to display the actual number unless you only have two decimal places in your number. Given that you specifically point out that this is a problem with small numbers, it seems especially unhelpful to just pretend that this solved the problem.

Leave a reply

ExcelDemy
Logo