Format Fractions and Scientific Notation in Excel

Most report numbers are money or counts, and #,##0.00 covers them. Two families of numbers need something else. Measurements in trades and engineering are often read as fractions — a 2⅜-inch bolt, a ⅝ drill — and showing them as 2.375 or 0.625 makes people convert in their heads. Scientific values — concentrations of 0.0000123, or Avogadro's 6.02 × 10²³ — become unreadable strings of zeros in a fixed decimal format. Excel has number formats for both, and openpyxl can apply them from Python. The same topic explains a common complaint about generated workbooks: product codes and account numbers that appear as 1.23457E+11, or that silently lose their last digits. This page covers fraction formats, scientific and engineering notation, and the General format's behaviour with long numbers, with every display checked through a LibreOffice export. It belongs to Applying Number and Date Formats in Excel.

Prerequisites

pip install openpyxl

LibreOffice (soffice) recalculates the workbook and exports each cell as displayed, so formats can be checked without Excel. A small helper does that for every example:

import csv, subprocess
from openpyxl import Workbook

AS_SHOWN = "csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true"

def shown(values, formats, name="demo"):
    wb = Workbook()
    ws = wb.active
    for v in values:
        ws.append([v] * len(formats))
        for col, fmt in enumerate(formats, start=1):
            ws.cell(ws.max_row, col).number_format = fmt
    wb.save(f"{name}.xlsx")
    subprocess.run(["soffice", "--headless", "--convert-to", AS_SHOWN, "--outdir", "calc", f"{name}.xlsx"], check=True, capture_output=True)
    with open(f"calc/{name}.csv", encoding="utf-8") as f:
        return list(csv.reader(f))

Step 1 — Fractions

A fraction format has an optional whole-number part, then a numerator and denominator made of ? placeholders or a fixed number:

FRACTIONS = ["# ?/?", "# ??/??", "# ???/???", "# ?/8", "# ?/16", "?/?"]
values = [0.5, 2.375, 0.3333333333, 3.14159265, 0.625, 1.1]
rows = shown(values, FRACTIONS, "fractions")
print(f"{'value':>12}  " + "  ".join(f"{f:>10}" for f in FRACTIONS))
for v, row in zip(values, rows):
    print(f"{v:>12}  " + "  ".join(f"{c.strip():>10}" for c in row))

# ?/? shows the whole number and the closest fraction with a one-digit denominator: 2.375 is exactly 2 3/8, and π comes out as 3 1/7. More question marks allow larger denominators and closer approximations — π is 3 14/99 with up to two digits and 3 16/113 — the classic 355/113 — with up to three, accurate to six decimal places. A fixed denominator such as # ?/8 or # ?/16 rounds to the nearest eighth or sixteenth and does not reduce: 0.625 shows as 10/16, not 5/8, which is exactly what a workshop drawing in sixteenths wants. Leaving out the # gives improper fractions: 2.375 becomes 19/8. Whatever is displayed, the cell still holds the exact decimal, so totals are unaffected by the approximation. That cuts both ways: 1.1 shows as 1 1/9 under # ?/?, because 1/10 needs a two-digit denominator and 1/9 is the closest one-digit fraction, so a reader adding up displayed fractions by hand can get a different total from Excel. Choose the denominator size from how precisely the values are known — sixteenths for imperial lengths, two digits for most ratios — rather than defaulting to one digit.

There is also an input-side trap. Typing 1/2 into a General cell in Excel enters the date 1 February (or 2 January, by locale), not a half; people enter fractions as 0 1/2 to avoid it. Workbooks generated from Python do not have that problem, because the code writes the number 0.5 and the format does the rest — but if the workbook is a template people type into, pre-format the input cells with the fraction format so typed fractions are read as numbers.

Fractions read awkwardly in proportional fonts because the numerators and denominators do not line up; the ? placeholders pad with spaces to help, which works best in a column with a fixed-width font or right alignment. Adding a unit as a literal — # ?/16" for inches — keeps the number numeric while showing the unit, as covered for other units in Write Custom Number Format Codes in Excel with Python.

Step 2 — Scientific and engineering notation

SCI = ["0.00E+00", "0.0E+0", "##0.0E+0", "0.000000"]
values = [0.000012345, 0.0042, 6.02214076e23, 1500, 123456789]
rows = shown(values, SCI, "scientific")
print(f"{'value':>14}  " + "  ".join(f"{f:>10}" for f in SCI))
for v, row in zip(values, rows):
    print(f"{v:>14}  " + "  ".join(f"{c:>10}" for c in row))

0.00E+00 is standard scientific notation with three significant figures and a two-digit exponent: 0.000012345 shows as 1.23E-05. Putting ##0 before the decimal point turns it into engineering notation, where the exponent is always a multiple of three, matching the metric prefixes: 12.3E-6 (micro), 4.2E-3 (milli), 602.2E+21. Engineers reading values in µA or kΩ find that much quicker to interpret. A fixed decimal format, by contrast, shows 0.000012 — losing precision — and turns 6.02 × 10²³ into a 24-digit string followed by six meaningless zeros.

The number of digits after the decimal point in the format sets the precision shown, not the precision stored. 0.0E+0 shows 1500 as 1.5E+3 and 123,456,789 as 1.2E+8; the second is a loss of information a reader cannot see. Pick enough digits for the precision the measurement actually has — three significant figures (0.00E+00) is a common default for laboratory results — and keep the full value in the cell for anyone who needs it. pandas has a parallel choice when writing: float_format rounds the stored values, while a number format only changes the display, as compared in Round Numbers for Excel Output: float_format vs Number Formats.

Step 3 — Why codes turn into 1.23457E+11

Excel's General format — the default for any cell without a format — shows up to 11 digits; a number with more digits than that is displayed in scientific notation, so the account number 123456789012 appears as 1.23457E+11 in a normal-width column. LibreOffice is more generous in its CSV export, but the second problem is shared by both:

codes = [123456789012, 1234567890123456789]
rows = shown(codes, ["General", "0", "@"], "codes")
for v, row in zip(codes, rows):
    print(f"{v}  ->  {row}")

Formatting the 12-digit code with 0 shows every digit. The 19-digit code cannot be rescued by any format — not even the text format @, which applied to a cell that already holds a number leaves it a number, as the export shows: spreadsheet numbers are double-precision floating point with about 15 significant digits, so 1234567890123456789 is stored as 1234567890123460000 and the last digits are gone before formatting is considered. Identifiers — card numbers, IBAN-like codes, long order references, barcodes — are not quantities and should be written as text. From pandas, keep such columns as strings (dtype=str when reading, astype(str) before writing) and they arrive in Excel as text with every digit intact, as covered in Format ZIP Codes and IDs with Leading Zeros.

Reading the values back

None of these formats changes what Python reads. pd.read_excel returns 2.375 for a cell showing 2 3/8 and 1.2345e-05 for one showing 12.3E-6, because it reads the stored value and ignores the display. openpyxl does the same, and exposes the format separately as cell.number_format, so a test can check both: that the value is right and that the format is the one intended. If you need the text exactly as a reader sees it — for a comparison against a printed report, say — the LibreOffice export used on this page is the reliable way, as described in Read Cell Values as Displayed in Excel.

Common pitfalls

• Expecting fixed denominators to reduce. # ?/16 shows 10/16, not 5/8 — by design.
• Approximate fractions in totals. The display rounds; the value does not.
• Fixed decimals for tiny values. Use scientific or engineering notation instead.
• Identifiers as numbers. Long codes show as E+11 or lose digits; write them as text.
• Trusting one application's display. Check formats where readers will open the file.

Conclusion

Use # ?/?, # ??/?? or # ???/??? to show measurements as the nearest fraction with up to one, two or three digit denominators, and # ?/8 or # ?/16 for a fixed denominator. Use 0.00E+00 for scientific notation and ##0.0E+0 for engineering notation with exponents in multiples of three. Format long quantities with 0 so they do not collapse to E+11 under General, and write identifiers as text, because numbers keep only about 15 significant digits.

Frequently asked questions

How do I show a number as a fraction in Excel with Python? Set the cell's number_format to a fraction format such as # ?/? for the nearest one-digit fraction or # ?/16 for sixteenths.

How do I format numbers in scientific notation with openpyxl? Use a format such as 0.00E+00; for engineering notation with exponents in multiples of three use ##0.0E+0.

Why does Excel show my number as 1.23457E+11? The General format displays numbers with more than 11 digits in scientific notation; apply the format 0 or #,##0, or write identifiers as text.

Why do long numbers lose their last digits in Excel? Excel stores numbers with about 15 significant digits, so longer numbers are rounded; store identifiers such as card or order numbers as text.

Related

• Up one level: Applying Number and Date Formats in Excel — number formats from Python.
• Format Numbers in Lakhs and Crores in Excel — digit grouping.
• Round Numbers for Excel Output: float_format vs Number Formats — rounding versus display.
• Parse Numbers with Units in Excel Cells — the reverse problem.