Microsoft designed Excel to work well with large numbers, but there are times when things can go awry. For example, when folks enter numbers that are more than 11 digits, they are converted right away to scientific notation.
We suspect many new users of Excel will find this puzzling, especially if they’ve heard a lot of good things about the platform, only to find out it is having issues with numbers. Now, is it possible to get the regular numbers back? The answer to that is a resounding yes.
How to remove Scientific Notation in Excel
You can prevent Excel from converting numbers to scientific notation. To remove scientific notation in Excel follow these methods:
- Use the Format Cells option
- Use the Formatting option
- Make use of the TRIM() function
- Use the CONCATENATE function
1] Use the Format Cells option
We should point out that the cells in Microsoft Excel are set to the General format. This tends to convert numbers automatically to scientific notations if they are longer than 11 digits.
The best way to set things right here is to change from General to the Number format, so let us look at how to get this done.
Click the range of the cells that you wish to remove the scientific notation.
From there, click on the Home tab, then select Format.
Right away a dialog box will make an appearance.
Choose the Number option via the Category area.
Change the Decimal Places value to 0.
You can leave the Decimal Places area as is if you want to showcase decimals in numbers.
When you’re done, hit the OK button, and that’s it.
READ: How to export InDesign file to Excel
2] Use the Formatting option
One of the best ways to set things back to normal, I to use the Formatting option that is located on the Ribbon.
First, you must highlight the range where you want the scientific notation removed.
After that, click on Home, then navigate to the Number Format section.
If you see a box that says General, please click it to reveal a drop-down menu.
From within the list, look for a Number and click on it.
When the above tasks are done correctly, the scientific notation will be converted to Number Format.
READ: Excel not auto calculating formulas
3] Make use of the TRIM() function
When you want to remove extra spaces from text in a cell, the TRIM() function is your best friend. However, you can also use it to convert numbers from scientific notation back to regular notation.
Let us explain how to apply the TRIM function correctly.
All you have to do here, for example, is type =TRIM, then click on the Enter key.
Drag the formula to all cells, and watch as the standard output is shown rather than scientific notation.
READ: How to use Copilot in Excel
4] Use the CONCATENATE function
We use the CONCATENATE function to join different text values, but it is also possible to use it to remove scientific notation. Now, this function works the same way as TRIM and will deliver the same output.
To use the CONCATENATE function, write the formula in the C5 cell, for example:
=CONCATENATE(B5)
Press the Enter key on your keyboard, then take advantage of the Fill Handle to get the required outputs that will remove scientific notation.
READ: Excel Auto Recover not working
Why is Excel showing scientific notation?
Spreadsheet programs such as Excel or even Google Sheets are limited in the number of digits they can handle. Where Excel is concerned, 15 digits is the largest it can handle, so if values go over the limit, they will then show in the form of scientific notation.
Read: How to remove Text before or after Specific Character in Excel
How do I get rid of E+ in Excel?
Excel does not give users the ability to turn off this functionality by default. However, if you choose your data, then right-click, then select Format Cells. From there, choose the Number you wish for Excel to stop from changing into scientific notation, and that’s it.
Leave a Reply