In this lesson, you will learn how to disable scientific notation (e.g., 1,23457E+17) in an Excel spreadsheet.

This issue arises when you attempt to paste a long number. When you paste it into the spreadsheet, Excel changes the formatting to scientific notation (e.g. 1,23457E+17).

Excel causes this problem, which has a 15-digit precision limit. It’s annoying, because most of the time, I’d prefer it if Excel just treated the number as text (until I wanted it sorted).

How to format the cell to disable scientific notation?

Just right-click on the cell and choose Format cell.

Change the cell format to Number with a zero number of decimal places.

Now, this problem doesn’t exist any more.

Tips:

• This solution works for other types of formatting as well. For example, Custom with a single 0 or Text.
• The number format does not affect the actual cell value, which is displayed in the formula bar.
• When Excel displays a cell with #### signs, it means that the cell is too narrow. Just make it wider.

Increasing the number of decimal places

One way to disable scientific notation in Excel is by increasing the number of decimal places:

1. In the Format Cells dialog box, select the Number tab.
2. In the Number section, select the number of decimal places you want to display. The default is 2 decimal places, but you can increase this number to show more decimal places if necessary.
3. The number will be displayed in the specified number of decimal places, instead of in scientific notation.

Note: This method works best for numbers that are small enough to be displayed with the specified number of decimal places. If the number is too large, it may still be displayed in scientific notation even if you increase the number of decimal places. In such cases, you may need to use a different method, such as changing the cell format to Text or using the Text formula.

Using the Custom format

Using the Custom format is another way to disable scientific notation in Excel. This method involves specifying a custom format for the cell to display the number in a specific way.

1. In the Format Cells dialog box, select the Number tab.
2. In the Category section, select Custom.
3. In the Type field, enter a custom format for the number. For example, to display the number with no decimal places, enter 0. To display the number with 2 decimal places, enter 0.00.
4. The number will be displayed in the specified format, instead of in scientific notation.

Note: This method can be useful when you need to display a specific number of decimal places or when you want to display the number in a specific way (such as with a comma separator).

Using the Text to Columns Tool

You can also remove scientific notation with the Text to Columns tool. This tool is used to improve the content of cells in case of problems with their content or formatting.

To start, select the column that contains unnecessary scientific notations and go to the Excel ribbon. Click Data > Text to Columns.

In the first step, check the Fixed width option. Already at this stage you can see in the preview of selected data at the bottom of the window that scientific notation will be deleted.

In the third step, select the text from available Excel data types. The scientific notation will disappear from the cells.

The inconvenience is that green triangles will appear in the cells.

Conversion of the cell to disable scientific notation

There is one more tricky way to accomplish the expected output that I would like to share with you.

There is a way to convert the content of the cell to the specific number format without changing it. Excel would stop using the scientific notation as well.

=TEXT(A1,”0″) is the Excel formula you should use.

The result is the same. E+ disappeared from the cell.

Trimming to remove scientific notation

Another way to remove scientific notation from your workbook is to use the trim function.

The Trim function is used to remove excess spaces from a cell. Using the formula Trim only leaves single spaces between words.

Use the formula = TRIM (A1) to get rid of scientific notation.

Related posts:

How to Edit Multiple Excel Sheets at Once How to Create Kpi Dashboard in Excel How to Count Unique Values in Excel