How to save a dataframe to Excel in Python

To save a pandas DataFrame to Excel is a common task for data analysis. Python's pandas library simplifies this with functions like to_excel(), which automates the export process.

In this guide, you'll explore several methods to save your data. You'll find practical examples, real-world applications, and advice on how to debug common issues effectively.

Basic to_excel() method

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({'Name': ['John', 'Alice'], 'Age': [25, 30]})
df.to_excel('output.xlsx', index=False)--OUTPUT--# No visible output, but 'output.xlsx' file is created

The to_excel() method is called directly on the DataFrame you want to export. The first argument, 'output.xlsx', specifies the name and path for the new Excel file. This simple command handles the entire file creation and data writing process for you.

You'll notice the index=False parameter. By default, pandas writes the DataFrame's index (the row numbers) into the first column of the spreadsheet. Setting this parameter to False prevents that, giving you a cleaner output that contains only the data from your DataFrame's columns.

Essential Excel export techniques

Going beyond a simple file save, the to_excel() method offers powerful parameters for organizing your data across multiple sheets and improving readability.

Using the sheet_name parameter

import pandas as pd

df = pd.DataFrame({'Product': ['Laptop', 'Phone'], 'Price': [1200, 800]})
df.to_excel('products.xlsx', sheet_name='Electronics', index=False)--OUTPUT--# File 'products.xlsx' created with sheet named 'Electronics'

The sheet_name parameter lets you name the worksheet inside your Excel file. If you don't specify one, pandas defaults to 'Sheet1'. Assigning a custom name is a great way to keep your workbook organized, especially when dealing with multiple datasets.

• In this case, sheet_name='Electronics' saves the data to a worksheet named 'Electronics'. This makes the file's contents immediately clear without needing to open it and guess what's inside.

Saving multiple DataFrames to separate sheets

import pandas as pd

df1 = pd.DataFrame({'Product': ['A', 'B'], 'Sales': [100, 200]})
df2 = pd.DataFrame({'Region': ['North', 'South'], 'Revenue': [5000, 6000]})

with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
   df1.to_excel(writer, sheet_name='Products', index=False)
   df2.to_excel(writer, sheet_name='Regions', index=False)--OUTPUT--# File 'multiple_sheets.xlsx' created with two sheets

When you need to save several DataFrames into one Excel file, pd.ExcelWriter is the tool for the job. It creates a writer object that manages the file. Using it within a with statement ensures everything is saved and closed correctly once you're done.

• Inside the with block, you call the to_excel() method on each DataFrame you want to save.
• Instead of a file path, you pass the writer object as the first argument.
• Each call includes a unique sheet_name to place the data on a separate worksheet.

Setting header formatting with freeze_panes

import pandas as pd

df = pd.DataFrame({'Revenue': [1500, 2500, 3600], 'Expenses': [1200, 1800, 2200]})
df.to_excel('formatted.xlsx', sheet_name='Financial', index=False, freeze_panes=(1,0))--OUTPUT--# File 'formatted.xlsx' created with frozen header row

The freeze_panes parameter is a great way to improve the readability of your Excel files, especially with large datasets. It locks specific rows and columns so they stay visible while you scroll. This ensures your headers are always in view, making the data easier to interpret.

• The tuple passed to freeze_panes, in this case (1, 0), defines what to lock. The first value freezes one row from the top, and the second value freezes zero columns from the left.

Advanced Excel export methods

To move beyond the essentials, you can leverage the xlsxwriter engine with ExcelWriter for fine-grained control over formatting, like column widths and number styles.

Using ExcelWriter for enhanced control

import pandas as pd

df = pd.DataFrame({'Date': pd.date_range('2023-01-01', periods=3), 'Value': [10, 20, 30]})
with pd.ExcelWriter('advanced.xlsx', mode='w') as writer:
   df.to_excel(writer, sheet_name='Data', startrow=1, startcol=1, index=False)--OUTPUT--# File 'advanced.xlsx' created with data starting at cell B2

The ExcelWriter object gives you precise placement control within a worksheet. You can use parameters like startrow and startcol to define exactly where the DataFrame export begins, which is useful for leaving space for titles or notes.

• The startrow=1 parameter tells pandas to begin writing from the second row.
• Similarly, startcol=1 shifts the starting point to the second column.

This combination places your data starting at cell B2, leaving the first row and column empty for other content.

Customizing column widths with xlsxwriter

import pandas as pd

df = pd.DataFrame({'LongColumnName': [1, 2], 'Values': [100, 200]})
with pd.ExcelWriter('custom_columns.xlsx', engine='xlsxwriter') as writer:
   df.to_excel(writer, index=False)
   worksheet = writer.sheets['Sheet1']
   worksheet.set_column('A:A', 20)  # Set width of column A to 20--OUTPUT--# File 'custom_columns.xlsx' created with wider first column

To customize column widths, you must set engine='xlsxwriter' in your ExcelWriter. This unlocks advanced formatting capabilities that aren't available by default. After exporting the DataFrame, you can access the underlying xlsxwriter worksheet object to make direct adjustments.

• You grab the specific worksheet using writer.sheets['Sheet1'].
• Then, you call the set_column() method on that worksheet. For example, set_column('A:A', 20) sets the width of the first column to 20 character units, preventing long headers or cell content from being cut off.

Applying number formatting with xlsxwriter

import pandas as pd

df = pd.DataFrame({'Numbers': [1234.56, 9876.54]})
with pd.ExcelWriter('styled.xlsx', engine='xlsxwriter') as writer:
   df.to_excel(writer, index=False)
   workbook = writer.book
   worksheet = writer.sheets['Sheet1']
   number_format = workbook.add_format({'num_format': '#,##0.00'})
   worksheet.set_column('A:A', 15, number_format)--OUTPUT--# File 'styled.xlsx' created with formatted numbers

You can also apply specific number formats for better data presentation. This is done by creating a format object from the `xlsxwriter` workbook and applying it to your desired columns, giving you control over how numbers appear in the spreadsheet.

• First, you access the workbook with `writer.book` and define a style using `workbook.add_format()`. The dictionary `{'num_format': '#,##0.00'}` tells Excel to use a comma as a thousands separator and show two decimal places.
• Then, you apply this style to column A with `worksheet.set_column('A:A', 15, number_format)`, which also conveniently sets the column width.

Move faster with Replit

Replit is an AI-powered development platform that transforms natural language into working applications. Describe what you want to build, and Replit Agent creates it—complete with databases, APIs, and deployment.

For the Excel export techniques we've explored, Replit Agent can turn them into production-ready tools:

• Build a reporting tool that automatically converts raw data into formatted Excel reports, using parameters like sheet_name and freeze_panes for clarity.
• Create a sales dashboard that fetches live data, processes it with pandas, and exports it into a multi-sheet Excel file using pd.ExcelWriter.
• Deploy an automated financial statement generator that applies custom number formats and column widths to transaction data with the xlsxwriter engine.

Describe your app idea, and the agent writes the code, tests it, and fixes issues automatically. Start building your own data tools with Replit Agent.

Common errors and challenges

Even with a straightforward method like to_excel(), you might encounter issues with dependencies, file permissions, or data formatting.

Fixing missing dependency errors when using to_excel()

The to_excel() function relies on other Python libraries to write Excel files. If you try to save a file without the necessary dependency installed, pandas will raise an ImportError.

• For modern .xlsx files, you'll need the openpyxl library.
• You can install it by running pip install openpyxl in your terminal. Once installed, your export command should work without a hitch.

Handling file permission issues with open Excel files

A PermissionError is another common roadblock. This error usually means the Excel file you're trying to write to is already open on your computer.

When a file is open, your operating system often locks it to prevent other programs from making changes. The fix is simple—just close the file in Excel and run your Python script again.

Properly formatting datetime columns in Excel

Sometimes, your datetime columns might show up in Excel as a string of numbers instead of readable dates. This happens because Excel stores dates as serial numbers, and pandas doesn't always apply the correct display format automatically.

As shown in the advanced examples, the best way to handle this is by using the xlsxwriter engine. You can define a specific date format—like 'yyyy-mm-dd'—and apply it directly to the column, ensuring your dates always look exactly as you intend.

Fixing missing dependency errors when using to_excel()

When using to_excel(), pandas delegates the actual file-writing to a specialized library. If this required dependency isn't installed in your environment, your script will fail with an ImportError. The code below shows a simple export that triggers this exact issue.

import pandas as pd

df = pd.DataFrame({'Data': [1, 2, 3]})
df.to_excel('output.xlsx')  # This will fail if openpyxl/xlsxwriter is not installed

This code fails because the to_excel() function calls a separate library to handle the export. If that library isn't present in your environment, pandas raises an error. The command below shows how to resolve this issue.

import pandas as pd
import sys

try:
   df = pd.DataFrame({'Data': [1, 2, 3]})
   df.to_excel('output.xlsx')
except ImportError:
   print("Please install openpyxl: pip install openpyxl")
   sys.exit(1)

This solution wraps the to_excel() call in a try...except block to gracefully handle dependency issues. It anticipates a potential ImportError, which occurs if a required library like openpyxl isn't installed. If the error happens, the except block prints a helpful installation message instead of crashing. This is a robust approach, especially for scripts you intend to share, ensuring users can easily resolve environment-related errors on their own.

Handling file permission issues with open Excel files

A PermissionError often pops up when your script tries to overwrite an Excel file that's already open. Your operating system locks the file to prevent conflicts, which stops pandas from saving changes. This is a common and easily fixable issue.

The following code demonstrates a simple scenario where running to_excel() on an open file will trigger this exact error.

import pandas as pd

df = pd.DataFrame({'Values': [10, 20, 30]})
df.to_excel('data.xlsx')  # Will fail if data.xlsx is already open in Excel

The script attempts to write directly to data.xlsx without checking if the file is available. If the file is open elsewhere, the operating system’s lock blocks the write operation, triggering the error. The code below shows how to handle this.

import pandas as pd
import time

df = pd.DataFrame({'Values': [10, 20, 30]})
for attempt in range(3):
   try:
       df.to_excel('data.xlsx')
       break
   except PermissionError:
       print(f"File is open. Retrying in 2 seconds... ({attempt+1}/3)")
       time.sleep(2)

This solution wraps the to_excel() call in a for loop that retries the operation up to three times. Inside the loop, a try...except block catches a PermissionError. If the file is open, the script prints a warning, waits two seconds using time.sleep(2), and tries again. Once the file is successfully written, the break statement exits the loop. This automated retry logic is useful for scripts that run without direct supervision.

Properly formatting datetime columns in Excel

When you export a DataFrame containing datetime objects, the dates can appear as raw serial numbers in the final Excel file. This formatting issue occurs because pandas doesn't automatically apply a human-readable date style. The code below shows this in action.

import pandas as pd
import datetime

dates = [datetime.datetime(2023, 1, 1), datetime.datetime(2023, 2, 1)]
df = pd.DataFrame({'Date': dates, 'Value': [100, 200]})
df.to_excel('dates.xlsx')  # Dates may not display as expected

The script writes datetime objects directly, but Excel needs a specific format to display them as dates. Without it, you'll just see serial numbers. The code below demonstrates the correct approach.

import pandas as pd
import datetime

dates = [datetime.datetime(2023, 1, 1), datetime.datetime(2023, 2, 1)]
df = pd.DataFrame({'Date': dates, 'Value': [100, 200]})
with pd.ExcelWriter('dates.xlsx', engine='xlsxwriter') as writer:
   df.to_excel(writer, index=False)
   workbook = writer.book
   worksheet = writer.sheets['Sheet1']
   date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
   worksheet.set_column('A:A', 12, date_format)

This solution gives you direct control over formatting by using the xlsxwriter engine with pd.ExcelWriter. It ensures your dates are readable instead of appearing as raw serial numbers.

• First, you define a custom date style, like 'yyyy-mm-dd', using workbook.add_format().
• Then, you apply this format to the entire date column with worksheet.set_column().

This approach is essential whenever you need consistent and clean date presentation in your final Excel file.

Real-world applications

With the methods and troubleshooting covered, you can now build practical reports and dashboards for real-world analysis.

Generating a monthly sales report with to_excel()

Creating a monthly sales report is a common business task that you can easily automate by processing data in a DataFrame and saving the final output with to_excel().

import pandas as pd
import numpy as np

# Create sample monthly sales data
sales_data = pd.DataFrame({
   'Month': pd.date_range('2023-01-01', periods=6, freq='M'),
   'Sales': np.random.randint(1000, 5000, 6),
   'Expenses': np.random.randint(800, 3000, 6)
})
sales_data['Profit'] = sales_data['Sales'] - sales_data['Expenses']
sales_data.to_excel('monthly_sales_report.xlsx', index=False)

This script constructs a pandas DataFrame to simulate financial data. It uses pd.date_range to create a series of six monthly dates and np.random.randint to populate the 'Sales' and 'Expenses' columns with sample numbers.

• A new 'Profit' column is created by subtracting the 'Expenses' from 'Sales'.
• The final DataFrame is then exported to an Excel file using to_excel().
• index=False ensures the spreadsheet doesn't include the default row index, resulting in a cleaner output.

Creating a multi-sheet financial dashboard

For a more comprehensive view, you can use pd.ExcelWriter to consolidate different financial datasets, such as revenue and expenses, into separate, neatly formatted sheets within a single Excel file.

import pandas as pd
import numpy as np

# Create financial datasets
revenue = pd.DataFrame({'Department': ['Sales', 'Marketing', 'R&D'],
                      'Q1': [45000, 22000, 31000],
                      'Q2': [48000, 24000, 32000]})
expenses = pd.DataFrame({'Category': ['Salaries', 'Equipment', 'Rent'],
                       'Amount': [75000, 45000, 35000]})

with pd.ExcelWriter('financial_dashboard.xlsx', engine='xlsxwriter') as writer:
   revenue.to_excel(writer, sheet_name='Revenue', index=False)
   expenses.to_excel(writer, sheet_name='Expenses', index=False)
   
   # Format the revenue sheet
   workbook = writer.book
   worksheet = writer.sheets['Revenue']
   number_format = workbook.add_format({'num_format': '$#,##0'})
   worksheet.set_column('B:C', 12, number_format)

This code uses pd.ExcelWriter to create a consolidated financial report. By specifying engine='xlsxwriter', you unlock advanced formatting options not available with the default engine.

• It first writes the revenue and expenses DataFrames to separate worksheets, organizing the data logically within one file.
• Next, it directly manipulates the worksheet by accessing the writer.book and writer.sheets objects. This allows it to create a custom currency format and apply it to specific columns, ensuring the final report is polished and easy to interpret.

Get started with Replit

Now, turn these techniques into a real tool. Tell Replit Agent to “build a tool that converts CSVs into a formatted Excel report” or “create a dashboard that exports sales data to a multi-sheet Excel file.”

The agent writes the code, tests for errors, and deploys your application automatically. You just describe the idea. Start building with Replit.