The slowest part of a monthly report is rarely the numbers — it is the charts. The figures are already calculated in Excel, but you still have to open a new sheet for every department, insert a chart, fix the colours, retitle it, then copy it into PowerPoint or post it to Facebook. By the time that is done, the afternoon is gone.
Worse, the moment next month's data arrives, every chart has to be rebuilt from scratch. The approach here uses Python's matplotlib to turn reading, plotting and saving into a single script. From then on you swap in a new Excel file, run it once, and every chart is regenerated with consistent filenames and dimensions.
Step 1: Install three libraries
Open Command Prompt and run:
Each has a job: pandas reads the Excel file, matplotlib draws the charts, and openpyxl is the background engine pandas needs to open .xlsx files — without it you will get an error.
Step 2: Fix Chinese text turning into empty boxes
The first time you print a Chinese title with matplotlib, you will most likely see a row of empty squares. The default font contains no Chinese glyphs. A few lines fix it:
The second line stops the minus sign on negative numbers from turning into a box. On a Mac, use "PingFang HK" instead.
Step 3: Read the numbers from Excel and draw one trend chart
Say you have sales.xlsx with two columns: month and revenue. Create make_charts.py and paste this in:
Line by line
- matplotlib.use("Agg") — draws without opening a window. Safe on a scheduled task or a machine with no display attached.
- figsize=(10, 5) — the canvas ratio, 10 inches wide by 5 tall; with dpi=150 that is roughly 1500×750 pixels, sharp enough for web and slides.
- marker="o" — puts a dot on every data point so readers can see the actual figure instead of guessing from the line.
- linewidth=3 — a thicker line. Thin lines vanish on projectors and phones.
- tight_layout() — trims the margins so titles and axis labels are not clipped.
- plt.savefig() — writes a PNG. Change it to chart_trend.svg for a vector file that stays sharp at any size.
Step 4: One for loop, a chart for every department
This is where the real saving is. Instead of opening Excel ten times and inserting ten charts, loop once and produce one chart per department, with the department name built into the filename:
plt.close() is the critical line: close the figure after every save. Skip it and a loop of dozens of charts keeps consuming memory until it crashes around the fifteenth one.
Pick the wrong chart type and the message distorts
The same data plotted the wrong way leads readers to the wrong conclusion. Four basic pairings:
- Trend → line chart, plt.plot. Continuous time series such as monthly revenue or daily site visitors.
- Comparison → bar chart, plt.bar. Branches or departments side by side, so the tallest is obvious.
- Share of a whole → pie chart, plt.pie — and only for five categories or fewer. Any more turns into a pile of slivers nobody can read.
- Relationship → scatter plot, plt.scatter. For example, whether ad spend and enquiries rise together.
Another common mistake: not starting the vertical axis at zero. Rather than exaggerating a gap visually, start at zero — if the difference looks small, it is small.
How much time it actually saves
Ten charts a month, five to eight minutes each by hand — open the file, insert the chart, fix colours, retitle, save — is about an hour. Once the script is written, you swap the Excel file, run it once, and all ten appear in roughly 15 seconds, formatted identically: no chart with a slightly bigger font, no chart in a different colour.
The bigger win is that nothing gets missed. Do it by hand ten times and one or two months will have a wrong month pasted in or a branch left out. With a script, as long as the source data is right, no chart is ever missing — the only remaining failure point is a mistake inside the spreadsheet itself.
Summary
Python Tutorial: you do not need to insert charts into Excel one by one — use matplotlib to turn reading, plotting and saving into a single script, then swap the Excel file each month and run it once. Ten charts in about fifteen seconds, consistent formatting, nothing forgotten. Don't overreach at the start: get one chart working, confirm the Chinese font and the output size look right, then add the loop for batch output.