You ever stared at an Excel file churned out by pandas and sighed? The data is there, but the formatting looks like it was tossed over a fence. Columns cramped tight, sheets all mashed together, and no clear way to highlight what matters. For all the power of pandas, its to_excel() function feels like it’s missing a few key tricks when it comes to making Excel files actually readable and useful.
What if you could write multiple DataFrames to different sheets, have nicely formatted columns, and even get those columns to auto-fit their content — all without diving into Excel yourself? You can. The catch is, pandas doesn’t do this for you out of the box. You have to work a little with Excel engines under the hood and handle the formatting manually. But once you nail that, your Excel exports will go from bland dumps to polished reports.
Here’s how to get there.
How does pandas to_excel() handle multiple sheets and formatting?
By default, pandas to_excel() writes a single DataFrame to one sheet. If you want multiple sheets in the same workbook, you need to use an ExcelWriter object. This object acts like a canvas: you keep adding sheets to it before saving the final file.
You specify which sheet each DataFrame goes to using the sheet_name parameter. This way, you can neatly separate data sets into different tabs, which feels natural for reports or dashboards. For example, a “People” sheet and a “Products” sheet.
But formatting? That’s where it gets interesting. Pandas relies on third-party engines like openpyxl or xlsxwriter to write Excel files. If you want to style cells or set column widths, you need to reach into those engines through the ExcelWriter object after writing your DataFrame.
Here’s a snippet to show this in action:
import pandas as pd
# Create sample DataFrames
df1 = pd.DataFrame({'Name': ['Alice', 'Bob'], 'Age': [25, 30]})
df2 = pd.DataFrame({'Product': ['Apple', 'Banana'], 'Price': [1.2, 0.5]})
# Use ExcelWriter to write multiple sheets
with pd.ExcelWriter('multi_sheets.xlsx', engine='openpyxl') as writer:
df1.to_excel(writer, sheet_name='People', index=False)
df2.to_excel(writer, sheet_name='Products', index=False)
# Access workbook and worksheet objects for formatting
workbook = writer.book
worksheet = writer.sheets['People']
# Example: set column width for 'People' sheet
worksheet.column_dimensions['A'].width = 15
worksheet.column_dimensions['B'].width = 10
# This snippet shows writing multiple DataFrames to separate sheets and applying column width formatting using openpyxl.Notice how after writing the data, we grab the worksheet object from writer.sheets and set column widths. Without this step, Excel would auto-size columns unpredictably or leave them narrow.
What about fitting multiple DataFrames into the same sheet?
It’s common to want to group related data in one sheet but keep them visually separate. Pandas lets you specify startrow and startcol to control where your DataFrame starts writing.
This is great if you want one table at the top, and a second table positioned below or beside it, with some spacing to keep things clear.
Here’s how that looks:
import pandas as pd
# Sample DataFrames
df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'X': ['foo', 'bar'], 'Y': ['baz', 'qux']})
with pd.ExcelWriter('multiple_dfs_one_sheet.xlsx', engine='openpyxl') as writer:
# Write first DataFrame starting at top-left
df1.to_excel(writer, sheet_name='Sheet1', index=False, startrow=0, startcol=0)
# Write second DataFrame below the first with some space
df2.to_excel(writer, sheet_name='Sheet1', index=False, startrow=len(df1) + 3, startcol=0)
# Shows how to position multiple DataFrames in the same sheet using startrow and startcol parameters.This lets you pack your data in a way that feels human-friendly rather than just dumping tables on top of each other.
How to auto-fit Excel columns with pandas to_excel()
One of the most frustrating things about pandas exports is that column widths end up fixed or poorly sized. Excel itself has “auto-fit” columns based on content — but pandas does not expose this directly.
You have to calculate the max width manually, then pass that to the Excel engine. Fortunately, the xlsxwriter engine makes this pretty straightforward.
The trick is to scan through each column in your DataFrame, determine the longest string length — including the header — then set that as the column width with a little padding.
Here’s a practical example:
import pandas as pd
# Sample DataFrame
df = pd.DataFrame({'City': ['New York', 'Los Angeles', 'Chicago'],
'Population': [8419000, 3980000, 2716000]})
with pd.ExcelWriter('auto_fit_columns.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Cities', index=False)
worksheet = writer.sheets['Cities']
# Calculate max width for each column
for i, col in enumerate(df.columns):
max_len = max(df[col].astype(str).map(len).max(), len(col)) + 2
worksheet.set_column(i, i, max_len)
# Demonstrates auto-fitting Excel columns by setting widths based on max content length with xlsxwriter.This simple loop does the heavy lifting. It checks the length of every value under each column (converted to string), compares it to the header length, and picks the longest. Adding 2 characters as buffer usually gives a nice fit without wrapping.
Why does this matter? Because auto-fit columns make reading data effortless. Without it, you end up with cramped views or manually resizing columns every time you open the file.
What’s the exact mechanism to write formatted Excel files with pandas?
Let’s get into the steps I follow whenever I need clean, formatted Excel exports. Call this approach the “Formatted MultiSheet ExcelWriter Pattern.”
- Choose your engine based on features and performance: – openpyxl is the default for .xlsx and supports basic formatting. – xlsxwriter offers richer formatting options and better control over column widths.
- Create an ExcelWriter context with your chosen engine. This manages file saving elegantly.
- Write each DataFrame using to_excel(), specifying sheet_name for multiple tabs or startrow/startcol for positioning on the same sheet.
- After writing, grab the workbook and worksheet objects from the ExcelWriter. Access the sheet you want to format.
- Apply formatting: – Set column widths by either fixed values (openpyxl) or calculated widths (xlsxwriter). – Change cell styles, apply filters, freeze panes as needed through worksheet methods.
- Save and close the writer to write the final Excel file.
This pattern is reliable, repeatable, and keeps your code clean.
What can go wrong with pandas to_excel() formatting?
- Assuming pandas will auto-fit columns natively — it won’t. You must calculate and set widths yourself.
- Mixing engines carelessly. For example, trying to use openpyxl-specific formatting features with xlsxwriter will fail.
- Forgetting to use ExcelWriter context or not closing the writer properly can corrupt your output.
- Large DataFrames can slow down Excel output, especially with formatting. Using engine=’xlsxwriter’ with pre-calculated widths helps mitigate this.
- Overlapping data when writing multiple DataFrames to the same sheet if you don’t carefully set startrow and startcol.
To keep these risks low, test your exports on sample data and automate column width calculations.
It’s tempting to think exporting data is just about dumping CSVs or raw tables. But Excel is a language of its own, and pandas’ to_excel() is a bridge that works best when you understand its limits and how to push it.
As the saying goes, “Simplicity is the ultimate sophistication.” — Leonardo da Vinci
A clear, well-formatted Excel sheet is a small thing that makes a big difference to whoever opens it. It cuts friction and invites trust. So next time you export, don’t just drop data in. Take the few extra steps with ExcelWriter, sheet naming, start positions, and column widths. Your future self — and your colleagues — will thank you.
BrontoWise 
Leave a comment