If you enter a number that has more than 11 digits in a cell in Excel, it will automatically be converted into the scientific notation (as shown below).
While the number in the back end would still remain the same, Excel changes the way it shows you that number.
Sometimes that’s what you want, and sometimes you may want to convert the scientific notation back to regular numbers.
In this short tutorial, I’m going to show you a couple of ways to convert numbers that are shown in the scientific notation back into being shown as regular numbers.
Convert Scientific Notation to Numbers in Excel
Let me first show you a very simple method to change the way numbers are shown to the users in a cell in Excel.
As I mentioned, any number that is more than 11 digits would automatically be converted into scientific notation.
Using the method below, you can force the formatting of the cell to show the entire number instead of converting it into scientific notation.
By Changing the Cell Format
Below I have a data set where I have some large numbers in cells in column a comma and I want to show these numbers as is.
As of now, you can see that these have been converted into scientific notation.
Below are the steps to do this:
- Select the cells that have the numbers in the scientific notation
- Click the Home tab in the ribbon
- In the Number group, click on the dialog box launcher icon (the small tilted arrow icon). This will open the
- In the Format Cells dialog box that opens, click on the Number tab
- In the Category options, select ‘Custom’
- In the Type field, enter 0
- Click Ok
The above steps would force the selected cells to show the whole number instead of converting them into scientific notation.
One important thing you need to remember when working with large numbers in Excel is that Excel would only show you numbers up to 15 digits with precision. This means that in case you have a 17-digit number, Excel will automatically convert the last two digits into 0.
If you’re working with numbers larger than 15 digits and want to show the entire number, you cannot do that by keeping it as a number in Excel. You will have to convert it into text (covered later in this tutorial).
Also read: How to Write Scientific Notation in Excel?
By adjusting the Column Width (If the Number is 11 Digits or Less)
Sometimes, when you’re working with numbers that have 11 or less than 11 digits (which is the length at which Excel should not convert these numbers into scientific notation), there is still a possibility that you may see these numbers be converted into scientific notation.
This can happen if the column width is not wide enough to show the entire number, so Excel tries to adjust the column width by showing that number in the scientific notation.
If you increase the column width enough to show the whole number, you’ll see that it will solve the problem.
Note that this would work only for numbers that are 11 digits or less. Any number that is longer than 11 digits would anyway be shown in the scientific notation, so adjusting the column width won’t help. In such cases, use the method covered before (by changing cell formatting)
Convert Scientific Notation to Text in Excel
In the above section, I showed you how to convert numbers that are being shown in scientific notation back into regular numbers that show all the digits.
As I mentioned, if you’re working with numbers that are more than 15 digits, Excel will automatically convert all the digits after the 15th digit to 0.
In such cases, it would be better to convert these numbers into text. Numbers that have been converted into the text format are not changed and are shown as is.
Let’s see a couple of easy ways to do this.
Adding an Apostrophe at the Beginning
If you’re manually entering numbers with more than 11 digits comma adding an apostrophe at the beginning of these numbers would convert the entire number to text.
Below I have an example where I have large numbers in two columns. The cells in column A are in the number format and the numbers in it are converted into scientific notation, but the numbers in column B have a leading apostrophe, so it’s treated as text and is shown as is.
One thing to remember about this method is that you won’t be able to use these numbers in formulas that specifically need a numeric value (such as the SUM formula). Thankfully, most Excel formulas that deal with numeric values would be able to interpret this as a number.
When you add an apostrophe before a number. Excel would show a green triangle at the top-left of the cell. That’s Excel’s way of letting you know that something may be wrong. You can ignore it, or select the cell, click on the yellow triangle warning icon that shows, and then click on ‘Ignore Error’.
Using Formulas (UPPER, TRIM, TEXT)
If you have some numbers that are being shown in scientific notation, you can use some really easy text formulas to quickly convert them into text so that the entire number is shown.
Below I have some numbers in column a that are currently being shown if scientific notation.
Here’s a formula that could show the entire number:
=TRIM(A2)
Although the TRIM formula is used to remove extra spaces from text, in this case, it takes the number and returns that number as is.
You can also use any of the below formulas:
=UPPER(A2)
Or
=LOWER(A2)
So these are some of the methods you can use to convert numbers that are being shown in scientific notation to show the full numbers.
If the number is 11 digits or less, you can use the first two methods.
If the number is more than 11 digits, you can use any one of the methods to convert scientific notation to text (by adding an apostrophe at the beginning of the number or by using formulas such as TRIM or UPPER/LOWER).
I hope you found this Excel tutorial useful!
Other Excel tutorials you may also like:
- Convert Text to Numbers in Excel
- Convert Date to Text in Excel – Explained with Examples
- How to Convert Serial Numbers to Dates in Excel
- How to Extract the First Word from a Text String in Excel
- How to Display Numbers as Fractions (Write Fractions in Excel)
