When working with spreadsheets, readability is just as important as the data itself. A well-formatted Excel file makes it easier to analyze, present, and share information. Instead of manually adjusting styles in Excel, you can use Python for Excel formatting to automate the process and save significant time.

This tutorial shows you how to format Excel with Python using the library Spire.XLS for Python. We’ll cover basic styling, advanced formatting, and practical use cases, while also explaining the key classes and properties that make Excel formatting in Python efficient.

Here's What's Covered:

• Why Use Python for Excel Formatting
• Setting Up the Environment and Project
• Basic Excel Formatting in Python
• Extended Excel Formatting in Python
• Key APIs for Excel Styling in Python
• Use Case: Formatting an Excel Report with Python
• Conclusion
• FAQ

Why Use Python for Excel Formatting

Formatting Excel manually is time-consuming, especially when handling large datasets or generating reports dynamically. By using Python Excel formatting, you can:

• Apply consistent formatting to multiple workbooks.
• Automate repetitive tasks like setting fonts, borders, and colors.
• Generate styled reports programmatically for business or research.
• Save time while improving accuracy and presentation quality.

With Python, you can quickly build scripts that apply professional-looking styles to your spreadsheets. Next, let’s see how to set up the environment.

Setting Up the Environment and Project

To follow this tutorial, you need to install Spire.XLS for Python, a library designed for working with Excel files. It supports creating, reading, modifying, and formatting Excel documents programmatically.

Install Spire.XLS for Python

Install the library via pip:

pip install Spire.XLS

Then import it in your Python script:

from spire.xls import *

Creating or Loading an Excel Workbook

Before we start formatting, we need a workbook to work with.

Create a new workbook:

workbook = Workbook()
sheet = workbook.Worksheets[0]

Or load an existing file:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

After applying formatting, save the result:

workbook.SaveToFile("output/formatted_output.xlsx", ExcelVersion.Version2016)

With the workbook ready, let’s move on to formatting examples.

Basic Excel Formatting in Python

Before diving into advanced operations, it’s important to master the fundamental Excel formatting features in Python. These basic techniques—such as fonts, alignment, borders, background colors, and adjusting column widths or row heights—are the building blocks of clear, professional spreadsheets. Once familiar with them, you can combine and extend these methods to create more complex styles later.

1. Formatting Fonts

Changing font properties is one of the most frequent tasks when working with styled Excel sheets. In Spire.XLS for Python, font settings are accessed through the CellRange.Style.Font object, which lets you control the typeface, size, color, and emphasis (bold, italic, underline).

cell = sheet.Range[2, 2]
cell.Text = "Python Excel Formatting"

cell.Style.Font.FontName = "Arial"
cell.Style.Font.Size = 14
cell.Style.Font.Color = Color.get_Blue()
cell.Style.Font.IsBold = True

This modifies the text appearance directly within the cell by adjusting its style attributes.

2. Alignment and Wrapping

Cell alignment is managed through the HorizontalAlignment and VerticalAlignment properties of the Style object. In addition, the WrapText property ensures that longer text fits within a cell without overflowing.

cell = sheet.Range[4, 2]
cell.Text = "This text is centered and wrapped."

cell.Style.HorizontalAlignment = HorizontalAlignType.Center
cell.Style.VerticalAlignment = VerticalAlignType.Center
cell.Style.WrapText = True

This produces neatly centered text that remains readable even when it spans multiple lines.

3. Adding Borders

Borders are defined through the Borders collection on the Style object, where you can set the line style and color for each edge individually.

cell = sheet.Range[6, 2]
cell.Text = "Border Example"

cell.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin
cell.Style.Borders[BordersLineType.EdgeBottom].Color = Color.get_Black()

This adds a thin black line to separate the cell from the content below.

4. Background Colors

Cell background is controlled by the Style.Color property. This is often used to highlight headers or important values.

cell = sheet.Range[8, 2]
cell.Text = "Highlighted Cell"
cell.Style.Color = Color.get_Yellow()

This makes the cell stand out in the worksheet.

5. Setting Column Widths and Row Heights

Besides styling text and borders, adjusting the column widths and row heights ensures that your content fits properly without overlapping or leaving excessive blank space.

# Set specific column width
sheet.Columns[1].ColumnWidth = 20
# Set specific row height
sheet.Rows[7].RowHeight = 20

In addition to specifying widths and heights, Excel rows and columns can also be automatically adjusted to fit their content:

• Use Worksheet.AutoFitColumn(columnIndex) or Worksheet.AutoFitRow(rowIndex) to adjust a specific column or row.
• Use CellRange.AutoFitColumns() or CellRange.AutoFitRows() to adjust all columns or rows in a selected range.

This helps create well-structured spreadsheets where data is both readable and neatly aligned.

Preview of Basic Formatting Result

Here’s what the basic Excel formatting looks like in practice:

In addition to these visual styles, you can also customize how numbers, dates, or currency values are displayed—see How to Set Excel Number Format in Python for details.

Extended Excel Formatting in Python

Instead of formatting individual cells, you can also work with ranges and reusable styles. These operations typically involve the CellRange object for merging and sizing, and the Workbook.Styles collection for creating reusable Style definitions.

1. Merging Cells

Merging cells is often used to create report titles or section headers that span multiple columns.

range = sheet.Range[2, 2, 2, 4]
range.Merge()
range.Text = "Quarterly Report"
range.Style.HorizontalAlignment = HorizontalAlignType.Center
range.RowHeight = 30

Here, three columns (B2:D2) are merged into one, styled as a bold, centered title with a shaded background—perfect for highlighting key sections in a report.

2. Applying Built-in Styles

Excel comes with a set of predefined styles that can be applied quickly. In Spire.XLS, you can directly assign these built-in styles to ranges.

range.BuiltInStyle = BuiltInStyles.Heading1

This instantly applies Excel’s built-in Heading 1 style, giving the range a consistent and professional appearance without manually setting fonts, borders, or colors.

3. Creating a Custom Style and Applying It

Creating a custom style is useful when you need to apply the same formatting rules across multiple cells or sheets. The Workbook.Styles.Add() method allows you to define a named style that can be reused throughout the workbook.

# Create a custom style
custom_style = workbook.Styles.Add("CustomStyle")
custom_style.Font.FontName = "Calibri"
custom_style.Font.Size = 12
custom_style.Font.Color = Color.get_DarkGreen()
custom_style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.MediumDashDot

# Apply style to a cell
cell = sheet.Range[4, 2]
cell.Text = "Custom Style Applied"
cell.Style = custom_style

This method makes Excel style management in Python more efficient, especially when working with large datasets.

Preview of Advanced Formatting Result

Below is an example showing the results of merged cells, built-in styles, and a custom style applied:

For highlighting data patterns and trends, you can also use conditional rules—see How to Apply Conditional Formatting in Python Excel.

Key APIs for Excel Styling in Python

After exploring both basic and advanced formatting examples, it’s helpful to step back and look at the core classes, properties, and methods that make Excel styling in Python possible. Understanding these elements will give you the foundation to write scripts that are flexible, reusable, and easier to maintain.

The following table summarizes the most important classes, properties, and methods for formatting Excel with Python. You can use it as a quick reference whenever you need to recall the key styling options.

With these building blocks, you can handle common formatting tasks—such as fonts, borders, alignment, colors, and number formats—in a structured and highly customizable way. This ensures that your spreadsheets look professional, remain easy to manage, and can be consistently styled across different Python automation projects. For more details, see the Spire.XLS for Python API reference.

Real-World Use Case: Formatting an Annual Excel Sales Report with Python

Now that we’ve explored both basic and advanced Excel formatting techniques, let’s apply them in a real-world scenario. The following example demonstrates how to generate a comprehensive Excel annual sales report with Python. By combining structured data, regional breakdowns, and advanced formatting, the report becomes much easier to interpret and presents business data in a clear and professional way.

from spire.xls import *

workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Sales Report"

# Report title
title = sheet.Range[1, 1, 1, 7]
title.Merge()
title.Text = "Annual Sales Report - 2024"
title.Style.Font.IsBold = True
title.Style.Font.Size = 16
title.Style.HorizontalAlignment = HorizontalAlignType.Center
title.Style.Color = Color.get_LightGray()
title.RowHeight = 30

# Data
data = [
    ["Product", "Region", "Q1", "Q2", "Q3", "Q4", "Total"],
    ["Laptop", "North", 1200, 1500, 1300, 1600, 5600],
    ["Laptop", "South", 1000, 1200, 1100, 1300, 4600],
    ["Tablet", "North", 800, 950, 1000, 1200, 3950],
    ["Tablet", "South", 700, 850, 900, 1000, 3450],
    ["Phone", "North", 2000, 2200, 2100, 2500, 8800],
    ["Phone", "South", 1800, 1900, 2000, 2200, 7900],
    ["Accessories", "All", 600, 750, 720, 900, 2970],
    ["", "", "", "", "", "Grand Total", 39370]
]

for r in range(len(data)):
    for c in range(len(data[r])):
        sheet.Range[r+2, c+1].Text = str(data[r][c])

# Header formatting
header = sheet.Range[2, 1, 2, 7]
header.Style.Font.IsBold = True
header.Style.Color = Color.get_LightBlue()
header.Style.HorizontalAlignment = HorizontalAlignType.Center
header.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

# Numeric columns as currency
for row in range(3, 10):
    for col in range(3, 8):
        cell = sheet.Range[row, col]
        if cell.Text.isdigit():
            cell.NumberValue = float(cell.Text)
            cell.NumberFormat = "$#,##0"

# Highlight totals
grand_total = sheet.Range[10, 7]
grand_total.Style.Color = Color.get_LightYellow()
grand_total.Style.Font.IsBold = True

# Freeze the first row and the first two columns
sheet.FreezePanes(2, 3)

# Auto fit columns
sheet.AllocatedRange.AutoFitColumns()

workbook.SaveToFile("output/annual_sales_report.xlsx", ExcelVersion.Version2016)

This script combines several formatting techniques—such as a merged and styled title, bold headers with borders, currency formatting, highlighted totals, and frozen panes—into a single workflow. Applying these styles programmatically improves readability and ensures consistency across every report, which is especially valuable for business and financial data.

Here’s the final styled annual sales report generated with Python:

Conclusion

Formatting Excel with Python is a practical way to automate report generation and ensure professional presentation of data. By combining basic styling with advanced techniques like custom styles and column adjustments, you can create clear, consistent, and polished spreadsheets.

Whether you are working on financial reports, research data, or business dashboards, formatting Excel with Python helps you save time while maintaining presentation quality. With the right use of styles, properties, and formatting options, your spreadsheets will not only contain valuable data but also deliver it in a visually effective way.

You can apply for a free temporary license to unlock the full functionality of Spire.XLS for Python or try Free Spire.XLS for Python to get started quickly.

FAQ - Excel Fpormatting in Python

Q1: Can you format Excel with Python?

Yes. Python provides libraries that allow you to apply fonts, colors, borders, alignment, conditional formatting, and more to Excel files programmatically.

Q2: How to do formatting in Python?

For Excel formatting, you can use a library such as Spire.XLS for Python. It lets you change fonts, set alignment, adjust column widths, merge cells, and apply custom or built-in styles through code.

Q3: Can I use Python to edit Excel?

Yes. Python can not only format but also create, read, modify, and save Excel files, making it useful for dynamic reporting and data automation.

Q4: What’s the best way to automate repeated Excel styling tasks?

Define custom styles or reusable functions that standardize formatting rules across multiple workbooks and sheets. This ensures consistency and saves time.

Applying styles is one of the simplest ways to enhance the professionalism and readability of Excel spreadsheets. Excel provides a wide range of built-in styles that allow users to quickly format cells, ranges, or worksheets. Additionally, users can create custom styles to specify fonts, colors, borders, number formats, and more, tailored to their individual preferences. Whether you're designing professional reports, sales presentations, or project management plans, knowing how to use styles effectively helps make data more visually appealing and easier to understand.

In this guide, you will learn how to apply styles to cells or worksheets in Excel in Python using Spire.XLS for Python.

• Apply a Built-in Style to Cells in Excel in Python
• Apply a Custom Style to Cells in Excel in Python
• Apply a Custom Style to a Worksheet in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Apply a Built-in Style to Cells in Excel in Python

Spire.XLS for Python offers the CellRange.BuiltInStyle property, which enables developers to apply built-in styles, such as Title, Heading 1, and Heading 2 to individual cells or ranges in Excel. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Get the desired cell or range of cells using the Worksheet.Range[] property.
• Apply a built-in style to the cell or range of cells using the CellRange.BuiltInStyle property.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Get the desired cell range
range = sheet.Range["A1:H1"]
# Apply a built-in style to the cell range
range.BuiltInStyle = BuiltInStyles.Heading2

# Save the resulting file
workbook.SaveToFile("ApplyBuiltinStyle.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply a Custom Style to Cells in Excel in Python

Developers can use the Workbook.Styles.Add() method to create a custom style, which can then be applied to individual cells or ranges using the CellRange.Style property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Get the desired cell or range of cells using the Worksheet.Range[] property.
• Add a custom style to the workbook using the Workbook.Styles.Add() method.
• Define the formatting, such as the font size, font color, text alignment, cell borders and cell background color, using the properties of the CellStyle class.
• Apply the custom style to the cell or range of cells using the CellRange.Style property.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Get the desired cell range
range = sheet.Range["A1:H1"]

# Add a custom style to the workbook
style = workbook.Styles.Add("CustomCellStyle")
# Set the font size
style.Font.Size = 13
# Set the font color
style.Font.Color = Color.get_White()
# Bold the text
style.Font.IsBold = True
# Set the vertical text alignment
style.VerticalAlignment = VerticalAlignType.Bottom
# Set the horizontal text alignment
style.HorizontalAlignment = HorizontalAlignType.Left
# Set the bottom border color
style.Borders[BordersLineType.EdgeBottom].Color = Color.get_GreenYellow()
# Set the bottom border type
style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium
# Set the background color
style.Color = Color.get_CornflowerBlue()

# Apply the custom style to the cell range
range.Style = style

# Save the resulting file
workbook.SaveToFile("ApplyCustomStyle.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply a Custom Style to a Worksheet in Excel in Python

In certain cases, it may be necessary to apply a custom style to an entire worksheet rather than to specific cells or ranges. This can be accomplished using the Worksheet.ApplyStyle() method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Add a custom style to the workbook using the Workbook.Styles.Add() method.
• Define the formatting, such as the font size, font color, and cell background color, using the properties of the CellStyle class.
• Apply the custom style to the worksheet using the Worksheet.ApplyStyle() method.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Add a custom style to the workbook
style = workbook.Styles.Add("CustomSheetStyle")
# Set the font size
style.Font.Size = 12
# Set the font color
style.Font.Color = Color.FromRgb(91, 155, 213)
# Set the cell background color
style.Color = Color.FromRgb(242, 242, 242)

# Apply the custom style to the worksheet
sheet.ApplyStyle(style)

# Save the resulting file
workbook.SaveToFile("ApplyCustomStyleToSheet.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.