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.

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

• How to Use SEQUENCE Function in Excel (16 Examples)
• LARGE function in Excel
• How to use RAND function in Excel (5 Examples)
• Solving equations in Excel (polynomial, cubic, quadratic, & linear)
• How to Use SUMPRODUCT Function in Excel (4 Examples)

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.

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.

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

• 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.

• 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

• 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.

• 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.

• 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.

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

• 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.

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

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

• 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

• How to Use SUMIFS Function in Excel (6 Handy Examples)
• 51 Mostly Used Math and Trig Functions in Excel
• How to Use MOD Function in Excel (9 Suitable Examples)
• Use ROUND Function in Excel (With 9 Examples)
• How to Use SUM Function in Excel (With 6 Easy Examples)
• Use INT Function in Excel (With 8 Examples)