How to Get Rid of Scientific Notation in Excel

This guide will explain how to get rid of scientific notation in Excel using six easy and simple methods

Excel is a popular tool when dealing with numbers and calculations. Because it has several built-in functions and tools, it makes performing tasks with numbers very easy.

And one feature of Excel is that it automatically stores large and small numbers in scientific notation. So this happens because Excel has a limit of 15 digits. When we input a number that is longer than 15 digits, Excel will automatically convert the value into a scientific notation. 

But, there are times when we need whole numbers instead of scientific notation especially when we perform calculations. Luckily, there are several methods we can use to get rid of scientific notation in Excel. 

Let’s take an example wherein we need to get rid of the scientific notation in Excel. 

Suppose we are performing calculations in Excel. Since most of the values are decimals, some of the values are longer than 15 digits due to the decimal places. 

But, we want to retain the whole value for accuracy in the calculations. So we want to get rid of the scientific notation to use the raw values for calculations. 

Great! Now let’s move on and learn the different methods we can use to get rid of scientific notation in Excel.

Get Rid of Scientific Notation in Excel by Using Number Formats

Firstly, we can utilize number formats to remove the scientific notation in our data set. Generally, Excel has the general format by default applied to numbers. So we can simply change the number format to remove the scientific notation. 

To use this method, follow the steps below:

1. Firstly, select the cell containing the scientific notation. Then, we will go to the Home tab and select the Number Format dropdown menu. Next, click on Number.

Get Rid of Scientific Notation in Excel

 

2. And tada! We successfully got rid of the scientific notation.

Final output

 

3. Alternatively, we can select the cell containing the scientific notation and right-click. Then, select the Number Format.

Going to Number Format

 

4. In the Number Format window, we will select Number. Next, we will change the Decimal places to 0. Lastly, click OK to apply the changes.

Changing the format

 

5. And tada! We have removed the scientific notation.

Final result

 

 

Get Rid of Scientific Notation in Excel by Using the TRIM Function

Secondly, we can use the TRIM function to remove scientific notation. So the TRIM function is used to remove any extra spaces from selected data. In this case, we will use it to remove the scientific notation from our data set.

Let’s learn how to use the TRIM function to remove scientific notation by following the step-by-step process below:

1. Firstly, we will input our formula in another cell where we want to display the results. So simply type in the formula “=TRIM(B3)”. Then, press the Enter key to return the results.

Get Rid of Scientific Notation in Excel

 

2. Secondly, we can easily drag the fill handle to all the other cells we want to apply the formula.

Get Rid of Scientific Notation in Excel

 

3. And tada! We successfully used the TRIM function to remove the scientific notation.

Get Rid of Scientific Notation in Excel

 

 

Get Rid of Scientific Notation in Excel by Using the CONCAT Function

Furthermore, we can utilize another function to remove the scientific notation from our data set. So the CONCAT function concatenates a list or range of selected text strings.

To use this method, follow the steps mentioned below:

1. Firstly, we will input the formula into a cell where we want to display the result. In this case, we will type in the formula “=CONCAT(B4)”. Then, press the Enter key to return the results.

Get Rid of Scientific Notation in Excel

 

2. Secondly, simply drag the fill handle to apply the formula to other cells.

Get Rid of Scientific Notation in Excel

 

3. And tada! We have removed the scientific notation using the CONCAT function.

Get Rid of Scientific Notation in Excel

 

 

Get Rid of Scientific Notation in Excel by Using the UPPER Function

And another function we can use to remove scientific notation is by using the UPPER function. So the UPPER function is commonly used to convert text to uppercase or capital letters. Additionally, we can use this function to get rid of scientific notation. 

Learn the process of how to use this method below:

1. Firstly, we will type the formula into any blank cell to display the results. So simply input the formula “=UPPER(B5)”. Next, press the Enter key to return the results.

Get Rid of Scientific Notation in Excel

 

2. Secondly, drag down the fill handle to copy the formula to other cells.

Get Rid of Scientific Notation in Excel

 

3. And tada! We have successfully used the UPPER function to get rid of scientific notation in Excel.

Get Rid of Scientific Notation in Excel

 

 

Get Rid of Scientific Notation in Excel by Using Text to Column

Additionally, we can utilize the text to column feature in Excel to remove scientific notation. So this method is really simple and easy. 

To use this method, follow the step-by-step process below:

1. Firstly, we need to copy the range containing the scientific notation and paste the data into another range. If you do not need a copy of the scientific notation, you can skip this step and immediately convert the original column.

So select the column and press Ctrl + C to copy. Then, press Ctrl + V to paste the column. Otherwise, we can also right-click and select Paste.

Get Rid of Scientific Notation in Excel

 

2. Secondly, we will select the copied column containing the scientific notation. Then, go to the Data tab and click Text to Columns.

Get Rid of Scientific Notation in Excel

 

3. Once the Convert Text to Columns Wizard window has opened, we will select Fixed width. Lastly, click the Next button.

Get Rid of Scientific Notation in Excel

 

4. Then, we will click the Next button again.

Get Rid of Scientific Notation in Excel

 

5. After, we will select Text under the Column data format. Finally, click Finish to apply the changes.

Get Rid of Scientific Notation in Excel

 

6. And tada! We have successfully removed the scientific notation.

Get Rid of Scientific Notation in Excel

 

 

Get Rid of Scientific Notation in Excel by Using An Apostrophe

Lastly, we can get rid of the scientific notation in Excel by simply adding or typing an apostrophe or a single quote before we type in a number. And this is also the easiest method to use to remove scientific notation. 

Furthermore, Excel may try to give us a warning that this is an error since numbers do not normally begin with an apostrophe. But, we can simply choose to ignore errors and move on. 

To learn how to use this method to remove the scientific notation from our data set, follow the step-by-step process below:

1. Firstly, we simply need to type an apostrophe before inputting a number. For instance, we will type in “‘7890064224568770000“.

Get Rid of Scientific Notation in Excel

 

2. When we enter the number, Excel does not convert it into a scientific notation. Although the value does overflow to other cells due to its length, we can simply adjust the cell length to accommodate the value.

Get Rid of Scientific Notation in Excel

 

You can make your own copy of the spreadsheet above using the link attached below. 

And that’s pretty much it! We have explained six easy and simple ways to get rid of scientific notation in Excel. Now you can use any one of the methods whenever you need to remove a scientific notation in your data set.

Are you interested in learning more about what Excel can do? You can now use the CONCAT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like