Formatting cell data in spreadsheet part VI: scientific notation

Posted by Greten on 12 Oct 2020 under Tools

Scientific notation is a method of writing very large and very small numbers. It is also used in writing quantities to have proper significant figures. Just a quick review, the format for writing scientific notation is c × 10e. The parts of scientific notation are coefficient, exponent, and base. The coefficient c is a real number with absolute value between 1 and 10, including 1 but excluding 10. The exponent e can be a positive or negative integer. The base is always 10.

This entry is part VI of the post series: Formatting cell data in spreadsheet software programs. The features and functions discussed in this entry are based on Calc 7.0.2, Excel 365, and the version of Google Sheets as of October 2020.

Spreadsheet exponent icon: decoration onlySpreadsheet applications do not display scientific notations in the traditional way it is written, e.g., 3.14 × 108. Instead, they use the method similar to ones used in calculators, e.g., 3.14E+008, where E is something of a substitute to represent × 10; the number on the left side of E is the coefficient while on its right is the exponent, although it is not written in superscript. Some calculators use this notation because the primitive LCD cannot display superscript and multiplication sign. I do not know why spreadsheets stick to this convention.

LibreOffice Calc, Google Sheets, and Microsoft Excel have straightforward ways of formatting a number as scientific notation. However, these applications automatically convert some numbers into scientific notation, such as a number with several zeroes. The factors that trigger the automatic conversion to scientific notation and how the number displays in the cell and the formula bar or input line vary among these spreadsheet applications.

Scientific notation in LibreOffice Calc

You can enter and format numbers as scientific notation in LibreOffice Calc.

How to format a number as scientific notation in Calc?

To format a number as scientific notation in Calc:

  1. Select the cell or cells containing the number you wish to format, or select a blank cell or cells in which you intend to encode a number in scientific notation later.
  2. Do any of the following:
    • Press Ctrl+Shift+2
    • On the top menu, select Format » Number Format » Scientific
    • Press Ctrl+1. In the Format Cells dialog box, select Numbers » Category » Scientific and click OK.

The default format for scientific notations in Calc is that the coefficient has two decimal digits, and the exponent has a leading zero if it has only one digit. This format results from its default format code, which you can see in the Format Cells dialog box.

0.00E+00

If you wish to change this default format, select the number(s) in scientific notation and press Ctrl+1, or right-click on it and select Format Cells; these will open the Format Cells dialog box. You can also change the default format as you format a number or a cell for the first time if you choose the last option in step 2 above.

LibreOffice Calc Format Cell dialog box with Scientific selected from among the Category.

If you do not want a leading zero for single-digit exponents, simply remove one of the zeroes on the right side of E; this side should have only one zero. If the exponent requires two or more digits, they are added as needed (you do not need to add hashes). The format code is going to be this:

0.00E+0

If you want to have a number of decimal places other than two in your coefficient, edit the Decimal places field under Options. You can also add or remove zeroes between the decimal point and E in the Format code; these zeroes correspond to the decimal digit.

How to directly enter a number as scientific notation in Calc?

You can directly enter a number as scientific notation. Calc will recognize a number as scientific notation if you encode a number in a blank cell in the following format and press Enter:

[coefficient]E[exponent]

The above method will work provided your coefficient is a real number, and your exponent is an integer. For example, you enter 3.45E3 in a blank cell with no prior format and press enter, Calc will show it as 3.45E+03 following the default format; this number is the same as 3.45 × 103 or 3,450 in its full form. If you enter a non-integer exponent, like say 3.45E0.3, Calc will recognize it as text and align it to the left.

If you enter a coefficient between -1 and 1, -10 or less, or 10 or greater, Calc will accept as scientific notation but it will adjust the exponent such that there is only one digit on the left of decimal point. For example, you enter 34.5E3, Calc will change it to 3.45E+04.

What numbers are automatically displayed as scientific notation in Calc?

Very large and very small numbers would automatically displayed, but not formatted, as scientific notation as seen in the cells, although some weird stuff happens.

  • They would not adhere to the default format even if you didn't change the format code.
  • If you select the cell and open the Formal Cells dialog box, the format as indicated in Category is still Number and not Scientific.
  • They do not adhere to the usual rule of showing scientific notation in cell and a full number in input line.

Calc may or may not automatically display scientific notations based on the following conditions:

  • Calc recognize large numbers with up to five whole number digits and small numbers with up to four decimal digits as regular numbers; if you want to enter them as scientific notation, you have to format them manually.
  • The weird stuff happens on large numbers with six to sixteen whole number digits and small numbers with 5 to 14 decimal digits. These numbers may or may not be displayed as scientific notations based on factors like the cells' width and the number of digits that are zero. Nonetheless, the input line still displays the full form of the number.
  • Very large numbers with more than 16 digits are displayed as scientific notation in both the cell and the input line.
  • Very small numbers with more than 15 decimal digits might be rounded-off into a number with fewer digits in both the cell and the input line. If there are at least 14 zeroes between the decimal point and the first non-zero decimal digit, it is displayed as scientific notation in both the cell and the input line.

Suppose a cell automatically displays a number as scientific notation. In that case, regardless if it's also in scientific notation in the input line or not, its format in the Format Cells is still that of a regular Number. It's a good idea to format it to Scientific manually, so it appears and behaves like your other numbers in scientific notation.

How to edit a number in scientific notation in Calc?

To edit the numerical data a cell formatted as scientific notation, move the cell pointer there and press F2, or double-click on that cell. Edit the full form of the number, and press Enter.

If it's a very large or a very small number that also displays scientific notation in the input line, you may edit only the numbers and the plus or minus signs on both sides of E, but leave E alone. Doing this will automatically format the number as scientific notation, acquiring the default scientific notation format and appearing as Scientific in Format Cells dialog box. You may also decide just to delete the entire number and enter a new number in that cell.

Scientific notation in Google Sheets

You can enter and format numbers as scientific notation in Google Sheets.

How to format a number as scientific notation in Sheets?

To format a number as scientific notation in Sheets:

  1. Select the cell or cells containing the number you wish to format, or select a blank cell or cells in which you intend to encode a number in scientific notation later.
  2. Do any of the following:
    • On the top menu, select Format » Number » Scientific
    • Select More formats icon near the top and select Scientific from the dropdown.

Screen capture of Google Sheets showing the More formats icon.

Like Calc, the coefficient has two decimal digits, and the exponent has a leading zero if it has only one digit in Sheet's default scientific format. This format has the same format code.

0.00E+00

If you wish to alter the default format of scientific notation, select the number you wish to reformat. Then, select Format » Number » More Formats » Custom number formats to open the Custom number format light box

Google Sheets Custom number format light box showing the default format code of scientific notation 0.00E+00

If you do not want a leading zero for single-digit exponents, simply remove one of the zeroes on the right side of E and select Apply; this side should have only one zero. If the exponent requires two or more digits, they are added as needed; you do not need to add hashes. The format code is going to be this:

0.00E+0

If you want to have a number of decimal places other than two in your coefficient, add or remove zeroes between the decimal point and E in the format code, so that number of zeroes is the same as the number of decimal places that you want to display

How to directly enter a number as scientific notation in Sheets?

You can directly enter a number as scientific notation in Sheets in the same way as Calc. Enter a real number coefficient and an integer exponent with E in between them. The same stuff happens if you don't follow the rules of writing scientific notation: the exponent adjusting if the coefficient you entered has no absolute value between 1 and 10, and the number interpreted as text if the exponent is not an integer.

What numbers are automatically formatted as scientific notation in Sheets?

Sheets may or may not automatically display scientific notations based on the following conditions:

  • Sheets recognize large numbers with up to sixteen whole number digits and small numbers with up to ten decimal digits as regular numbers; if you want to enter them as scientific notation, you have to format them manually. The input line always displays the full number even if you format the cell as scientific.

A screen capture of Google Sheets showing the cell pointer on a cell containing a number formatted as scientific notation. The formula bar on top shows the full form of the number.

  • The cells may display large numbers with 17 to 21 digits as full numbers or as scientific notations depending on how many zeroes are there between the decimal point and the smallest non-zero whole number digit. It's likely to display as scientific if it has more zeroes. For some weird reason, if the numbers with the aforementioned whole number digits are displayed as full numbers, the cell align them to the left like a text although you can still use mathematical operations on them.
  • Very large numbers with more than 21 digits are displayed as scientific notation in both the cell and the input line.
  • Very small numbers with more than 11 decimal digits may round-off, display as full number, or display as scientific notation depending on the number of zeroes between the decimal point and the first non-zero digit. If it displays as scientific in the cell, it also becomes scientific in the input line.

Numbers that automatically display as scientific notation due to being very small or very large do not follow the default format even initially and still retain the default Automatic format. To ensure that they behave like scientific notations, format them as Scientific even if they already look like one.

How to edit a number in scientific notation in Sheets?

Editing numbers in scientific notation in Sheets is very similar to Calc; enter F2 or double-click on the cell to start editing. If the number is too large or too small such that its formula bar is also showing scientific notation, edit it by editing the numbers and retaining the E will cause it to format as Scientific for real. Meaning, the number acquires the default scientific notation format and with a check in Scientific in Format » Number.

Scientific notation in Microsoft Excel

You can enter and format numbers as scientific notation in Microsoft Excel.

How to format a number as scientific notation in Excel?

To format a number as scientific notation in Excel:

  1. Select the cell or cells containing the number you wish to format, or select a blank cell or cells in which you intend to encode a number in scientific notation later.
  2. In the ribbon's Number group, open the drop down and select Scientific.

Screen capture of Microsoft Excel ribbon showing the Number group, with the drop down and Scientific options highlighted in red rounded rectangle.

Like Calc and Sheets, the default format for scientific notations in Excel is that the coefficient has two decimal digits, and the exponent has a leading zero if it has only one digit. This format results from its default format code, which you can see in the Format Cells dialog box.

0.00E+00

If you wish to change this default format:

  1. Select the number(s) in scientific notation that you wish to reformat.
  2. Open the Format Cells dialog box. You can do this in two ways:
    • Open the drop down menu in the ribbon's Number group and select More Number Formats at the bottom of the drop down.
    • Right-click on the cell(s) containing the number(s) you want to format. Select Format Cells from the menu that appeared.
  3. When the Format Cells dialog box opens, the Category is set to Scientific. Select Custom.
  4. When you select Custom, the Type field contains the default format code for scientific notation. Edit the format code. For example, remove one of the zeroes on the right side of E if you do not want a leading zero for single-digit exponents.
  5. Select OK.

Microsoft Excel Format Cell dialog box with Custom selected from among the Category. The Type field shows 0.00E+00.

How to directly enter a number as scientific notation in Excel?

You can directly enter a number as scientific notation in Excel in the same way as Calc and Sheets. Enter a real number coefficient and an integer exponent with E in between them. Adjustments to the exponent also happens if the coefficient does not follow the standard of writing scientific notation. If the exponent is not an integer, the system will not recognize the number as scientific notation.

What numbers are automatically formatted as scientific notation in Excel?

Excel may or may not automatically display scientific notations based on the following conditions:

  • Excel recognizes large numbers with up to five whole number digits and small numbers with up to three decimal digits as regular numbers; if you want to enter them as scientific notation, you have to format them manually.
  • Large numbers with six to eleven whole number digits and small numbers with four to nine decimal digits may display as full numbers or as scientific notations based on the cell width; the narrower the cells, the more numbers display as scientific notation. However, the system still recognizes them as General (regular number format), and you still need to format them as Scientific if you want to ensure that they display and behave as scientific notation.
  • Large numbers with twelve to twenty-one whole number digits and small numbers with ten to nineteen decimal digits always display as scientific notation regardless of the width. However, the system still recognizes them as General, the formula bar shows the numbers' full form, and you still need to format them as Scientific if you want to ensure that they display and behave as scientific notation.
  • Very large numbers with more than twenty-one whole number digits and very small numbers with more than nineteen decimal digits always display as scientific notation in both the cells and the formula bar. However, the system still recognizes them as General, and thus, you need to format them as Scientific if you want to ensure that they display and behave as scientific notation.

How to edit a number in scientific notation in Excel?

Editing numbers in scientific notation in Excel is similar to Calc and Sheets, enter F2 or double-click on the cell to start editing the number in its full form in the formula bar. If the number is too large or too small such that its formula bar is also showing scientific notation, edit it by changing the numbers and retaining the E. This edit causes the spreadsheet to format the number as scientific notation, with the drop down in Number group changing to Scientific.

Conclusion

Spreadsheet applications allow users to format numbers as scientific notation, but uses the E notation similar to old calculators. If you're a teacher who is more familiar with the c × 10e format, you can imagine E to be × 10 and the exponent getting pulled-down. Calc, Sheets, and Excel all follow the format code 0.00E+00, where a single-digit exponent has leading zero and the coefficient comes with two decimal places, but you can change it. Very large and very small numbers, such as those with two-digit exponents or more, are usually displayed as scientific notations, but their underlying number format is still that of a regular number.

Last updated on 12 Oct 2020.

Share your thoughts

* Required. Your email will never be displayed in public.

Instructional design and educational technology for effective learning