How to Stop Excel from Changing Numbers to Scientific Notation

Large numbers are automatically placed into scientific notation by default in Microsoft Excel. In fact, Excel can handle 11 digits before it converts the numbers to scientific notation. Now, sometimes you may want to display all the numbers instead of the scientific notation. Considering this, the following article describes 7 easy ways how to stop Excel from changing numbers to scientific notation.

How to Stop Excel from Changing Numbers to Scientific Notation: 7 Simple Ways

Here, we’ll utilize the various options and functions that Excel offers to change scientific notation back to numbers. Therefore, without further delay, let’s see each process in detail.
Considering the Sales Data in the B4:D14 cells, where we have the Customer Name, Invoice Number, and the Sales in USD. Here, the Invoice Number consists of 13 digits, which are stored in scientific notation by default, as shown in Column C (see the following image). However, we want to display these as regular numbers and stop Excel from changing the numbers to scientific notation.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

Method-1: Using Format Cells Option

Let’s begin with a quick and simple method, that is to say, we’ll use the Format Cells option to change the Invoice Numbers to regular numbers. So, let’s see it in action.

📌 Steps:

• At the very beginning, make a copy of the Invoice Number column >> select the D5:D14 cells >> press the CTRL + 1 keys.

Now, this opens the Format Cells dialog box.

• Next, go to the Number tab >> change the Decimal places to 0 >> click the OK button.

Finally, the results should look like the image shown below.

Read More: How to Stop Excel from Auto Formatting Numbers

Method-2: Utilizing UPPER Function

If you’re one of those people who enjoy using Excel functions, then our next method has you covered. While the UPPER function typically changes a string of text to upper-case, it can also transform scientific notation into numbers. Hence, follow these simple steps below.

📌 Steps:

• In the first place, move to the D5 cell and enter the formula given below.

Here, the C5 cell indicates the Invoice Number.

• Then, use the Fill Handle tool to copy the formula into the cells below.

Eventually, the output should look like the picture shown below.

Method-3: Applying TRIM Function

Another way to convert the numbers from scientific notation to regular numbers involves using the TRIM function. Normally, the TRIM function primarily removes extra spaces from a string of text, however, it can also convert scientific notation to numbers. Therefore, let us go through the steps.

📌 Steps:

• Initially, navigate to the D5 cell and insert the expression given below.

In this formula, the C5 cell represents the Invoice Number.

Lastly, your results should look like the screenshot given below.

Method-4: Employing CONCATENATE Function

You can also apply the CONCATENATE function to change the scientific notation in the dataset. Though the CONCATENATE function mainly combines two or more strings in a cell, it can stop Excel from changing numbers to scientific notation. So, just follow the steps.

📌 Steps:

• First, jump to the D5 cell and type in the following expression.

Here, the C5 cell points to the Invoice Number.

Consequently, the output should look like the screenshot shown below.

Method-5: Inserting a Leading Apostrophe

For our next method, we’ll insert a leading apostrophe to change from scientific notation to regular numbers. So, let’s see the process in detail.

📌 Steps:

• Initially, copy the Invoice Number from the C5 cell >> paste it into the D5 cell >> insert a leading apostrophe.

• Likewise, repeat the same process for the other cells, and the output should look like the image given below.

Method-6: Adjusting Width of Column

Sometimes Excel displays the numbers in scientific notation because the column width is insufficient and luckily there is a simple fix for this issue. Now, allow me to demonstrate the process in the steps below.
Assuming the List of Employees dataset in the B4:D14 cells. Here, the dataset shows the ID, Names, and Departments of employees respectively. Now, the ID numbers are shown in scientific notation and we’d want to change them to regular numbers.

📌 Steps:

• To begin with, hover the cursor above the column, here it is column B >> Double-click on the mouse.

Lastly, this should generate the results shown in the picture below.

Method-7: Using Text to Columns Wizard

Excel’s built-in Text to Columns wizard is also a handy way to change the scientific notation in your dataset. So, just follow along.

📌 Steps:

• First, select the D5:D14 cells >> move to the Data tab >> click on the Text to Columns button.

Now, this opens the Text to Columns wizard.

• Next, select the Fixed width option >> click on the Next button.

• Third, hit the Next button again to go to the next page.

• Lastly, select the Text option >> press the Finish button.

As a note, Excel may show an error that you can ignore by clicking the Ignore Error option.

Subsequently, the results should look like the screenshot shown below.

Read More: How to Stop Excel from Rounding 16 Digit Numbers 

Things to Remember

As a note, you should keep this in mind when changing a number from scientific notation to a regular number.

• The UPPER, TRIM, and CONCATENATE functions can handle a maximum of 20 digits before they automatically convert the numbers to scientific notation.
• The adjusting the column width method is applicable if the number of digits is 11 or less than 11, which means there is insufficient space to display all the digits.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Download Practice Workbook

You can download the practice workbook from the link below.

Conclusion

I hope this article helps you understand how to stop Excel from changing numbers to scientific notation. If you have any queries, please leave a comment below.

Related Articles

• Why Is Excel Changing My Numbers to Zero? 
• How to Stop Excel from Changing Last Number to 0 
• [Fixed!] Why Is Excel Changing My Numbers? 
  
• How to Stop Excel from Auto Formatting Hyperlinks
• How to Stop Excel from Changing Numbers to Dates
• How to Prevent Cell Format Changes Automatically in Excel
• [Fixed!] Excel Changing Dates to Random Numbers
• How to Stop Autocorrect in Excel for Dates 
• Why Excel Is Changing My Numbers to Decimals 

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel