Formatting & presenting reports
Number & date formats
Currency, percentages, dates and custom format codes that survive the round trip.
Start with the full guides
The long-form treatment of number & date formats, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toFormat Dates in Excel Cells with PythonFormat dates in Excel cells with openpyxl: write real datetime objects so Excel stores a date serial, then apply yyyy-mm-dd, mmm d yyyy, or dd/mm/yyyy display codes.
- How-toFormat Durations Over 24 Hours in Excel with PythonWrite time spent, machine run time or hours worked to Excel so 26 hours 30 minutes shows as 26:30, not 2:30 or 1. Understand how Excel stores durations, fix pandas timedelta columns that arrive formatted as whole days, use [h]:mm and [mm] formats, show decimal hours, total durations with formulas, and avoid negative-time errors.
- How-toFormat Excel Cells as Currency with PythonFormat Excel cells as currency with openpyxl: set $#,##0.00, apply it across a column, use €/£ symbols, accounting parentheses for negatives, and post-process pandas output.
- How-toFormat Fractions and Scientific Notation in ExcelControl how Excel shows very small, very large and fractional numbers written from Python: fraction formats with up to one, two or three digit denominators or a fixed denominator such as sixteenths, scientific and engineering notation, why long numbers turn into 1.23457E+11 under the General format, and why IDs longer than 15 digits must be written as text.
- How-toFormat Numbers as Percentages in Excel with PythonA percentage format multiplies by 100 on display, so store 0.125 not 12.5 — the format strings, the factor-of-100 trap, negatives in red, and writing percentages from pandas.
- How-toFormat Numbers in Lakhs and Crores in ExcelShow amounts with Indian digit grouping — 12,34,56,789 instead of 123,456,789 — in Excel files generated with Python: a conditional number format that groups by lakh and crore on any computer, a matching format for negative numbers, the ₹ symbol and decimals, the limits of the approach above 100 crore, a Python formatter to check every case, and when to report in lakhs or crores instead.
- How-toFormat Numbers in Thousands and Millions in Excel with PythonShow 1,234,567 as 1,235K or 1.2M in an Excel report without changing the stored value — scaling commas in number formats, units in headers or suffixes, auto-switching K and M with conditional sections, negatives and small values, and totals that seem not to add up.
- How-toFormat ZIP Codes and IDs with Leading Zeros in ExcelWrite ZIP codes, employee numbers and account codes to Excel so 02134 stays 02134. Choose between storing them as text and storing numbers with a zero-padding format, write both ways with pandas, openpyxl and xlsxwriter, handle 5- and 9-digit ZIPs in one column, add prefixes and phone-style patterns, and avoid the lookup mismatches each choice can cause.
- How-toLocalise an Excel Report for Other Languages and LocalesProduce one Excel report for readers in Germany, France and Poland without breaking it: let number formats follow each reader's separators, use the locale-aware short date, pin month names to a language with LCID tags, place currency symbols, translate labels from one table, set right-to-left sheets, and check the result under each locale.
- How-toRead Cell Values as Displayed in Excel with Pythonopenpyxl and pandas return the stored value — 1234.5 — not the text Excel shows, such as (1,235) or 05/03/2026. Get the displayed text two ways — exported by LibreOffice, or formatted in Python by a small function that handles common number and date codes — and check them against each other.
- How-toShow Negative Numbers in Red with Parentheses in ExcelFormat negatives the way finance readers expect — (1,235) in red, aligned with the positives above them — from Python with openpyxl or XlsxWriter. Covers the _) alignment trick, zero as a dash, currency and accounting layouts, reversed colours for costs, the "(0)" trap and parsing parentheses back into numbers.
- How-toShow Units Like kg or Hours with Number FormatsDisplay units such as kg, t, h, °C, m² or pcs next to numbers in an Excel report while the cells stay real numbers that sum, sort and chart: number format codes with quoted unit text, a column-to-unit mapping applied from pandas, kilograms shown as tonnes with the thousands scaling comma, singular and plural with conditions, and the displayed text checked in LibreOffice.
- How-toShow Zeros as Dashes or Hide Them in ExcelMake zero values in a generated Excel report less noisy without changing the data: show them as a dash with the zero section of a number format, hide them in chosen cells or across a whole sheet, keep dashes aligned with the numbers around them, and understand how displayed dashes, hidden zeros, empty strings and true blanks behave in totals, averages and charts.
- How-toWrite Custom Number Format Codes in Excel with PythonThe four-section format language explained: dashes for zero, red parenthesised negatives, thousands scaling with a trailing comma, literal units — all without changing the stored value.
- How-toConvert Currency Strings Like "$1,234.56" and "(500)" to Numbers with pandasAmount columns exported as text — "$1,234.56", "(1,200.00)", "-€50", "1.234,56 EUR", "750.00-" or "100.00 CR" — cannot be summed. Parse every common currency format into a signed number and a separate currency code, report the values that do not parse, and write real numbers back to Excel.
- How-toConvert Percentage Strings Like "12.5%" to Numbers with pandasA rate column that mixes typed text like "12.5%" with real percentage cells that pandas reads as 0.125 cannot be averaged. Convert every form — "12.5%", "12,5 %", "-3%", "25 bp", fractions and bare numbers — to one consistent fraction, flag the ambiguous values, and write them back with a percent format.
- How-toExcel TEXT Function Equivalent in pandasTurn numbers and dates into formatted text — "1,234.50", "12.3%", "00042", "Mar 2026", "(1,234.50)" — for labels, file names and email bodies, the way Excel's TEXT does. Map TEXT format codes to Python format specs and strftime, and fix the rounding difference that makes "1,234.5" come out as "1,234" instead of "1,235".
- How-toMatch Excel Rounding in pandaspandas and Python round halves to the nearest even number, while Excel's ROUND rounds them away from zero — so 0.125 becomes 0.12 in pandas and 0.13 in Excel. Reproduce ROUND, ROUNDUP, ROUNDDOWN and MROUND exactly, and fix totals that differ by a penny.
- How-toConvert Excel Time Values and Durations in PythonExcel stores times and durations as fractions of a day, and pandas returns them as time objects, timedeltas or 1900 dates depending on the cell format. Turn them all into timedeltas, handle shifts that cross midnight, parse durations typed as text and write durations back so Excel shows hours, not "1".
- How-toAdd Variance Arrows and Trend Indicators to a DashboardShow at a glance whether each dashboard figure moved the right way: custom number formats that put a coloured up or down arrow next to a variance without changing the value, separate formats for measures where down is good, a dead band so tiny changes show as flat, a three-period trend indicator built with formulas, and a check of the displayed text with LibreOffice.
- How-toWrite Dates, Times and Durations with XlsxWriterWrite dates, times and durations to Excel correctly with XlsxWriter: why a date written without a format appears as 46297, setting a default date format, handling the timezone error by converting before stripping, durations over 24 hours with [h]:mm, times of day, dates before 1900, and the same settings through pandas' ExcelWriter.
- How-toWrite Dates Without the Time Part from pandas to Excelpandas writes datetime columns with a YYYY-MM-DD HH:MM:SS format, so every date shows 00:00:00. Write real dates that display as dates: convert to date objects, set datetime_format on an xlsxwriter ExcelWriter, or apply a number format per column — and avoid the openpyxl engine option that is silently ignored.
Compare the options
Which tool or approach to pick, and why.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix #### Showing Instead of Values in Excel OutputNumbers and dates written from Python show as ##### in Excel when the column is too narrow for the formatted value, and negative dates and times always show that way in Excel. Set widths from the formatted text, use xlsxwriter's autofit, shrink to fit where space is fixed, and check the result by rendering the sheet.
- FixFix Long ID Numbers Losing Digits or Turning into 1.23E+18 in ExcelAccount numbers, card numbers and 18-digit IDs lose their last digits when written to Excel as numbers — Python writers keep 16 significant digits and Excel keeps 15. Write identifiers as text, read them back as text, and stop the leading zeros and trailing digits from changing.
- FixFix "Number Stored as Text" Warnings in Excel OutputGreen triangles all over a workbook your script wrote mean numbers went in as strings. Convert them to real numbers before writing, keep identifiers as text on purpose, and silence the warning where text is correct.
Follow it through a track
Prefer an ordered route? These tracks cover number & date formats along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Number & date formats is part of Formatting & presenting reports.