Formatting & presenting reports
Charts & images
Native Excel charts, sparklines, matplotlib figures, logos and pictures in cells.
Start with the full guides
The long-form treatment of charts & images, with the reasoning the shorter pages assume.
- GuideCreating Charts in Excel with openpyxlBuild native, editable Excel charts from Python with openpyxl: bind data with Reference and Series, make bar, line, and pie charts, add titles and legends, and anchor them.
- GuideInserting Images and Logos into ExcelEmbed logos and images into Excel with openpyxl: floating drawings anchored to cells, pixel resizing, header bands, and why pandas can't keep them.
- GuideBuilding Multi-Sheet Excel DashboardsBuild a multi-sheet Excel dashboard in Python: a Summary tab with KPIs and a chart, detail sheets behind it, cross-sheet hyperlinks, frozen headers, and tab order.
- OverviewFormatting and Charting Excel Reports with PythonTurn plain pandas output into branded, stakeholder-ready Excel reports: cell styling, number and date formats, charts, and embedded logos with openpyxl.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toBuild a Dashboard Sheet with Charts from Multiple TabsAssemble one dashboard page whose charts read from other sheets: cross-sheet series references sized from the data, cell-anchored layout, and the openpyxl equivalent.
- How-toBuild a Rolling 12-Month Chart on an Excel DashboardGive an Excel dashboard a chart that always shows the latest twelve months, built from Python with openpyxl: a helper block of EDATE and SUMIFS formulas anchored on the newest month in the data, an optional override cell for looking back, a prior-year comparison line, and checks with LibreOffice that the window moves when new months arrive.
- How-toKeep Charts and Images When Filling an Excel Templateopenpyxl drops charts, images and pivot tables on re-save. Why it happens, which objects survive, and three ways to fill a template without losing its visuals.
- How-toAdd a Chart to an Excel File with xlsxwriterInsert a real Excel chart from Python: series built from row counts, axis and label formatting, a combined column-and-line chart on a secondary axis, and placement that survives changing data.
- How-toAdd Sparklines to an Excel Report with xlsxwriterPut a trend in every row: xlsxwriter sparklines — line, column and win/loss types, high and low markers, shared axes across rows, styling, and the openpyxl caveat.
- How-toBuild a Waterfall Chart in Excel with PythonShow how an opening figure becomes a closing one — revenue bridges, budget walks, cash movements — as a waterfall chart built with xlsxwriter. Uses the stacked-column technique with an invisible base, formula-driven helper columns that update when inputs change, green and red steps, total bars and value labels.
- How-toLabel Chart Points from Cell Values with XlsxWriterPut product names, regions or comments on chart points instead of bare numbers: XlsxWriter custom data labels that reference cells (shown by Excel, ignored by LibreOffice), a portable one-series-per-point technique whose series-name labels render everywhere, labelling only the points that matter, labels from a different column on a column chart, and a test that the labels are really drawn.
- How-toAdd a Combo Chart with a Secondary Axis in openpyxlPlot revenue as bars and margin as a line on their own axis — combining chart objects, the y_axis.axId and crosses settings, and when a second axis misleads.
- How-toAdd a Data Table Below an Excel ChartShow the numbers behind a chart in a grid directly underneath it — Excel's chart data table — from Python: switch it on with openpyxl's DataTable or XlsxWriter's set_table, choose borders and legend keys, drop the separate legend, carry the cells' number formats into the table, and check the rendered result. Includes when a data table helps and when data labels or a separate table are better.
- How-toAdd a Trendline to an Excel Chart with PythonAdd linear, moving-average, polynomial and exponential trendlines to Excel line and column charts with openpyxl — forecast periods ahead, show the equation and R², style the line, and put the same fitted numbers into cells with SLOPE, INTERCEPT and RSQ so the trend can be read by people and by code.
- How-toAdd a Line Chart to an Excel Report with PythonAdd an editable line chart to an Excel report with openpyxl: write time-series data, plot multiple series, set a date x-axis, add markers, label axes, and anchor it.
- How-toCreate a Box and Whisker Chart in Excel with PythonExcel's built-in box-and-whisker chart cannot be written by openpyxl or XlsxWriter, but a native, editable box plot can be built from a stock chart: compute quartiles and whisker ends with pandas, map them to open, high, low and close, let up-down bars draw the boxes and high-low lines the whiskers, add a median marker, and list outliers beside the chart.
- How-toCreate a Bubble Chart in Excel with openpyxlPlot three measures at once — margin across, growth up, revenue as bubble size — to see a product portfolio or customer list at a glance. Build a native Excel bubble chart with openpyxl, add one series per group, size bubbles by area, scale them, label each bubble with its name, and keep axis labels clear of negative values.
- How-toCreate a Bullet Chart in Excel with PythonBuild a bullet chart — qualitative bands, an actual-value bar and a target marker — as a native Excel chart from Python: normalise KPIs with different units, draw the bands as stacked columns, draw the actual as a thick error bar and the target as a dash marker on the same axis, tidy the legend and axis, and check the rendered result. Includes why the common secondary-axis recipe is fragile.
- How-toCreate a Butterfly Chart in Excel with PythonBuild a butterfly (tornado, population-pyramid) chart as a native Excel bar chart with openpyxl: plot one side as negative values, overlap the two series on the same rows, hide the minus signs with a two-part number format, move the category labels to the edge, show positive data labels, colour each side, and check the rendered result.
- How-toCreate a Doughnut Chart with openpyxlBuild an Excel doughnut chart from Python: data and categories from a sheet, the hole size, one colour per slice, percentage labels that are readable on dark slices, a start angle, two rings to compare periods, the total shown in the title, and when a bar chart would say it better.
- How-toCreate a Dumbbell Chart in Excel with PythonBuild a dumbbell chart — two dots per category joined by a line, showing a before and after value — as a native Excel chart with openpyxl: a line chart with the lines switched off and markers on, the connecting bars drawn as custom error bars from a hidden helper series, categories sorted by the size of the change, the helper hidden from the legend, and the result rendered to check it.
- How-toCreate a Funnel Chart in Excel with PythonDraw a conversion funnel — visitors to orders, leads to deals, applicants to hires — as an Excel chart from Python: step and overall conversion rates with pandas, a centred stacked bar chart with an invisible spacer series in openpyxl, labels that carry counts and rates, stages top to bottom, and why the native funnel chart type is out of reach.
- How-toCreate a Gantt Chart in Excel with PythonDraw a project or month-end close timeline as a native Excel Gantt chart from a task list. Build it from a stacked bar chart with an invisible start series, put the first task at the top and the dates along the bottom, fix the date range, shade progress, and — as an alternative — paint a Gantt grid with conditional formatting.
- How-toCreate a Gauge Chart in Excel with PythonBuild a speedometer-style KPI gauge from Python with openpyxl: a half-circle doughnut made by hiding half of each ring, coloured red, amber and green bands from your thresholds, a value arc that moves when the KPI cell changes, a helper table of formulas behind it, several gauges on one dashboard, and a render check.
- How-toCreate a Histogram in Excel with PythonShow how values are distributed — order sizes, delivery times, invoice amounts — as a histogram in an Excel report. Choose bin edges with numpy, count values per bin, label bins the way readers expect, draw them as touching columns with openpyxl or xlsxwriter, and keep a live FREQUENCY version for data that changes.
- How-toCreate a Pie Chart in Excel with openpyxlPieChart, data labels that show percentages, exploding a slice, a doughnut variant — and the honest guidance on when a bar chart tells the same story better.
- How-toCreate a Radar Chart with openpyxlCompare 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.
- How-toCreate a Scatter Chart in Excel with openpyxlPlot one numeric column against another with openpyxl: ScatterChart and Series, markers without lines, axis titles and scaling, a trendline, and multiple series.
- How-toCreate a Slope Chart in Excel with PythonBuild a slope chart in Excel with openpyxl to compare each item between two periods: lay the data out with one series per item, draw a line chart with only two category points, colour rising and falling items differently, label each line at its ends instead of using a legend, tidy the axes, and check the rendered chart.
- How-toCreate a Stacked Column Chart with openpyxlBuild stacked and 100% stacked column charts in Excel with openpyxl — grouping and overlap settings, series colours, value labels inside each segment, a total label above every stack using an invisible line series, hiding its legend entry, and horizontal stacked bars.
- How-toCreate a Step Chart in Excel with PythonDraw values that change at specific moments and then hold — prices, interest rates, headcount, stock levels — as a step chart in Excel from Python: why a normal line chart misleads with diagonal ramps, how to double up the change points with pandas, build the chart as an openpyxl scatter chart on a real date axis, carry the last value to the end of the period, and compare several series.
- How-toCreate a Stock Chart with openpyxlDraw an open-high-low-close candlestick chart in Excel from Python: the column order Excel requires, hiding the series lines, high-low lines, up and down bars in your own colours, a value axis that does not start at zero, trading days without weekend gaps, and a volume chart underneath.
- How-toCreate a Waffle or Progress Chart in ExcelShow progress towards 100% — share of a target reached, budget used, survey completion — as a waffle chart of 100 cells or as a progress bar, built from worksheet cells and conditional formatting with openpyxl rather than a chart object: number the grid so it fills from the bottom, colour cells with one formula rule driven by the percentage cell, hide the numbers, add data-bar progress bars with fixed 0–100% limits, and render the sheet to check.
- How-toCreate an Area Chart in Excel with openpyxlShow how a total builds up over time — revenue by region, hours by project, stock by warehouse — with a native Excel area chart. Build standard, stacked and 100% stacked area charts with openpyxl, make overlapping areas see-through, keep axes visible in Excel, order series so small ones are not hidden, and handle gaps in the data.
- How-toCreate a Bar Chart in Excel with openpyxlBuild a native, editable Excel bar chart from Python with openpyxl: write sample data, bind it with Reference, set categories and titles, pick a style, and anchor it.
- How-toCreate One Chart per Group with openpyxlTurn a long table — sales by region and month — into a grid of small charts, one per region, generated in a loop: write each group as its own block, build a chart that references only that block, lay the charts out in a grid with computed anchors, give every chart the same axis scale so they can be compared, or put each group on its own sheet with its chart.
- How-toFormat Chart Axes with openpyxlMake the axes of an openpyxl chart say what you mean: keep them visible in Excel, title them, fix the scale and the step between labels, show thousands as "k", switch gridlines on or off, use a logarithmic scale for fast growth, rotate crowded category labels, and move labels clear of negative bars.
- How-toMake an Excel Chart Grow with Its DataA chart points at fixed cell ranges, so rows appended by a script fall outside it. See the problem in a saved file, extend every series of an existing chart to the new last row with openpyxl, rebuild the chart from the data's current size instead, let Excel tables carry the chart when rows are added inside Excel, and know why dynamic named ranges are brittle in generated files.
- How-toPlot a Chart from Non-Adjacent ColumnsChart only some of a sheet's columns — dates from column A, actual from D and forecast from G — without copying data around: add each series from its own Reference with openpyxl or its own add_series call in XlsxWriter, take series names from the header cells, find columns by heading instead of letter, keep a chart working when columns in between are hidden, and check the series references in the chart XML.
- How-toPut a Chart on Its Own Chartsheet with openpyxlGive a chart a whole sheet to itself — a full-screen, full-page chart tab that opens first in the workbook, prints on one page and cannot be dragged out of place. Create chartsheets with openpyxl, size the chart so every application shows it properly, make one per region in a loop, colour and order the tabs, protect them and set landscape printing.
- How-toShade a Target Band Behind a Line ChartShow a target range — on-time delivery between 95% and 98%, stock cover between two and four weeks — as a shaded band behind a line in a native Excel chart built with openpyxl: a stacked area chart with an invisible lower series and a coloured band series, a line chart combined on the same axes, band limits driven by two input cells, the helper series removed from the legend, and the weeks outside the band listed next to the chart.
- How-toShow Negative Bars in a Different ColourColour the negative bars of a variance or profit chart red and the positive ones green, from Python: split the values into two helper series that update with the data, colour individual data points when the values are fixed, or use XlsxWriter's invert-if-negative colour for Excel 2010 and later — with what each approach writes into the file, how to check it, and why openpyxl's invertIfNegative on its own turns negative bars white.
- How-toStyle Chart Colours and Data Labels with openpyxlReplace Excel's default chart styling from Python — series fill and outline colours, one highlighted bar, dashed target lines, subtle gridlines, bold coloured data labels, label number formats that survive every viewer, and a single palette function so every chart in a report matches.
- How-toUse a Date Axis in an Excel Chart with openpyxlPlot values against real time instead of evenly spaced labels: why irregular dates need a date axis, how to set one up in openpyxl with DateAxis and crossAx, tick units and label formats, straight lines instead of smoothed ones, when to keep a text axis, and why readings within a day need a scatter chart instead.
- How-toUse Multi-Level Category Labels in an Excel ChartLabel a chart's category axis in two tiers — quarters grouped under years, months under quarters, products under regions — with openpyxl: write the outer label only on the first row of each group, point the categories at both columns as a multi-level string reference, and see why repeated outer labels break the grouping. Rendered to check, with the XlsxWriter equivalent.
- How-toAdd a Watermark to an Excel Sheet with PythonMark a report as DRAFT or CONFIDENTIAL from Python: make a transparent watermark image with Pillow, place it in the page header with xlsxwriter so it prints on every page, use a sheet background for on-screen marking, stamp the exported PDF with pypdf when the PDF is what gets shared, and avoid the openpyxl re-save that silently removes header images.
- How-toAdd a Logo Image to an Excel Report with openpyxlStep-by-step openpyxl guide to add a logo to an Excel report: create the image, anchor it at A1, size it to a header band, add a merged title, and save.
- How-toBuild a Product Catalog with Images in ExcelTurn a product list and a folder of photos into a catalogue workbook: shrink each photo to a thumbnail before inserting it, keep its aspect ratio, centre it in a fixed-size cell, handle products without a photo, group by category, and anchor images so they move and hide with their rows when the sheet is sorted or filtered.
- How-toEmbed a matplotlib Chart in an Excel ReportRender a figure to PNG and anchor it in a worksheet: sizing and DPI that stay sharp, in-memory buffers so nothing hits the disk, keeping the source data on a sheet, and when a native chart is the better answer.
- How-toEmbed Images Inside Cells with xlsxwriterPut a product photo or status icon inside a cell rather than floating over the grid, so it sorts, filters and resizes with its row. Use xlsxwriter's embed_image, and know how it differs from insert_image.
- How-toExport an Excel Chart as an Image with PythonTurn a chart inside an Excel workbook into a PNG for an email, a slide or a web page — with Excel itself through COM on Windows, with headless LibreOffice on any server plus a Pillow crop, or by redrawing the chart from the same data with matplotlib — and choose by fidelity, platform and resolution.
- How-toExtract Images from an Excel File with PythonPull every picture out of an .xlsx — all at once from the zip archive, floating images with the cell they sit on through openpyxl, and pictures placed in cells by following the rich-value parts.
- How-toGenerate QR Codes and Barcodes in Excel with PythonPut a scannable QR code and a Code 128 barcode next to every row of an asset or stock list: generate the images in memory with qrcode and python-barcode, anchor them in cells with openpyxl, size rows and columns to fit, and prove every code still scans after printing by decoding the rendered PDF.
- How-toInsert an Image from Memory into Excel with PythonPut images into a workbook straight from bytes — a logo downloaded over HTTP, a base64 string from an API, a photo stored in a database, a picture resized with Pillow — without writing temporary files, using openpyxl or XlsxWriter, and avoid the closed-buffer and format errors that only appear at save time.
- How-toRender an Excel Range as an Image with PythonTurn a formatted block of cells — a KPI table, a summary, a small report — into a PNG for an email body, a chat message or a slide: LibreOffice on Linux via a print area and a PDF, cropped with Pillow at any resolution; xlwings to_png on Windows with Excel; and when a pure-Python rendering is enough.
- How-toResize and Position Images in Excel with openpyxlPlace a logo or chart image exactly where you want it — pixel sizing, aspect ratio, cell and offset anchors, and why an image moves when a column is resized.
- How-toRun a Pareto Analysis on Excel Data with pandasFind the handful of customers or products that make up 80% of revenue. Aggregate, sort, compute cumulative shares, classify items into A, B and C with a clear rule for the item that crosses each threshold, and write the table with a native Excel Pareto chart — columns plus a cumulative line on a secondary axis.
- How-toAdd a Chart to an Excel Online Workbook with Microsoft GraphCreate and maintain native charts in a workbook stored in OneDrive or SharePoint without downloading it: call the Graph charts/add endpoint with a type and source range, set the title and position, point an existing chart at new data with setData, fetch a PNG of the chart for an email, and run it all inside a workbook session — with the request code tested against a mocked Graph endpoint.
- How-toBuild a Project Budget Burn ReportTrack how fast a project is spending its budget with pandas and openpyxl: combine timesheet costs and supplier invoices into weekly actuals, compare cumulative spend with a time-phased budget, add earned value with CPI and SPI from percent complete, forecast the cost at completion, flag workstreams at risk, and chart burn against plan in Excel.
- How-toBuild a One-Page Executive Summary SheetBuild the first sheet of a monthly Excel report as a one-page executive summary with Python: four headline figures against budget and last year, three sentences of commentary written from the numbers, a small trend chart, the biggest movers, and page setup that prints on exactly one A4 sheet — checked by converting it to PDF.
- How-toLay Out a Dashboard Sheet on a GridLay out a generated Excel dashboard on a regular grid with openpyxl: set uniform column widths and row heights, describe every tile and chart by grid position and span, snap charts to cell boundaries with two-cell anchors so they line up and resize with the grid, and check the layout for overlaps before anyone opens the file.
- How-toEmbed a Chart Image in the Body of a Report EmailShow the report chart right in the email instead of hiding it in an attachment: render the workbook's own Excel chart to a sharp PNG through LibreOffice and pypdfium2 (or draw it with matplotlib), size it for phones and high-DPI screens, keep it light and dark-mode safe, embed several images by Content-ID, and test that every image reference resolves.
- How-toTurn PDF Report Pages into ImagesRender the pages of a PDF exported from an Excel report as PNG or JPEG images with pypdfium2, which installs with pip and needs no Poppler: choose the resolution for screen, email or print, trim the white margins, make small thumbnails for a report index, and compare file sizes and formats — with pdftoppm as the command-line alternative.
- How-toGenerate PowerPoint Slides from Excel DataBuild the monthly deck from the spreadsheet instead of by copy and paste: python-pptx slides with a title per region, native editable PowerPoint charts from pandas summaries, a formatted table, a KPI headline computed from the data, a company template's layouts, and a check that renders the deck to PDF.
- How-toTest That Excel Charts Were Written CorrectlyWrite pytest tests for the charts a report script produces: since openpyxl does not load charts back, read them straight from the chart XML inside the .xlsx — chart type, title, series names, value and category ranges, axis settings and which sheet and cell each chart sits on — compare them with expectations or a stored snapshot, and catch the regressions that leave a chart empty, pointing at the wrong column, or with hidden axes.
- How-toCombine Chart Types and Chartsheets with XlsxWriterBuild combination charts and full-page chartsheets with XlsxWriter: columns and a line in one chart with the line on a secondary axis, a stacked column chart with a total line, charts fed from a pandas summary written to a data sheet, chartsheets that show one chart per tab, and checks of the generated chart XML so a broken chart is caught before anyone opens the file.
- How-toInsert a matplotlib Plot into a Live Workbook with xlwingsPut a matplotlib chart into a workbook that is open in Excel and keep it up to date: read the data with xlwings, build the figure, add it with sheet.pictures.add, place it next to a cell with anchor, replace it in place on every run with name and update=True, choose PNG resolution or SVG, and size it so it looks right on screen and in print.
- How-toPlot Charts with matplotlib in Python in ExcelDraw matplotlib and seaborn charts inside a =PY cell, show them in the cell or floating over the grid, size them properly, and know when a native Excel chart is the better choice.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
Follow it through a track
Prefer an ordered route? These tracks cover charts & images along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Charts & images is part of Formatting & presenting reports.