Python Automation – Excel Charts Creation with Threshold / Target Range / Bands in the Background

Python automation for creating Excel charts with threshold, target range, or bands in the background involves using Python code to generate charts in Excel that visually represent data. These charts can help in analyzing trends, setting goals, or monitoring progress.

In this process, Python libraries like openpyxl and xlwings are often used to interact with Excel files. Data is first prepared or imported into an Excel sheet using Python, and then a chart is created based on this data. The chart is customized to include a threshold, target range, or bands in the background, which can be used to highlight specific values or goals.

This automation is useful in various scenarios, such as sales tracking, performance monitoring, or project management, where visual representations of data help in better understanding and decision-making. By automating this process, repetitive tasks can be eliminated, and charts can be generated quickly and consistently.

Let us get into the code know. Create a python file with .py extension and add the below code to it , you can also take your own excel sheet to perform the same.

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Insert data into the Excel sheet
data = [
    ['Month', 'Sales'],
    ['Jan', 100],
    ['Feb', 120],
    ['Mar', 130],
    ['Apr', 110],
    ['May', 105],
    ['Jun', 115],
]
for row in data:
    ws.append(row)

# Create a line chart
chart = LineChart()
chart.title = "Sales Trend"
chart.y_axis.title = "Sales"
chart.x_axis.title = "Month"

# Add data to the chart
data = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=7)
categories = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# Add a threshold or target range
threshold = 120
chart.threshold = threshold

# Add the chart to the worksheet
ws.add_chart(chart, "E5")

# Save the workbook
wb.save('chart_with_threshold.xlsx')

Read More