Python Academy · Lesson

Formulas and Charts

Add formulas and charts.

Formulas and Charts is a free Python Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Beyond Static Values

Real spreadsheets calculate. openpyxl can write Excel formulas that recompute when data changes, and add charts that visualize the numbers directly inside the workbook.

This makes generated reports feel native to Excel users.

Writing a Formula

A formula is just a string starting with =. Assign it like any value: ws['B10'] = '=SUM(B2:B9)'. Excel evaluates it when the file opens.

openpyxl stores the formula text; it does not compute the result itself.

first_row, last_row = 2, 9
formula = '=SUM(B' + str(first_row) + ':B' + str(last_row) + ')'
print('Cell B10 ->', formula)

Common Functions

The everyday Excel functions all work as strings:

=SUM(range) total
=AVERAGE(range) mean
=MAX(range) / =MIN(range)
=COUNT(range) count of numbers

You compose them exactly as you would type them in Excel.

Formulas Are Not Evaluated in Python

Important: if you write a formula and then read it back with openpyxl without opening in Excel, you get the formula text, not the number. To read computed results, open the file in Excel first, then load with data_only=True.

Building Cell References

Generating formulas often means building references programmatically. Combining a column letter with a row number gives an address. This is plain string work.

col = 'C'
def cell_ref(c, r):
    return c + str(r)

total_formula = '=AVERAGE(' + cell_ref(col, 2) + ':' + cell_ref(col, 11) + ')'
print(total_formula)

Adding a Chart

openpyxl can embed charts. You create a chart object such as BarChart(), give it data via a Reference to a cell range, and add it to the sheet with ws.add_chart(chart, 'E2').

Chart types include bar, line, pie, and scatter.

References to Data

A Reference(ws, min_col=2, min_row=1, max_row=6) selects the cells the chart reads. Categories (the labels) come from another reference. Getting these ranges right is the key step.

data_rows = 6
ref = {'min_col': 2, 'min_row': 1, 'max_row': data_rows}
print('Chart reads column', ref['min_col'], 'rows', ref['min_row'], 'to', ref['max_row'])

Chart Titles and Axes

Set chart.title = 'Monthly Sales', and label axes via chart.x_axis.title and chart.y_axis.title. A labeled chart inside the report needs no separate explanation.

Choosing the Chart Type

The same advice as any visualization applies: bar for category comparison, line for trends, pie for parts of a whole. Pick the type that matches the question your report answers.

question = 'how did revenue change month over month'
if 'change' in question or 'trend' in question:
    print('Use a LineChart')
else:
    print('Use a BarChart')

Putting It Together

A typical report writes the data table, adds a total formula at the bottom, then places a chart referencing the same data. When opened, Excel shows live totals and a visual, all generated from Python.

Conditional Formulas

Excel logic functions work as strings too: =IF(B2>100, 'High', 'Low') or =SUMIF(A2:A9, 'North', B2:B9). These let your generated report classify and aggregate without extra Python passes.

Build them with the same string concatenation you use for any formula.

Quick Check

Test your formula and chart knowledge.

Recap

You can now add logic and visuals:

Write formulas as = strings; openpyxl stores but does not evaluate them
Use data_only=True after Excel computes results
Build references programmatically from column letters and row numbers
Embed charts with BarChart()/LineChart(), a Reference, and ws.add_chart

Frequently asked questions

Is the “Formulas and Charts” lesson free?

Yes — the full text of “Formulas and Charts” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Formulas and Charts”?

Add formulas and charts. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Formulas and Charts” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy