Add Edit Excel Charts Using Python openpyxl

Python openpyxl makes Excel chart automation easy. You can create dynamic charts from data. This saves time and ensures consistency.

Charts help visualize data trends. Manual chart creation is repetitive. Automation with Python solves this problem efficiently.

Setting Up openpyxl for Chart Creation

First, ensure openpyxl is installed. Use pip for installation. Check our Install Openpyxl in Python Step by Step guide.

 
# Install openpyxl if not already installed
# pip install openpyxl

import openpyxl
from openpyxl.chart import BarChart, Reference

Import necessary modules. BarChart and Reference are essential. They help create and position charts.

Creating Your First Bar Chart

Bar charts compare categories visually. Let's create sample sales data. Then build a bar chart from it.

 
# Create workbook and worksheet
wb = openpyxl.Workbook()
ws = wb.active

# Add sample sales data
data = [
    ['Product', 'Q1 Sales', 'Q2 Sales'],
    ['Laptops', 150, 200],
    ['Phones', 300, 250],
    ['Tablets', 100, 150]
]

for row in data:
    ws.append(row)

# Create bar chart
chart = BarChart()
chart.title = "Quarterly Sales Comparison"
chart.x_axis.title = "Products"
chart.y_axis.title = "Sales Units"

# Define data ranges
data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=4)

# Add data to chart
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

# Position chart in worksheet
ws.add_chart(chart, "E2")

# Save workbook
wb.save("sales_chart.xlsx")

# File created: sales_chart.xlsx
# Contains bar chart at cell E2

The Reference class defines data ranges. add_chart positions the chart. The chart appears in the Excel file.

Creating Line Charts for Trends

Line charts show data trends over time. They are perfect for time series data. Let's create monthly revenue trend.

 
from openpyxl.chart import LineChart

# Create new worksheet for line chart
ws2 = wb.create_sheet("Revenue Trends")

# Monthly revenue data
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
revenue = [5000, 5200, 4800, 6000, 6200, 7000]

ws2.append(['Month', 'Revenue'])
for i in range(len(months)):
    ws2.append([months[i], revenue[i]])

# Create line chart
line_chart = LineChart()
line_chart.title = "Monthly Revenue Trend"
line_chart.x_axis.title = "Months"
line_chart.y_axis.title = "Revenue ($)"

# Add data to line chart
data_ref = Reference(ws2, min_col=2, min_row=1, max_row=7)
categories_ref = Reference(ws2, min_col=1, min_row=2, max_row=7)

line_chart.add_data(data_ref, titles_from_data=True)
line_chart.set_categories(categories_ref)

# Add chart to worksheet
ws2.add_chart(line_chart, "D2")
wb.save("sales_chart.xlsx")

Line charts use the LineChart class. They connect data points with lines. This shows progression clearly.

Creating Pie Charts for Proportions

Pie charts display part-to-whole relationships. They show percentage distributions. Perfect for market share data.

 
from openpyxl.chart import PieChart

# Create market share data
ws3 = wb.create_sheet("Market Share")
share_data = [
    ['Company', 'Market Share'],
    ['Company A', 35],
    ['Company B', 25],
    ['Company C', 20],
    ['Company D', 15],
    ['Others', 5]
]

for row in share_data:
    ws3.append(row)

# Create pie chart
pie_chart = PieChart()
pie_chart.title = "Market Share Distribution"

# Add data to pie chart
data_ref = Reference(ws3, min_col=2, min_row=1, max_row=6)
categories_ref = Reference(ws3, min_col=1, min_row=2, max_row=6)

pie_chart.add_data(data_ref, titles_from_data=True)
pie_chart.set_categories(categories_ref)

# Position pie chart
ws3.add_chart(pie_chart, "D2")
wb.save("sales_chart.xlsx")

Pie charts use PieChart class. They don't need axis titles. The focus is on proportional representation.

Editing Existing Charts

You can modify existing charts programmatically. Change titles, colors, and styles. This maintains chart consistency.

 
# Load existing workbook with charts
wb = openpyxl.load_workbook("sales_chart.xlsx")
ws = wb["Sheet"]

# Access existing chart (first chart in worksheet)
chart = ws._charts[0]

# Modify chart properties
chart.title = "Updated Quarterly Sales Report"
chart.style = 2  # Apply predefined style

# Change chart position
ws.add_chart(chart, "H2")
wb.save("updated_charts.xlsx")

The _charts attribute accesses existing charts. You can modify any chart property. Then reposition as needed.

Advanced Chart Customization

Openpyxl offers deep customization options. You can modify colors, fonts, and layouts. This matches corporate branding.

 
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice

# Customize bar chart appearance
chart = BarChart()
chart.title = "Custom Styled Chart"
chart.x_axis.title = "Categories"
chart.y_axis.title = "Values"

# Add sample data
ws.append(['A', 'B', 'C', 'D'])
ws.append([10, 25, 15, 30])

data_ref = Reference(ws, min_col=1, min_row=1, max_col=4, max_row=2)
chart.add_data(data_ref, titles_from_data=True)

# Apply custom style
chart.graphical_properties.ln = None  # Remove border
chart.plot_area.graphical_properties.solidFill = "F0F0F0"  # Background color

ws.add_chart(chart, "F10")
wb.save("custom_charts.xlsx")

For more styling options, see our Style Cells and Fonts in Excel with Python openpyxl guide.

Working with Multiple Data Series

Charts often need multiple data series. This compares different metrics. Openpyxl handles this seamlessly.

 
# Add multiple series to worksheet
ws.append(['', '2022 Sales', '2023 Sales', 'Growth %'])
ws.append(['Product X', 1000, 1500, 50])
ws.append(['Product Y', 800, 1200, 50])
ws.append(['Product Z', 1200, 1800, 50])

# Create chart with multiple series
multi_chart = BarChart()
multi_chart.title = "Year-over-Year Sales Comparison"
multi_chart.type = "col"  # Column chart

# Add multiple data series
sales_data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
multi_chart.add_data(sales_data, titles_from_data=True)

# Set categories
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
multi_chart.set_categories(categories)

ws.add_chart(multi_chart, "E15")
wb.save("multi_series_chart.xlsx")

Multiple series help compare related datasets. The add_data method handles this automatically when titles are included.

Best Practices for Chart Automation

Follow these practices for reliable chart creation. They ensure maintainable and error-free code.

Always define data ranges carefully. Incorrect ranges cause empty charts. Use Reference with precise coordinates.

Use descriptive chart titles. Clear titles make charts self-explanatory. They improve communication effectiveness.

Position charts strategically. Avoid overlapping with data. Leave enough space for chart display.

For working with multiple sheets, check our Openpyxl Load Workbook Iterate Sheets Guide.

Conclusion

Python openpyxl transforms Excel chart creation. It automates repetitive tasks efficiently. You save time and reduce errors.

You learned to create bar, line, and pie charts. Also how to edit existing charts. Customization options are extensive.

Start with simple charts. Then progress to complex visualizations. Your Excel reports will become dynamic and professional.

Chart automation is a valuable skill. It enhances data analysis workflows. Openpyxl makes it accessible to all Python users.