How to write an Excel file in Python

You can use Python to write Excel files, a fundamental task for anyone who manages data. It's a powerful way to automate reports and streamline complex data workflows with simple code.

In this article, we'll cover several techniques for this task. We'll provide practical tips, show real-world applications, and offer debugging advice to help you write Excel files with confidence for your projects.

Using pandas to write a basic Excel file

import pandas as pd

data = {'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [25, 30, 35]}
df = pd.DataFrame(data)
df.to_excel('simple_example.xlsx', index=False)--OUTPUT--# No visible output, but 'simple_example.xlsx' is created in the current directory

The pandas library excels at this task because it uses DataFrames—a two-dimensional structure that mirrors a spreadsheet. The code first organizes the data into a pd.DataFrame. This step is crucial because it prepares your data in a format that pandas can easily export.

The to_excel() method then handles the conversion to an .xlsx file. Notice the index=False argument. This is a key detail that tells pandas to omit the DataFrame's index, which are the row numbers. This ensures your final spreadsheet only contains the data you specified.

Basic Excel writing techniques

While pandas is excellent for quick exports, libraries like openpyxl and xlsxwriter give you more granular control over creating and styling your Excel files.

Using openpyxl to create a workbook from scratch

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 'Name'
ws['B1'] = 'Score'
ws.append(['Alice', 95])
wb.save('openpyxl_example.xlsx')--OUTPUT--# No visible output, but 'openpyxl_example.xlsx' is created in the current directory

With openpyxl, you build your spreadsheet piece by piece. First, you create a new Workbook() and grab the active worksheet with wb.active. This gives you a blank sheet to populate.

• You can assign values directly to cells using their address, like ws['A1'].
• The append() method adds a complete row of data at once.

Once your data is in place, the save() function writes the entire workbook to an .xlsx file.

Using xlsxwriter for Excel file creation

import xlsxwriter

workbook = xlsxwriter.Workbook('xlsxwriter_example.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Product')
worksheet.write('B1', 'Price')
worksheet.write(1, 0, 'Laptop') # Row 1, Col 0
worksheet.write(1, 1, 999.99)
workbook.close()--OUTPUT--# No visible output, but 'xlsxwriter_example.xlsx' is created in the current directory

The xlsxwriter library provides a direct approach to file creation. You initialize a new file by calling xlsxwriter.Workbook() and then add a worksheet to it. This gives you a blank canvas to work with.

• The write() method lets you add data flexibly. You can use either cell notation like 'A1' or zero-indexed row and column numbers.
• It's crucial to call workbook.close() when you're finished. This function saves the file and properly closes it.

Adding cell formatting with xlsxwriter

import xlsxwriter

workbook = xlsxwriter.Workbook('formatted_excel.xlsx')
worksheet = workbook.add_worksheet()
bold_format = workbook.add_format({'bold': True, 'bg_color': '#E6E6E6'})
worksheet.write('A1', 'Header', bold_format)
worksheet.write('A2', 'Data')
workbook.close()--OUTPUT--# No visible output, but 'formatted_excel.xlsx' is created with formatted cells

The xlsxwriter library shines when it comes to styling. You can create reusable format objects to control fonts, colors, and alignment. The add_format() method is your starting point—it takes a dictionary of style properties to define how your cells should look.

• In the example, a format object is created with {'bold': True, 'bg_color': '#E6E6E6'} to define a bold font and a light gray background.
• You then apply this style by passing the format object as the third argument to the write() method, giving you precise control over your spreadsheet’s appearance.

Advanced Excel operations

Beyond creating and formatting single sheets, you can also manage multiple worksheets, generate charts, and even modify existing files to build more dynamic reports.

Working with multiple worksheets

import pandas as pd

df1 = pd.DataFrame({'Data': [10, 20, 30]})
df2 = pd.DataFrame({'Data': [40, 50, 60]})

with pd.ExcelWriter('multi_sheet.xlsx') as writer:
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)--OUTPUT--# No visible output, but 'multi_sheet.xlsx' is created with two worksheets

To write data across multiple sheets, you'll use pd.ExcelWriter. This object acts as a file handler. Wrapping it in a with statement is best practice, as it automatically saves and closes the file for you.

• Inside the with block, you call the to_excel() method on each DataFrame.
• You pass the writer object to the method and use the sheet_name argument to name each worksheet, directing your data to the right place.

Creating charts with xlsxwriter

import pandas as pd
import numpy as np

data = pd.DataFrame({'Value': np.random.randn(10).cumsum()})
writer = pd.ExcelWriter('chart_example.xlsx', engine='xlsxwriter')
data.to_excel(writer, sheet_name='Data')
workbook = writer.book
worksheet = writer.sheets['Data']
chart = workbook.add_chart({'type': 'line'})
chart.add_series({'values': '=Data!$B$2:$B$11'})
worksheet.insert_chart('D2', chart)
writer.close()--OUTPUT--# No visible output, but 'chart_example.xlsx' is created with a line chart

To visualize data, you can integrate pandas with the xlsxwriter engine. You do this by setting engine='xlsxwriter' when creating the pd.ExcelWriter. This gives you access to xlsxwriter’s powerful features directly from the writer object.

• First, you create a chart object using workbook.add_chart() and define its type.
• Next, you link your data to the chart with add_series(), referencing the specific cells.
• Finally, insert_chart() places the finished chart onto your worksheet.

Using openpyxl to modify existing Excel files

from openpyxl import load_workbook

wb = load_workbook('simple_example.xlsx')
ws = wb.active
ws['D1'] = 'Status'
for row in range(2, 5):
ws.cell(row=row, column=4, value='Active')
wb.save('modified_example.xlsx')--OUTPUT--# No visible output, but 'modified_example.xlsx' is created with the additional column

To edit an existing spreadsheet, you'll use the load_workbook() function. This opens your specified file and prepares it for changes. From there, you can select a worksheet and manipulate its cells just as you would with a new workbook.

• The cell() method is used within a loop to programmatically add data to a new column.
• Finally, save() writes your changes. It's good practice to save to a new filename to keep your original data intact.

Move faster with Replit

Replit is an AI-powered development platform that comes with all Python dependencies pre-installed, so you can skip setup and start coding instantly. You can describe what you want to build, and Agent 4 handles everything—from writing the code and connecting to APIs to deploying your application.

Instead of piecing together techniques to write Excel files, you can describe the final tool you need and let Agent 4 build it:

• A sales dashboard that automatically pulls daily figures and generates a multi-sheet Excel report with summary charts.
• An inventory management tool that exports current stock levels into a formatted spreadsheet for weekly analysis.
• A personal finance tracker that categorizes your spending and creates a visual budget in an Excel file.

Simply describe your app, and Replit will write the code, test it, and fix issues automatically, all within your browser.

Common errors and challenges

When writing Excel files with Python, you might run into a few common roadblocks, but they’re usually straightforward to solve.

A frequent issue with pandas is the 'Missing required dependencies' error. This happens because pandas relies on other libraries like openpyxl or xlsxwriter to actually write .xlsx files. If they aren't installed in your environment, pandas can't complete the job. The fix is simple—just install the necessary package, for example, pip install openpyxl.

File path errors are another common hurdle when using methods like to_excel(). You might see a FileNotFoundError if the directory you're trying to save to doesn't exist. Always double-check your path for typos and ensure all folders in the path are already created. Using an absolute path can also help debug whether the issue is with the path itself or your script's current working directory.

When using xlsxwriter to add formulas, remember that you're writing a string that Excel will interpret, not Python. A common mistake is trying to use Python syntax. For a formula to work, it must follow Excel's conventions, such as starting with an equals sign and using cell ranges like =SUM(B2:B10). If your formulas aren't calculating correctly, check that they match the syntax you would type directly into an Excel cell.

Resolving the 'Missing required dependencies' error with pandas

The to_excel() function is powerful, but it doesn't work in isolation. It needs a helper library—an "engine"—to handle the actual file writing. If you haven't installed one, you'll hit the "Missing required dependencies" error when you try to save your file.

The following code demonstrates a common scenario where this error occurs. It will fail if the required openpyxl library isn't installed in your environment.

import pandas as pd

data = {'Name': ['Alice', 'Bob'], 'Score': [95, 87]}
df = pd.DataFrame(data)
# Will fail with "Missing required dependencies" if openpyxl is not installed
df.to_excel('scores.xlsx', index=False)

The to_excel() function relies on an external engine to write the file. If that engine isn't installed, pandas has no tool to do the job, which is why the operation fails. The following example shows how to fix this.

# First install the required dependency:
# pip install openpyxl

import pandas as pd

data = {'Name': ['Alice', 'Bob'], 'Score': [95, 87]}
df = pd.DataFrame(data)
df.to_excel('scores.xlsx', index=False)

The fix is straightforward—you just need to install the missing engine. For .xlsx files, openpyxl is a common choice. Running pip install openpyxl in your terminal gives pandas the tool it needs to write the file.

You'll typically encounter this error when exporting a DataFrame for the first time in a new environment. Once the dependency is installed, the to_excel() function will work as expected, successfully creating your spreadsheet.

Fixing file path errors when using to_excel()

When using to_excel(), you might encounter a FileNotFoundError. This happens because pandas won't create directories for you. If you try saving to a folder that doesn't exist, the operation will fail. The code below shows this exact scenario.

import pandas as pd

data = {'Name': ['Alice', 'Bob'], 'Score': [95, 87]}
df = pd.DataFrame(data)
# This will fail if the 'reports' directory doesn't exist
df.to_excel('reports/scores.xlsx', index=False)

The to_excel() method is instructed to save the file inside a `reports` subdirectory. Since that folder doesn't exist, the operation fails. The following code demonstrates how to correctly prepare the path before writing the file.

import pandas as pd
import os

data = {'Name': ['Alice', 'Bob'], 'Score': [95, 87]}
df = pd.DataFrame(data)
# Create the directory if it doesn't exist
os.makedirs('reports', exist_ok=True)
df.to_excel('reports/scores.xlsx', index=False)

The fix is to create the directory before saving. You'll do this using the os module.

• Use os.makedirs('reports', exist_ok=True) to create the target folder if it's missing.
• The exist_ok=True argument is crucial, as it prevents an error if the folder already exists.

This makes your script safe to run multiple times, especially when you're organizing output files into new subdirectories.

Fixing Excel formula syntax in xlsxwriter

When using xlsxwriter, it's easy to mix up Python and Excel syntax inside formulas. The library writes a string directly to the cell, so it must be a valid Excel formula, starting with = and using Excel's functions and cell referencing.

The following code shows a common mistake where the formula syntax is incorrect, which prevents Excel from calculating the result.

import xlsxwriter

workbook = xlsxwriter.Workbook('calculations.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 10)
worksheet.write('A2', 20)
worksheet.write('A3', '=SUM(A1+A2)') # Incorrect formula syntax
workbook.close()

The formula =SUM(A1+A2) fails because the SUM() function expects a cell range, not an expression with a + operator. The following example shows how to write a valid formula that Excel can calculate.

import xlsxwriter

workbook = xlsxwriter.Workbook('calculations.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 10)
worksheet.write('A2', 20)
worksheet.write('A3', '=SUM(A1:A2)') # Correct formula syntax
workbook.close()

The fix is to use valid Excel syntax. The SUM() function expects a cell range like A1:A2, not an arithmetic expression like A1+A2. Since xlsxwriter passes the formula string directly to the spreadsheet, it must be a formula Excel can understand.

Always ensure your formulas match the syntax you would use directly in an Excel cell, especially when automating reports with calculations.

Real-world applications

Putting these skills into practice, you can automate complex tasks like personal finance tracking and creating detailed sales performance reports.

Creating a personal expense tracker with xlsxwriter

You can build a practical expense tracker by using pandas to organize the data and xlsxwriter to apply custom currency formatting and add a final total.

import pandas as pd

expenses = {
'Date': ['2023-01-15', '2023-01-20', '2023-01-25', '2023-01-30'],
'Category': ['Groceries', 'Utilities', 'Dining', 'Transportation'],
'Amount': [120.50, 85.75, 45.20, 32.00]
}

df = pd.DataFrame(expenses)
total = df['Amount'].sum()

with pd.ExcelWriter('expense_tracker.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Expenses', index=False)
workbook = writer.book
worksheet = writer.sheets['Expenses']
money_format = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.set_column('C:C', 10, money_format)
worksheet.write(len(df)+1, 1, 'Total')
worksheet.write(len(df)+1, 2, total, money_format)

This script combines the data handling of pandas with the styling capabilities of xlsxwriter. It calculates the total expenses before writing to the file, allowing it to add a summary row. The key is using pd.ExcelWriter with engine='xlsxwriter' to gain access to advanced formatting.

• A currency style is created with workbook.add_format().
• The set_column() method applies this style to the entire 'Amount' column for consistency.
• Finally, the script writes the total at the bottom, reusing the same format for a polished look.

Generating sales performance reports with conditional formatting

You can use conditional formatting to automatically highlight key performance indicators in a sales report, making it easy to spot high and low performers at a glance.

import pandas as pd

sales_data = {
'Salesperson': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
'Region': ['North', 'South', 'East', 'West', 'Central'],
'Sales': [95000, 82000, 110000, 75000, 120000],
'Target': [100000, 90000, 100000, 80000, 100000]
}

df = pd.DataFrame(sales_data)
df['Performance'] = (df['Sales'] / df['Target']).round(3)

with pd.ExcelWriter('sales_performance.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sales Report', index=False)
workbook = writer.book
worksheet = writer.sheets['Sales Report']

worksheet.set_column('C:D', 12, workbook.add_format({'num_format': '$#,##0'}))
worksheet.set_column('E:E', 12, workbook.add_format({'num_format': '0.0%'}))

worksheet.conditional_format(1, 4, len(df), 4, {
'type': 'cell', 'criteria': '>=', 'value': 1.0,
'format': workbook.add_format({'bg_color': '#C6EFCE'})
})
worksheet.conditional_format(1, 4, len(df), 4, {
'type': 'cell', 'criteria': '<', 'value': 1.0,
'format': workbook.add_format({'bg_color': '#FFC7CE'})
})

This script first loads sales data into a pandas DataFrame and calculates a new Performance column. It then uses pd.ExcelWriter with the xlsxwriter engine to create the spreadsheet. After writing the data, it accesses the worksheet to apply specific styles.

• The set_column() method applies currency and percentage formats to the data for better readability.
• Two rules are added with conditional_format(). One rule applies a green background to cells in the performance column with a value of 1.0 or greater, and another applies a red background for values less than 1.0.

Get started with Replit

Turn these techniques into a real tool. Describe what you need to Replit Agent, like "a script to convert CSVs to formatted Excel reports" or "a tool to track daily sales in a multi-sheet file."

Replit Agent handles the entire workflow. It writes the code, tests for errors, and deploys your application. Start building with Replit.