Excel Scientific Notation (All Things You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Scientific Notation

 


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.

Selecting Scientific option from Number section under Home tab

  • We’ve entered exponents successfully in Excel.
  • As the numbers are very high values the exponents are positive.

Showing scientific notation with positive exponents


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.

Selecting Scientific option from Number section under Home tab

  • We successfully entered scientific notation.
  • As the numbers are very low values the exponents are negatives.

Showing scientific notation with negative exponents


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.

Selecting Format Cells option from Context menu

  • Format Cells dialog box will appear.
  • From the Category: under the Number tab click on Scientific and then click on OK.

Choosing Scientific as a Category from Format Cells window

  • You’ll see scientific notation in your designated cells.

Showing scientific notation in 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.

Manually typing value as scientific notation

Note
  • 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.

Showing manually written scientific notation in a single cell

  • Repeat the same process for the rest of the cells.

Showing manually written scientific notation in all 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.

Selecting Format Cells option from Context menu

  • Format Cells dialog box will appear.
  • From the Category: under the Number tab click on Custom and choose 0 from Type:.
  • Click on OK.

Choosing Custom as a Category and 0 as a Type from Format Cells window

  • The values are converted to numbers format again from scientific format.

Showing converted numbers in designated cells


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.

Inserting TRIM function to convert values from scientific format to numbers format

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.

Selecting Format Cells option from Context 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.

Choosing Number as a Category and putting 0 as Decimal places from Format Cells window

  • The values are once more changed from scientific format to numerical format.

Showing converted numbers in designated cells


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


<< Go Back to Number FormatLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sajid Ahmed
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo