By default, Microsoft Excel automatically stores big and small integers in Scientific Notation. Excel has a maximum number length of 11 digits. Excel automatically transforms numbers with more than 11 digits to scientific notation to accommodate this restriction. In this article, we are going to show you 6 easy ways to remove scientific notation in excel.
📁 Download Excel File
Learn to Remove Scientific Notation in Excel with These 6 Suitable Approaches
When you work in excel sometimes you may encounter a value like 1.54E+18. As the value is too large it stored the value automatically in scientific notation. Here we will illustrate the ways to remove these scientific notations using the following dataset.
1. Using Format Cells Option
In this method we will use the cell formatting option to remove scientific notation following the steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- First we will go to the Cell Formatting worksheet.
- Then we will copy the values from B6 to B9 and paste them in the without scientific notation column.
- Further select cell C6.
- Then right click on the mouse and a dialog box will open . From the dialog box select the Format Cells option.
- After clicking Format Cells you will get a window like below. In this window select the Number first then change the Decimal places to 0 and click OK.
- After clicking OK we will see that the scientific notation has changed.
- However, remember that for decimals with a significant number of 0’s before a non-zero digit, you must raise the decimal places; otherwise, the values will display 0. So for the value 5.32E-12 we will increase the decimal places to a suitable number and click ok. Then we will get our desired value.
- Now we will follow the previous steps for the other two values and we will obtain our desired result.
2. Using TRIM Function
In this method we will use excel TRIM function. Trim function is normally used to remove extra spaces from a text however it can also remove scientific notation.
⬇️⬇️ STEPS ⬇️⬇️
- First go to the TRIM worksheet.
- Select cell C6.
- Enter the following formula.
- Click the Enter key.
- Then select cell C6 and drag the Fill handle icon from C6 to C9.
3. Utilizing CONCATENATE Function
By using the CONCATENATE function, we can also simply eliminate scientific notation. This function functions identically to the TRIM function and produces the same result. Although the CONCATENATE function is often used to merge two or more texts in a single cell, it may also be used to eliminate scientific notation.
⬇️⬇️ STEPS ⬇️⬇️
- First go to the CONCATENATE worksheet.
- Select cell C6.
- Enter the following formula.
- Then click the Enter key.
- Then select cell C6 and drag the Fill handle icon from C6 to C9.
4. Using UPPER Function
The UPPER function may also be used to eliminate scientific notation. The UPPER function essentially converts the whole text to all capital letters. However, if you use the function to remove the scientific notation, you will get the same result as with the preceding techniques.
⬇️⬇️ STEPS ⬇️⬇️
- First go to the UPPER worksheet.
- Select cell C6.
- Enter the following formula.
- Click the Enter key.
- Then select cell C6 and drag the Fill handle icon from C6 to C9.
5. Using Text to Columns Feature
In the first approach we use the Text to Column option from the toolbar and remove scientific notation in excel by following the steps given bellow.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the Text to Column worksheet and copy the values from with scientific notation and paste them in without the scientific notation column.
- Then from the excel toolbar go to Data and then click Text to Columns.
- After clicking Text to Column you will Convert text to column wizard. Select Fixed width there and click Next.
- Then you get another window like below and click Next.
- Then select Text in the last step and click on Finish.
- And we can see here we have removed the scientific notation. Then apply the same process for other cells and finally we will be able to remove scientific notation from all the cells.
Here finally we can see that we have removed the scientific notation from every number by applying Text to Column option.
6. Adding Apostrophe
By adding apostrophe at the beginning of the number we can also remove the scientific notation. Here we will show you how to perform this operation.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the Apostrophe worksheet.
- Then copy and paste the values from column B to Column C.
- Select cell C6. Double click on the cell and put an apostrophe at the beginning of the number and then click enter.
- You will see that the scientific notations have been removed.
- Repeat the same procedure for all the values and you will see that the scientific notations have been removed.
How to Convert Scientific Notation to Text in Excel
Sometimes it may be necessary to convert the Scientific Notation to Text format to display the number in its entirety. If a number comprises more than 11 digits, one of the basic capabilities of Microsoft Excel converts it to Scientific Notation. In this post, we will examine the easy way available for changing the format to our liking. Here we will use the TEXT function.
⬇️⬇️ STEPS ⬇️⬇️
- First go to the TEXT worksheet.
- Select cell C6.
- Enter the following formula.
- Click the Enter key.
- Then select cell C6 and drag the Fill handle icon from C6 to C9.
📝 Takeaways from This Article
📌 In this article we learned six easy methods to remove notation in excel.
📌 We also learned few excel functions and their use. For example, TRIM, CONCATENATE, UPPER and TEXT.
📌 We also used few options in excel and learned about them.
Conclusion
In this post, we tried to show every method to remove scientific notation in excel. So that you may choose the appropriate approach for you. Please post any queries in the comments section below. Finally, we recommend visiting Excelden to learn more about Excel.