How to Create an Excel File from Data in Python

So your data is in Python, but your colleagues live in Excel. This is one of the most common final steps in any data analysis project: you’ve done the hard work of cleaning, analyzing, and wrestling with your data, but now you need to package it up for someone who needs a simple .xlsx file. This article will show you exactly how to create Excel files directly from your Python scripts, moving from simple data dumps to impressive multi-sheet reports with custom formatting.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Why Not Just Use a CSV?

Creating a CSV (Comma-Separated Values) file is often the first thing people learn when exporting data. It's simple, universal, and works well for raw data. But let's be honest - it falls short the moment you need a professional-looking report.

Here’s where a true Excel file (.xlsx) has the clear advantage:

• Multiple Sheets: You can organize different types of data into separate tabs within a single file. For example, you could have one sheet for sales data, another for marketing campaign performance, and a third for website traffic, all in one workbook.
• Formatting: You can apply formatting like bold headers, currency symbols, colored cells, and adjusted column widths to make your data much more readable and professional.
• Charts and Visualizations: You can programmatically generate charts and graphs directly inside the spreadsheet, turning a boring data table into an instant visual report.
• Formulas: You can write Excel formulas into cells to allow for dynamic calculations right within the spreadsheet.

A CSV is a snapshot of raw data. An .xlsx file is a shareable, polished report ready for your team or client.

Setting Up Your Python Environment

Before we start writing code, let’s get the necessary libraries installed. It’s always a good practice to use a virtual environment to keep your project's dependencies separate from your system's global Python installation. You can create one from your terminal:

# Create a new virtual environment
python -m venv my_excel_project

# Activate it (on MacOS/Linux)
source my_excel_project/bin/activate

# Activate it (on Windows)
.\my_excel_project\Scripts\activate

Once your environment is active, you can install the libraries we'll be using. We'll focus on two main players:

• Pandas: If you're doing any data manipulation in Python, you're probably already using Pandas. It's the absolute standard for working with tabular data and has a wonderfully simple method for exporting data to Excel.
• OpenPyXL: This is the library that Pandas uses under the hood to write .xlsx files. You often don't need to interact with it directly, but it's good to install it as the engine.
• XlsxWriter: An alternative, and more powerful, engine for creating Excel files. We'll use this later to add custom formatting and charts.

Install them using pip:

pip install pandas openpyxl xlsxwriter

With that done, you're ready to start exporting data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Quick and Easy Export with Pandas

For 90% of cases, Pandas is all you need to create an Excel file. Its to_excel() method is incredibly powerful and easy to use.

First, let's create some sample data in a Pandas DataFrame. Imagine we have some basic performance data for a few marketing campaigns.

import pandas as pd

# Creating some sample data
data = {
    'Campaign': ['Summer Sale', 'Back to School', 'Holiday Promo', 'New Year Kickoff'],
    'Spend': [5000, 7500, 15000, 8000],
    'Clicks': [12000, 18500, 32000, 21000],
    'Conversions': [250, 450, 950, 520]
}

df = pd.DataFrame(data)

print(df)

Now, to export this DataFrame to an Excel file, you just need a single line of code:

df.to_excel("campaign_performance_report.xlsx")

Run that script, and you’ll find a file named campaign_performance_report.xlsx in the same directory. Open it, and you'll see your data. However, you'll probably notice one slightly annoying thing: an extra column on the left with numbers 0, 1, 2, 3. That's the DataFrame's index.

Most of the time, you don't want this in your report. You can easily remove it by setting the index parameter to False.

# Correct way to export without the index
df.to_excel("campaign_performance_report.xlsx", index=False)

Much better! You now have a clean, simple Excel file with your data.

Writing to a Specific Sheet Name

By default, your data is written to a sheet named "Sheet1". You can easily give it a more descriptive name using the sheet_name parameter.

df.to_excel(
    "campaign_performance_report.xlsx",
    index=False,
    sheet_name="Q4 Campaign Data"
)

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Exporting to Multiple Sheets in One File

What if you have multiple datasets you want to include in a single workbook? For instance, maybe you want your high-level campaign data on one sheet and detailed lead data on another. Trying to do this with separate to_excel() calls will just overwrite the file each time.

The solution is to use the pd.ExcelWriter object. This lets you open a single Excel file for writing and add multiple sheets to it, one by one. Here's how it works.

First, let's create a second DataFrame with some more granular data.

import pandas as pd

# First DataFrame: Campaign Summary
campaign_data = {
    'Campaign': ['Summer Sale', 'Back to School', 'Holiday Promo'],
    'Spend': [5000, 7500, 15000],
    'Conversions': [250, 450, 950]
}
campaign_df = pd.DataFrame(campaign_data)

# Second DataFrame: Monthly Leads
leads_data = {
    'Month': ['October', 'November', 'December'],
    'Organic Leads': [1200, 1500, 2100],
    'Paid Leads': [800, 1100, 3500],
}
leads_df = pd.DataFrame(leads_data)

# Use ExcelWriter to save both to the same file
with pd.ExcelWriter('full_marketing_report.xlsx') as writer:
    campaign_df.to_excel(writer, sheet_name='Campaign Performance', index=False)
    leads_df.to_excel(writer, sheet_name='Monthly Leads', index=False)

When you run this code, you'll get a single file, full_marketing_report.xlsx, with two tabs: "Campaign Performance" and "Monthly Leads". The with statement ensures the file is properly saved and closed when you're done, which is a clean way to manage the process.

Adding Custom Formatting and Charts with XlsxWriter

Plain data is good, but a well-formatted report is even better. This is where you might need more control than Pandas' default exporter provides. While libraries like openpyxl can also handle formatting, XlsxWriter offers a very clean and powerful API for this, and it plugs directly into Pandas.

To use it, you just tell the pd.ExcelWriter to use xlsxwriter as its engine. From there, you can access the workbook and worksheet objects to add formats and charts.

Let's take our campaign performance data and make it look much more presentable. We'll make the headers bold, format the 'Spend' column as currency, and adjust the column widths to fit the content.

import pandas as pd

# Our sample data again
data = {
    'Campaign': ['Summer Sale', 'Back to School', 'Holiday Promo', 'New Year Kickoff'],
    'Spend': [5000, 7500, 15000, 8000],
    'Clicks': [12000, 18500, 32000, 21000],
    'Conversions': [250, 450, 950, 520]
}
df = pd.DataFrame(data)

# Set up the Excel writer with the xlsxwriter engine
writer = pd.ExcelWriter("formatted_campaign_report.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name="Formatted Report", index=False)

# Get the workbook and worksheet objects
workbook = writer.book
worksheet = writer.sheets["Formatted Report"]

# --- Add Formatting ---

# 1. Create a format for the header
header_format = workbook.add_format({
    "bold": True,
    "bg_color": "#D3D3D3", # Light gray background
    "border": 1
})

# 2. Create a format for currency
currency_format = workbook.add_format({"num_format": "$#,##0"} )

# 3. Apply the header format
worksheet.write_row("A1", df.columns, header_format)

# 4. Apply currency formatting to the 'Spend' column (column B)
worksheet.set_column(1, 1, None, currency_format)

# 5. Let's make the columns wider for readability
worksheet.set_column("A:A", 20) # Column A width
worksheet.set_column("B:D", 15) # Columns B to D width

# --- It's that simple! Save the file ---
writer.close()

When you open formatted_campaign_report.xlsx, you'll see a huge difference. The headers are bolded with a gray background, the spend values have dollar signs, and the columns are neatly sized. This is the kind of automation that can save you hours of manual cleanup in Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Final Thoughts

Learning how to export your data from Python directly into a clean, multi-sheet, and well-formatted Excel workbook is a game-changer for anyone in a data-driven role. It turns the final, often tedious, step of reporting into a repeatable, automated part of your analysis pipeline. By leveraging Pandas and XlsxWriter, you can stop manually copying and pasting and spend more time uncovering insights.

Automating Excel reports is a fantastic step, but the next evolution is creating live, interactive dashboards that are always up-to-date. Instead of exporting static files, this is where a tool like Graphed comes in. We built it so that marketers, founders, and sales teams can connect their data sources - like Google Analytics, Shopify, or Salesforce - and build real-time reports just by asking for what they want in plain English. There’s no code or manual exporting needed, you connect your tools once, and your dashboards update automatically, providing you with instant answers whenever you need them.