To format an Excel file created with pandas, write the data with DataFrame.to_excel(), then apply styles with pandas Styler or with the workbook engine, such as XlsxWriter or openpyxl. For example, df.to_excel('report.xlsx', index=False) exports the data; it does not, by itself, create polished fonts, number formats, column widths, or filters.
What pandas does when it exports Excel
Pandas converts a DataFrame into worksheet cells. The basic export is straightforward:
df.to_excel('report.xlsx', sheet_name='Sales', index=False)
The index=False argument omits the DataFrame index, which is often desirable for a report. Leave it out if the index contains meaningful labels you want in the workbook. You can also choose the sheet name, starting cell, whether to write column headings, and other export options through to_excel().
It helps to separate two ideas that are easy to confuse: the value in a cell and how Excel displays that value. A revenue value can remain numeric while a currency number format displays it as dollars with two decimal places. That preserves its usefulness for sorting, calculations, and charts. By contrast, converting values to strings to insert dollar signs can make later calculations less convenient.
Pandas supports Excel export through an engine. For modern .xlsx files, commonly used choices are openpyxl and XlsxWriter. They are separate Python packages, so the package you choose must be installed in the environment running the script. Setting the engine explicitly makes the intended behavior clear:
with pd.ExcelWriter('report.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sales', index=False)
Engine availability and defaults can vary with your pandas installation and configuration. If code depends on a particular engine feature, specify that engine rather than relying on automatic selection.
Choose a formatting approach
The best method depends on whether you need quick visual styling, detailed control over a new workbook, or edits to a workbook that already exists.
- Pandas Styler: useful for styling a DataFrame with familiar pandas operations, such as formatting values or applying a color scale, and exporting the result.
- XlsxWriter: useful for generating a new workbook with explicit Excel formats, worksheet layout, charts, and conditional formatting.
- openpyxl: useful for applying styles to an existing
.xlsxworkbook or modifying a workbook after pandas has written it.
These approaches are not interchangeable in every situation. XlsxWriter creates workbooks but does not read or edit an existing workbook. openpyxl can load and modify many existing .xlsx files, but it does not provide every feature available in XlsxWriter. Styler offers a concise data-oriented interface, while engine APIs offer more direct control of workbook features.
Use pandas Styler for DataFrame-based styles
Styler is a good starting point when the desired appearance follows values or columns in a DataFrame. For instance, it can display revenue as currency and apply a gradient to a rate column:
styled = df.style.format(
{'Revenue': '${:,.2f}', 'Rate': '{:.1%}'}
).background_gradient(subset=['Rate'], cmap='RdYlGn')
styled.to_excel('styled_report.xlsx', sheet_name='Sales', index=False)
Here, the format strings describe presentation, and the gradient assigns cell colors according to the rate values. The names in the dictionaries and the subset must match actual DataFrame columns. This is convenient for a report whose formatting is closely tied to its data.
Excel export supports a subset of the styling available through Styler’s HTML display. A CSS effect that looks right in a browser is not necessarily supported or represented identically in an Excel workbook. Check the pandas documentation for the Styler export behavior for your installed version, especially if the design relies on complex CSS, unusual borders, or precise layout. Styler also does not replace an Excel engine when you need extensive worksheet-level controls such as column widths, panes, or a custom workbook structure.
Format a new workbook with XlsxWriter
XlsxWriter lets you create reusable Excel formats and apply them to worksheet ranges. This example assumes df has columns named Product and Revenue and contains at least one row:
with pd.ExcelWriter('sales_report.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sales', index=False)
workbook = writer.book
worksheet = writer.sheets['Sales']
money_fmt = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.set_column('A:A', 22)
worksheet.set_column('B:B', 14, money_fmt)
worksheet.freeze_panes(1, 0)
worksheet.autofilter(0, 0, len(df), len(df.columns) - 1)
The first row is the header, so freezing at row 1 keeps it visible while scrolling. The column-width settings give the first and second columns different widths; the second also receives a numeric display format. The autofilter range starts at the header row and ends at the final data row. If your DataFrame has a different number or order of columns, adjust the range and formats to match it. For an empty DataFrame, handle the filter range separately rather than assuming there is a data row.
XlsxWriter also provides methods for adding formatted headers, charts, and conditional formatting. One important detail is that pandas writes a header by default, and that header may not use a custom XlsxWriter format automatically. A common solution is to export with header=False, start the data one row below the top, and write the header yourself with XlsxWriter’s worksheet methods. This gives direct control over header font, fill, alignment, and borders.
Style an exported workbook with openpyxl
When pandas has already written the workbook, openpyxl can reopen it and apply cell-level styles. Save the file before loading it, and save the workbook again after making changes:
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
path = 'sales_report.xlsx'
df.to_excel(path, sheet_name='Sales', index=False)
workbook = load_workbook(path)
worksheet = workbook['Sales']
for cell in worksheet[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(fill_type='solid', fgColor='1F4E78')
for row in worksheet.iter_rows(min_row=2, min_col=2, max_col=2):
row[0].number_format = '$#,##0.00'
worksheet.column_dimensions['A'].width = 22
worksheet.column_dimensions['B'].width = 14
worksheet.freeze_panes = 'A2'
worksheet.auto_filter.ref = worksheet.dimensions
workbook.save(path)
This example assumes the first column is a label and the second contains numeric revenue values. The header styling is applied to row 1, and the number format is applied to cells in column B from row 2 down. Change the column positions and number format to suit the data. For example, dates need a date display format, while percentages generally need a format such as 0.0%.
openpyxl style objects are shared and treated as immutable after assignment in many styling workflows. Reuse a style object for many cells rather than repeatedly building large numbers of unique styles. Also, loading and saving a workbook can affect features openpyxl does not fully preserve; check files with advanced Excel features after editing, and retain an untouched copy when the workbook matters.
Format numbers, widths, and worksheet layout
Number formats are among the most useful Excel styles because they change how values appear without turning numeric cells into text. Typical formats include currency, percentages, dates, and thousands separators. Choose a format that matches the underlying data: a rate stored as 0.25 should usually be displayed as 25%, not 0.25%. The displayed result depends on both the stored value and the format.
Column widths and alignment matter just as much as colors. A long description may need a wider column, while dates and short numeric values can use less space. For readability, consider wrapping long text, aligning headings consistently, and freezing the header row on long worksheets. Use XlsxWriter’s worksheet methods or openpyxl’s row and column dimensions for these layout changes; a DataFrame’s values alone do not determine a polished worksheet layout.
For a report with several sheets, create one worksheet per logical subject and use clear names. Apply formats by column meaning, not just by position, so a later column reorder does not silently put a currency format on a date field. In reusable scripts, check that expected columns exist before applying formatting. This turns a quiet presentation error into a clear, fixable problem.
Add conditional formatting carefully
Conditional formatting lets Excel change a cell’s appearance based on its value, such as highlighting low inventory or showing a color scale. XlsxWriter and openpyxl expose conditional-formatting features, but their APIs differ. Styler can also export some data-driven color styling. Pick one method that fits the workbook workflow, and verify the result in Excel rather than assuming an on-screen DataFrame preview guarantees identical Excel behavior.
Use conditional formatting when readers benefit from seeing exceptions at a glance, not merely to decorate every column. For example, a warning color may help identify values below a meaningful threshold, while a gradient can make relative patterns easier to scan. State the units and interpretation in a heading or note. A red cell should have a clear meaning; color alone can be ambiguous and may not be accessible to every reader.
Common problems and practical checks
- The workbook has no formatting:
DataFrame.to_excel()writes data, but does not automatically style a report. Use Styler or the selected engine’s formatting methods. - Numbers behave like text: avoid adding symbols to values by converting them to strings when you need Excel arithmetic. Keep numeric values numeric and apply a number format.
- Styles disappear after export: writing the DataFrame again creates or replaces worksheet content and may not preserve styles previously applied to that sheet. Apply styles after the final data write, or use a workflow designed to edit the existing workbook.
- The wrong cells are formatted: confirm whether your worksheet includes an index, where the header starts, and which Excel column corresponds to each DataFrame field.
- An existing workbook cannot be opened with the chosen engine: XlsxWriter is for creating workbooks, not loading an existing one. Use openpyxl when you need to read and modify a supported
.xlsxfile.
For reliable output, test the script on a small sample, open the resulting workbook in Excel, and check representative cells, filters, dates, and displayed numbers. If a recipient depends on a particular appearance, inspect the actual workbook rather than only a notebook preview. Keep a separate source DataFrame or original workbook so presentation changes do not become the only copy of the data.
Frequently Asked Questions
Can pandas format an Excel file without openpyxl or XlsxWriter?
Pandas needs an Excel-writing engine for .xlsx output. Styler provides a pandas interface for styles, but export still uses an engine such as openpyxl or XlsxWriter. Install the required package and set the engine explicitly when the choice matters.
Does to_excel() preserve formatting in an existing workbook?
Do not assume it will. A normal export writes worksheet data and can replace existing content. To modify a workbook, use a workflow that loads the existing file, such as openpyxl, and check the result for features that may not be preserved.
How do I format a column as currency?
Keep the cells numeric and assign an Excel number format to the column or its data cells. XlsxWriter uses a format created with workbook.add_format(); openpyxl uses a cell’s number_format property. Use a format appropriate to your currency and reporting requirements.
Can I change column widths with pandas?
Column widths are worksheet layout settings rather than DataFrame formatting. Set them through XlsxWriter’s worksheet.set_column() or openpyxl’s column dimensions after identifying the correct Excel columns.
Which is better for Excel formatting, openpyxl or XlsxWriter?
Use XlsxWriter when creating a new workbook and you want its worksheet-generation features. Use openpyxl when you need to load and edit an existing supported .xlsx workbook. For styles expressed directly in terms of DataFrame values, Styler may be the simplest starting point.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Add a Cover Page in Microsoft Word 365To add a cover page in Word 365 for Windows, open the document and choose Insert > Pages…
- Excel Dropdown List Conditional Formatting: How It WorksTo apply conditional formatting to an Excel dropdown, first create the dropdown with Data Validation, then select the…
- Data Bars in Google Sheets: Built-In Feature or Workaround?Google Sheets does not currently offer Excel-style data bars as a built-in option under conditional formatting. To show…
- Add Apps Script to Google Sheets: A Practical GuideTo add Apps Script to a Google Sheet, open the spreadsheet in a web browser and choose Extensions…
- Excel Remove Duplicates Shortcut Key: What to PressIn Excel for Microsoft 365 on Windows, press Alt, then A, then M to open Remove Duplicates. These…
- Google Sheets Filter Shortcut: Turn Filters On and OffIn Google Sheets in a web browser, press Ctrl+Shift+L on Windows or ChromeOS, or ⌘+Shift+L on a Mac,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.