In this article, you’ll learn how to add Excel scientific notation using the scientific format, the Format Cells option and the manual process.
We’ll use the scientific format for both positive and negative exponents.
You’ll also learn how to convert scientific notation back to numbers in Excel using the Format Cells option and the TRIM function.
Lastly, you’ll find how to turn off automatic scientific notation in Excel.
When dealing with extremely large or extremely small numbers, where the conventional number format becomes ineffective, scientific notation is especially helpful.
For engineering calculations, you can do mathematical operations on exponents numbers in Excel, which enables you to calculate quickly and reduce mistakes.
Below you’ll see an overview image of adding scientific notation in Excel.
Download Practice Workbook
You can download the Excel file from here for practice.
How to Add Scientific Notation in Excel
Method 1: Using Scientific Format
1.1 For Positive Exponent
- Select the cell range D5:D10.
- Click on the DropDown box from the Number section under the Home tab and choose Scientific.
- We’ve entered exponents successfully in Excel.
- As the numbers are very high values the exponents are positive.
1.2 For Negative Exponent
- Select the cell range C5:C7.
- Select Scientific by clicking the DropDown box in the Number section of the Home tab.
- We successfully entered scientific notation.
- As the numbers are very low values the exponents are negatives.
Method 2: Using Format Cells Option
In this part, we’ll use the Format Cells option to enter scientific notation in Excel.
- Select the cell range D5:D10 and give right-click to bring up the Context menu.
- Click on Format Cells… from the menu.
- Format Cells dialog box will appear.
- From the Category: under the Number tab click on Scientific and then click on OK.
- You’ll see scientific notation in your designated cells.
Method 3: Manually Typing Scientific Notation
- There are 10 digits in each Revenue value.
- So, type 2.847379794e9 in cell D5 and press Enter.
- The value 2847379794 from cell D5 can be written as, 2.847379794e9 or 28.47379794e8.
- The e is not case sensitive, meaning e or E will provide the same result.
- You’ll see the value as a scientific notation.
- Repeat the same process for the rest of the cells.
How to Convert Scientific Notation Back to Numbers in Excel
In this section, we’ll convert scientific notation to numbers in Excel using the Format Cells option and TRIM function.
Method 1: Using Format Cells Option
- Give right-click after selecting the range D5:D10 to see the Context menu.
- In the menu, select the Format Cells… option.
- Format Cells dialog box will appear.
- From the Category: under the Number tab click on Custom and choose 0 from Type:.
- Click on OK.
- The values are converted to numbers format again from scientific format.
Method 2: Using TRIM Function
- Type the following formula in cell E6 to convert the value from scientific format to numbers format-
=TRIM(D6)
- Apply the Fill Handle tool for the rest of the values.
Read More: Prevent Excel from Converting to Scientific Notation in CSV Data
How to Turn Off Automatic Scientific Notation in Excel
- Excel automatically assists in converting numbers with more than 12 digits to scientific notation. Additionally, it appears when the cell width is not sufficiently large to make the number fully visible. Excel does not by default offer a way to deactivate this option.
- So, to turn off the automatic scientific notation, select the cell range D5:D10 and give right-click to bring up the Context menu.
- Click on Format Cells… from the menu.
- Format Cells dialog box will appear.
- From the Category: under the Number tab click on Number and type 0 in the Decimal places: box.
- Click on OK.
- The values are once more changed from scientific format to numerical format.
Things to Remember
- Before learning how to use this topic in Excel, we must first understand how it functions in Mathematics.
- Excel automatically converts large and small numbers with 12-digit values or more to scientific notation.
- The largest and smallest number you can add in Excel using scientific notation is 9 x 10 ^ 307 and -9.9 x 10 ^ -307.
- All Excel numbers can only be defined up to 15 digits. Numbers greater than 15 digits are rounded to zeros by default.
- For large numbers, Excel uses positive scientific notation, whereas, for small numbers, it uses negative scientific notation.
Frequently Asked Questions
1. How do I get rid of E+ in Excel?
To get rid of E+ in Excel, select the Number category, set the number of decimal places at 0, and click OK in the Format Cells window.
2. How do I use x10 in Excel?
To use this symbol in Excel, click on a blank cell and type the = sign. Enter your base number, the exponent character, and then the exponent after that.
3. How do I change E+ to text in Excel?
You can change E+ to text by using the Format Cells option and choosing Text format from there.
Conclusion
Finally, it should be noted that working with large or small numbers in engineering calculations requires a solid understanding of how to use scientific notation in Excel. In this article, we have shown various techniques for adding scientific notation, converting it to conventional numbers, and turning off automated scientific notation in Excel. If you want to learn more about this topic, you can also visit the links provided in the Knowledge Hub section of this article.
Excel Scientific Notation: Knowledge Hub
- Set Scientific Notation to Powers of 3
- Convert Scientific Notation to Text
- Remove Scientific Notation
- Display Power in Excel
- Apply Engineering Number Format
- How to Convert Scientific Notation to Text in Excel
- Excel Scientific Notation without e
<< Go Back to Number Format |Â Learn Excel