Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Scientific Notation in Excel – A Step-by-Step Guide

Scientific Notation in Excel – A Step-by-Step Guide

(Note: This guide on how to use the scientific notation in Excel is suitable for all Excel versions including Office 365)

Excel is widely used across industries ranging from education and finance to engineering. This is only possible with the help of the various dedicated data formats in Excel. You may already know that numbers are displayed in different styles in different fields. 

For example, in the accounting department, numbers are shown in a certain order along with their respective currencies. While in the science & engineering fields, very large or very small numbers are usually displayed in terms of exponents along with their decimal places.

Related:

  • How to use EDATE in Excel? 11 Easy Examples
  • How to Enable Excel Dark Mode? 2 Easy Methods
  • How to Graph a Function in Excel? 2 Easy Ways


This guide will look at one of the most important data formats in Excel: The Scientific Notation. 

Table Of Contents
  1. How to Use the Scientific Notation Format in Excel? 
  2. How to Convert Scientific Notation Back to Numbers? 
  3. Limits of Scientific Notation in Excel 
  4. Number Precision in Excel
  5. How to Turn Off Automatic Scientific Notation?
  6. Closing Thoughts

How to Use the Scientific Notation Format in Excel? 

Using the scientific notation in Excel is quite straightforward. Just follow these steps:

  1. Select the cells or range of cells where you want to apply the scientific notation. 
Select the range of cells
Select the range of cells
  1. Click on the drop-down menu list from the Number group of the Home tab. 
  1. Locate and click on the Scientific formatting option from the drop-down list. 
Choose the Scientific formatting option from the drop-down in the Number group of the Home tab.
Choose the Scientific formatting option from the drop-down in the Number group of the Home tab.

This will apply the scientific notation to the selected cells. 

Scientific Notation in Excel
Scientific Notation in Excel

In this example, the normal format number 4589762 gets converted to 4.59E+06. This means that the number is equal to 4.59×10^6. 

Please note that Excel will only display two decimal digits by default, and round off the trailing numbers suitably. In the above example, the number 4589762 was rounded off to 4.59E+06. 

If you want to avoid this, just click on the Increase Decimal button right below the Number group drop-down.

This will make Excel display the desired number of decimals before rounding them off. 

So, clicking on the button will display the number 4.589E+06. 

Similarly, you can use the nearby Decrease Decimal button to decrease the number of decimal places in your scientific notations. 

Increase or Decrease decimals in the scientific notation
Increase or Decrease decimals in the scientific notation

Note: Excel applies the positive scientific notation for large numbers and the negative scientific notation for small numbers. 

For example, the scientific notation of 0.0065 will be 6.5E-03. Whereas the scientific notation of 8.1 will be 8.1E+00. 

Did you know that Excel automatically assigns scientific notation to all numbers which are more than 12 digits long?

How to Convert Scientific Notation Back to Numbers? 

After you are done with your calculations, it is advisable to convert your scientific notation numbers back to normal notation. There are two different ways to do this. 

Let us explore them one by one. 

Method 1:

  1. Select the numbers you want to convert to normal formatting. 
  1. Right-click on your selection and choose Format Cells. 
  1. In the Format Cells dialog box, go to the Custom group in the Number tab and type ‘0’ in the blank space. 
Set the Custom type to 0
Set the Custom type to 0
  1. Click OK. 

This will immediately convert all scientific notation numbers to normal numbers. 

Converting scientific notation into normal number formatting
Converting scientific notation into normal number formatting

Also Read:

  • How to Add Leading Zeros in Excel? 4 Easy Methods
  • How to Use Nested VLOOKUP in Excel? A Step-by-Step Guide
  • Excel String Compare – 5 Easy Methods

Method 2: 

  1. Arrange all the necessary numbers in a column, let us say from A2:A10
  1. In the adjacent column, just type TRIM(A2) and drag this formula from A2 to A10. 
Use the TRIM Function to convert scientific notation into normal numbers.
Use the TRIM Function to convert scientific notation into normal numbers.

This will instantly convert all the numbers from scientific notation to normal format. The advantage of this method is that you still get to retain the original scientific notation as it is for future references. 

Limits of Scientific Notation in Excel 

The largest positive number which can be stored in Excel in scientific notation is 9.9X10^307. Similarly, the smallest number which can be written in scientific notation is -9.9X10^-307. 

Number Precision in Excel

While working with the scientific notation in Excel, it is important to keep in mind that Excel’s numbers are precise only up to 15 digits. After that, Excel automatically rounds off the extra digits to zeros. 

How to Turn Off Automatic Scientific Notation?

As we saw earlier, Excel automatically converts numbers with more than 12 digits to the scientific notation. This can also happen if the cell width is too narrow to display the number completely. 

So, how do you stop this from happening?

Unfortunately, Excel does not allow you to switch this off by default. The only way you can prevent this from happening is as follows. 

  1. Select the range of cells where you need to avoid using the scientific notation. 
  1. Right-click on your selection and select Format Cells. 
  1. In the dialog box, go to the Number group in the Number tab and change the decimal places to zero. 

This way you can prevent Excel from converting numbers to automatic scientific notation. 

Suggested Reads:

  • How to Hide and Unhide Columns in Excel? (3 Easy Steps)
  • How to Stop Excel From Rounding? 4 Easy Ways
  • How to Make a Pie Chart in Excel? A Step by Step Guide

Closing Thoughts

The scientific notation in Excel is a seldom covered topic, but very important nonetheless. In this guide, we saw a brief overview of the scientific notation in Excel, while covering all its important aspects. 

If you have any questions, please feel free to ask in the comments section. We are always happy to help. 

If you find this guide helpful and are looking for more guides, please visit our Excel resources center. 

Want to skill up in Excel?

Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Print Envelopes in Microsoft Word

Creating an Album in Photoshop Elements 10

How to Create Pivot Charts in Excel 2013

How to Set Up Customers and Jobs in QuickBooks Pro 2017 Part 1

Automatic Creation of Org Chart Using External Data in Visio 2010 – Part 1

How to Receive Payments in QuickBooks 2019

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)