Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Radar Chart with openpyxl

Compare several options across the same criteria — vendors, products, candidates, regions — with an Excel radar (spider) chart built from Python: series from a scoring table, a fixed 0–5 scale, visible axes, marker and filled styles, one colour per option, and a weighted score table next to it so the chart is not the only basis for a decision.
Charts & imagesopenpyxl

A radar chart — also called a spider or web chart — puts each criterion on its own spoke around a circle and draws every option as a shape joining its scores. It is the standard picture for a vendor evaluation, a product comparison or a skills assessment: one glance shows which option is strong where, and whether one dominates the others or they trade off. openpyxl builds it as a native Excel chart. Getting a fair picture takes a few settings the defaults do not provide: a scale fixed to the scoring range, axes that Excel actually displays, and distinct colours. This page, part of Creating Charts in Excel with openpyxl, builds one for a three-vendor evaluation.

Prerequisites

Bash
pip install openpyxl pandas

vendor_scores.xlsx has a Scores sheet with six criteria down the side, a weight for each, and scores from 0 to 5 for three vendors. The chart plots the three score columns; the weights feed the table in Step 5.

The scoring table A table of six criteria with weights and scores from 0 to 5 for vendors A, B and C. The three vendor columns are the chart's series and the criteria are its spokes. vendor_scores.xlsx A B C D E 1 Criterion Weight Vendor A Vendor B Vendor C 2 Price 0.25 4 3 5 3 Features 0.20 5 4 2 4 Support 0.15 3 5 3 5 Security 0.20 4 4 3 6 Integration 0.10 5 2 3 7 Usability 0.10 3 4 4

Step 1 — Build the chart from the table

Each vendor column is a series and the criteria are the categories, which become the spokes:

Python
from openpyxl import load_workbook
from openpyxl.chart import RadarChart, Reference

wb = load_workbook("vendor_scores.xlsx")
ws = wb["Scores"]
chart = RadarChart()
chart.type = "marker"
chart.title = "Vendor evaluation (0–5)"
chart.add_data(Reference(ws, min_col=3, max_col=5, min_row=1, max_row=7), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=7))
chart.width, chart.height = 14, 10
print(len(chart.series), "series")

titles_from_data=True takes each series name from the header row, so the legend says "Vendor A" rather than "Series1". type chooses the style: "standard" draws lines, "marker" adds a dot at each score, and "filled" fills each shape. With several series, filled shapes hide each other — the last series drawn covers the earlier ones — so "marker" is the better default for comparisons.

Step 2 — Fix the scale and show the axes

Left alone, Excel scales the value axis to the data, so if every score happens to be 2 or more, the centre of the chart is 2, not 0, and differences look larger than they are. Fix the axis to the scoring range, and tell Excel to display the axes — openpyxl writes them marked as deleted by default, which Excel honours and LibreOffice ignores:

Python
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 5
chart.y_axis.majorUnit = 1
chart.y_axis.delete = False
chart.x_axis.delete = False

A fixed 0–5 scale means a vendor scoring 5 everywhere draws the outer ring and a vendor scoring 0 a dot at the centre — the picture means the same thing in every evaluation, which matters when people compare charts from different reports. The delete = False lines are the difference between a chart with labelled spokes and gridline values in Excel and a floating shape with no reference.

Auto scale and fixed scale Two panels. With an automatic scale the centre of the chart is set by the data and small differences look large. With the scale fixed to 0 to 5 the centre is always zero and shapes are comparable between charts. Automatic scale centre moves with the data small gaps look dramatic charts not comparable Fixed 0–5 centre is always 0 outer ring is always 5 same meaning in every report

Step 3 — One colour and marker per vendor

Give each series a distinct line colour, a slightly thicker line and matching markers, so vendors stay distinguishable in print and for colour-blind readers:

Python
COLOURS = {"Vendor A": "4338CA", "Vendor B": "0F9488", "Vendor C": "B4740A"}
SYMBOLS = {"Vendor A": "circle", "Vendor B": "square", "Vendor C": "triangle"}

for series, name in zip(chart.series, COLOURS):
    colour = COLOURS[name]
    series.graphicalProperties.line.solidFill = colour
    series.graphicalProperties.line.width = 28575          # 2.25 pt, in EMU
    series.marker.symbol = SYMBOLS[name]
    series.marker.size = 7
    series.marker.graphicalProperties.solidFill = colour
    series.marker.graphicalProperties.line.solidFill = colour

ws.add_chart(chart, "A10")
wb.save("vendor_scores.xlsx")
print([s.marker.symbol for s in chart.series])

Line widths are in EMU (English Metric Units): 12,700 per point. Different marker shapes as well as different colours keep the series apart when the chart is printed in grey. Anchoring the chart at A10, below the table, keeps it within a printed page's width.

Step 4 — Check the rendering

Convert the workbook to PDF with LibreOffice and look at the chart:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf vendor_scores.xlsx > /dev/null 2>&1
pdftoppm -png -r 60 -f 1 -l 1 vendor_scores.pdf preview && ls preview*

The rendering shows the trade-offs directly. Vendor A reaches the outer ring on features and integration; Vendor B is strongest on support and weakest on integration; Vendor C wins on price and loses on features. No vendor encloses the others — which is exactly why the next step is needed.

Step 5 — Add a weighted score next to the chart

A radar chart shows shapes, and people read area as quality — but area depends on the order of the spokes, and every criterion counts equally in it even when the weights say otherwise. Put the weighted total next to the chart, as the number the decision rests on:

Python
import pandas as pd

scores = pd.read_excel("vendor_scores.xlsx", sheet_name="Scores")
vendors = ["Vendor A", "Vendor B", "Vendor C"]
weighted = (scores[vendors].mul(scores["Weight"], axis=0)).sum().round(2)
ranking = weighted.sort_values(ascending=False).rename("Weighted score").to_frame()
print(ranking)

wb = load_workbook("vendor_scores.xlsx")
ws = wb["Scores"]
ws["H1"], ws["I1"] = "Vendor", "Weighted score (0–5)"
for i, (vendor, row) in enumerate(ranking.iterrows(), start=2):
    ws.cell(i, 8, vendor)
    ws.cell(i, 9, f"=SUMPRODUCT($B$2:$B$7,{'CDE'[vendors.index(vendor)]}2:{'CDE'[vendors.index(vendor)]}7)")
wb.save("vendor_scores_ranked.xlsx")

Check that the formulas agree with pandas by letting LibreOffice calculate a copy:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated vendor_scores_ranked.xlsx > /dev/null 2>&1
ls recalculated
Python
check = load_workbook("recalculated/vendor_scores_ranked.xlsx", data_only=True)["Scores"]
print([(check.cell(r, 8).value, round(check.cell(r, 9).value, 2)) for r in range(2, 5)])

The formulas give the same 4.05, 3.70 and 3.40, in the same order. Vendor A leads because it is strong on the two heaviest criteria, price and features, while Vendor B's best score is on support, which carries less weight — something the radar shapes alone do not reveal.

The ranking is computed in pandas to sort it, and written back as SUMPRODUCT formulas so a reviewer can change a weight on the sheet and see the scores update. Reopening a workbook with openpyxl drops its existing charts, so this version is saved under a new name; in a real script, build the chart and the table in the same run before saving. SUMPRODUCT and Weighted Average in pandas covers the calculation.

Weighted scores out of 5 Horizontal bars of the weighted scores. Vendor A scores 4.05, Vendor B 3.70 and Vendor C 3.40. Vendor A 4.05 Vendor B 3.70 Vendor C 3.40 Price and features carry the most weight, which favours Vendor A.

When a radar chart is the wrong choice

Radar charts work for a handful of options on five to eight criteria that share one scale. With more options the shapes turn into a tangle; with criteria on different scales — cost in euros next to a satisfaction score — the shape is meaningless. For ranking, a sorted bar chart of the weighted score is clearer; for seeing each criterion, a grouped bar chart puts the scores side by side on a straight axis, which people judge more accurately than distances along spokes.

Performance notes

Charts cost almost nothing to write: openpyxl stores a reference to the cells, not the data, so a radar chart over six criteria and three vendors adds a few kilobytes of XML regardless of how the numbers change. What does take time is producing many of them — one chart per candidate in a hiring review, say. In that case generate the charts in one workbook, one per sheet, in a single run, and render the whole workbook to PDF in one LibreOffice call for checking. Keep each chart's data on the same sheet as the chart, or on a clearly named data sheet, so that reviewers who open the file can see where every point comes from.

Common pitfalls

  • Automatic scaling. Fix y_axis.scaling.min and max to the scoring range.
  • Hidden axes in Excel. Set x_axis.delete = False and y_axis.delete = False; LibreOffice shows axes regardless, so a LibreOffice preview can hide the problem.
  • Filled series covering each other. Use "marker" for more than one series.
  • Reading area as quality. Area depends on spoke order; show a weighted score alongside.
  • Re-saving with openpyxl. Loading a workbook drops its charts; add charts in the final save.

Conclusion

Build a RadarChart with one series per option and the criteria as categories, choose the "marker" type for comparisons, fix the value axis to the scoring range with delete = False on both axes, and give every series its own colour and marker shape. Render with LibreOffice to check it, and put a weighted score table next to the chart so the decision rests on the weights, not on the look of the shapes.

Frequently asked questions

How do I make a radar chart in Excel with Python? Use openpyxl's RadarChart: add the score columns with add_data(..., titles_from_data=True), set the criteria as categories with set_categories, and place it with ws.add_chart. Set chart.type to "marker", "standard" or "filled".

Why doesn't Excel show the axes on my openpyxl radar chart? openpyxl writes axes marked as deleted by default. Set chart.x_axis.delete = False and chart.y_axis.delete = False. LibreOffice displays the axes either way, so check in Excel.

How do I set the radar chart scale from 0 to 5? Set chart.y_axis.scaling.min = 0, chart.y_axis.scaling.max = 5 and chart.y_axis.majorUnit = 1 so the rings mark each score.

What is the difference between standard, marker and filled radar charts? Standard draws lines only, marker adds a point at each value, and filled shades each series' area. Filled charts work for one or two series; with more, later series cover earlier ones.

More on charts & images

More on charts & images →