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.
- 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.
- 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.
- 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.
- 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.
- 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.
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)
- 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)
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?
“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.
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.
Thanks for the feedback.
And I will work with the click-baits to make it more user-friendly.
Thanks and regards
This isn’t helpful. The values are truncated. Notice the E+15, E+16, and E+20 examples particularly in the Concatenate example the zeros on the right side are growing. In the barcode comment, that users would lose the last product number and check digit. I’m planning to use Notepad++ and RegEx to split the number into 2 cell and format it in Excel. It appears Excel turns it into Scientific Notation when it opens the csv file. Our numbers are 20 digit “strings” as an ID.
Thank you for your query. So, your goal is to import data from a CSV file that has a column named ID with 20 digits in each cell of the column. You can use the following procedure to avoid converting the data into scientific notation while importing the CSV file.
First, create a CSV file with the required data. For demonstration, I have used the following data.
Now, go to the Data tab >> click on From Text/CSV.
Then navigate to your file and select it >> click on Import.
Next, click on the drop-down button >> choose the Do not detect data types option >> click on Load.
That’s it! Your data has been successfully loaded into Excel without any conversion to scientific notation or data loss.
I believe this has addressed your query. If you require additional support, please don’t hesitate to inform us.
Thanks Zahid! your solution worked for me.
You are most welcome.