Skip to content
Python Central Hub

Automating Excel Formulas and Charts

Add formulas

excel_formula.py
from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Qty", "Price", "Total"])
ws.append(["Pen", 10, 2.5, "=B2*C2"])
ws.append(["Book", 3, 12.0, "=B3*C3"])
 
wb.save("sales.xlsx")
excel_formula.py
from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Qty", "Price", "Total"])
ws.append(["Pen", 10, 2.5, "=B2*C2"])
ws.append(["Book", 3, 12.0, "=B3*C3"])
 
wb.save("sales.xlsx")

Create a chart

excel_chart.py
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Total"])
ws.append(["Pen", 25])
ws.append(["Book", 36])
ws.append(["Ruler", 15])
 
data = Reference(ws, min_col=2, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
 
chart = BarChart()
chart.title = "Totals"
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
 
ws.add_chart(chart, "D2")
wb.save("sales_chart.xlsx")
excel_chart.py
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
 
wb = Workbook()
ws = wb.active
ws.title = "Sales"
 
ws.append(["Item", "Total"])
ws.append(["Pen", 25])
ws.append(["Book", 36])
ws.append(["Ruler", 15])
 
data = Reference(ws, min_col=2, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
 
chart = BarChart()
chart.title = "Totals"
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
 
ws.add_chart(chart, "D2")
wb.save("sales_chart.xlsx")

Notes

  • Charts can be limited vs native Excel features
  • For heavy reporting, consider generating chart images (matplotlib) and embedding

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did