How to Remove Scientific Notation in Excel (4 Quick Ways)

In Microsoft Excel, large and small numbers are stored automatically into Scientific Notation by default. Excel has a number limit which is 15 digits. If your number digits are 15+ then, excel automatically converts that to scientific notation as a way of dealing with that limit. You can easily remove scientific notation in Excel by following the simple ways below.


Download Practice Workbook

You can download the workbook and practice with them.


What is Scientific Notation?

A special way of writing very small and very large numbers in a shorthand method is called scientific notation.

What is Scientific Notation?

Read More: How to Enter Scientific Notation in Excel (4 Methods)


4 Quick Ways to Remove Scientific Notation in Excel

When you are working with excel, the formulas will end up giving you values that look kind of strange. Actually, Excel is giving you the number in scientific notation. So, when you have an E (Euler’s Number), you either have a very small number which is a negative exponent (E-) or you have a very large number which would be a positive exponent (E+) number.

Dataset Introduction

In the following example, there are two columns with scientific notation and without scientific notation. We will take the first column numbers and remove the scientific notation. Cell (B5), (B7) contains very large numbers (E+ denotes the large positive numbers) and cell (B6), (B8) contains very small numbers (E- denotes negative small numbers).

Dataset Introduction


1. Remove Scientific Notation Using Cell Formatting

In Excel, the Format Cells just change the aspect of a number without changing the number itself. For numbers, excel uses the General format by default. You can simply change the format by following the steps.

STEPS:

  • First, Copy the first column numbers to the next column. Now, by selecting the numbers Right-click on your mouse.
  • Then, select Format Cells. This will open the format cells dialog box.

Remove Scientific Notation Using Formatting

  • From the Number tab, change the category General to Number.
  • Set the number of Decimal places to 0 for positive numbers. But remember that for the decimals having a noticeable amount of zeros (0) before a non-zero digit you have to increase the decimal places otherwise the numbers will show zero (0).

This picture is for large numbers.

Remove Scientific Notation Using Formatting

The following picture is an example of such cases mentioned above.

Remove Scientific Notation Using Formatting

  • When you are done setting up then click the OK button.

Remove Scientific Notation in Excel

Now you can see the number without any scientific notation.


2. Erase Scientific Notation from Numbers Using Excel Functions

There are many ways to convert Scientific notation to Standard notation. Some functions we are going to use are.

1. Trim Function,  2. Concatenate Function,  3. Upper Function

2.1 Use of TRIM Function

The TRIM function is used to remove extra spaces from data. It is also used to remove the scientific notation from data.

Syntax: TRIM(text)

Here, text can be a text string, a cell reference, or a value.

STEPS: 

  • First, write down the formula in Cell C5:
=TRIM(B5)

Use of TRIM Function

  • Then, drag the fill handle to all the cells you want to use this formula.

Use of TRIM Function

Now you can see your desired results.

Use of TRIM Function


2.2 Use of CONCATENATE Function

The CONCATENATE Function is used to combine values from several cells into one cell. We can use this function to remove the scientific notation.

Syntax: CONCATENATE(text1, [text1],…)

Here, text can be a text string, a cell reference, or a formula operating value.

STEPS:

  • Please enter the formula in Cell C5:
=CONCATENATE(B5)
  • Then, drag the fill handle over to the range you want to remove the scientific notation.

Use of CONCATENATE Function


2.3 Use of UPPER Function

The UPPER Function is used to convert text to uppercase (all capital letters). It also can be used to remove the scientific notation from data.
Syntax: UPPER(text)

Here, text can be a reference to a cell or a text string.

STEPS:

  • The associated formula in Cell C5 will be:
=UPPER(B5)
  • Then, drag the fill handle over to the range you want to apply the formula.

Use of UPPER Function

You can apply any of the functions as you opt to choose.


3. Delete Scientific Notation by Using Text to Column Feature in Excel

The Text to Columns feature is also a great way to remove the scientific notation from your data.

STEPS:

  • First, Copy the data from the scientific notation column and paste the data. Then, select the cells you want to remove scientific notation.

Remove Scientific Notation by Using Text to Column Feature

  • Go to the Data tab in the ribbon. Next click on Text to Columns.

Remove Scientific Notation by Using Text to Column Feature

  • Now, you can see a dialog box. Select Fixed width. Click on the Next button.

  • Then, click Next again in the second step of the wizard.

  • Select General to Text from column data format. Then, Finish.

Remove Scientific Notation by Using Text to Column Feature

  • And, finally, you can see the result.

Read More: 3 Methods to Convert Scientific Notation to Text in Excel


4. Delete Scientific Notation by Adding an Apostrophe at the Beginning

You can also remove the scientific notation by just adding an apostrophe/a single quote (‘) before you enter the number.

Delete Scientific Notation by Adding an Apostrophe at the Beginning

If you find an error showing in the screenshot below, right-click your mouse and select Ignore Error.

Delete Scientific Notation by Adding an Apostrophe at the Beginning

And there you go!

Delete Scientific Notation by Adding an Apostrophe at the Beginning


Things to Keep in Mind

Excel is unable to keep more than 15 digits of the original number, the other digits are changed to zeroes.


Conclusion

By following the steps, you can easily remove the scientific notation in your Excel workbook. All those methods are simple, fast, and reliable. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Readings

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo