Formatting & presenting reports
Report layouts & templates
Multi-sheet dashboards, summary and contents sheets, and filling a designed template.
Start with the full guides
The long-form treatment of report layouts & templates, with the reasoning the shorter pages assume.
- GuideBuilding Finance and Operations Reports in Excel with PythonThe month-end pack, the aging report, the commission statement and the stock reorder list share one shape: exports in, reference data joined, a period chosen, numbers calculated, totals tied back to the source and exceptions surfaced first. This guide builds that shape once — a budget-versus-actual report from a ledger export — and shows how each finance and operations report reuses it.
- 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.
- GuideGenerating Excel Reports from TemplatesInject data into a prepared .xlsx with openpyxl instead of rebuilding sheets from scratch — preserve branding, formulas, and print areas, then save a dated copy.
- OverviewAutomating Reporting Workflows with Python and ExcelBuild an end-to-end Python reporting pipeline: ingest data, transform it with pandas, generate a styled Excel workbook, then schedule and email the result.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAllocate Shared Costs Across Departments with PythonSpread rent, IT, HR and finance costs over the departments that use them: choose a driver per cost pool, calculate shares, allocate with rounding that still adds up to the cent, compare the direct method with the step-down method for service departments, and write an allocation workbook with formulas finance can audit.
- How-toBuild a Bank Reconciliation Report with Python and ExcelMatch a month of bank statement lines to the cash book: first on cheque numbers and invoice references, then on amount within a few days, one to one. List what is left — outstanding cheques, deposits in transit, bank charges and interest — flag old unmatched items, and prove the adjusted bank and book balances agree.
- How-toBuild a Bill of Materials Cost RollupCalculate the standard cost of every assembled product from a multi-level bill of materials in Excel: walk the parent–component tree recursively with quantities, add each assembly's own labour and overhead, detect loops and components with no cost, show where a part is used, and work out what a supplier price increase does to every finished product — all with pandas and plain Python.
- How-toBuild a Break-Even Analysis in Excel with PythonBuild a live break-even workbook from Python: contribution margin, break-even units and revenue and margin of safety as Excel formulas driven by input cells, a multi-product break-even based on the sales mix, a price-by-cost sensitivity grid, and a cost-volume-profit chart, all checked against the same figures computed in pandas.
- How-toBuild a Capacity Planning ReportCompare the work in the order book with the hours each work centre can actually deliver, week by week, with pandas: turn orders into required hours through a routing table, calculate available capacity from shifts, efficiency, holidays and planned maintenance, show utilisation as a heatmap, find overloaded weeks, and test whether pulling work forward into spare weeks clears the backlog.
- How-toBuild a 13-Week Cash Flow Forecast Report in Excel with PythonTurn open receivables, payables and recurring commitments into a rolling 13-week cash forecast: expected receipt dates from each customer's payment history, weekly buckets, opening and closing balances, a minimum-cash warning, and an Excel report with live balance formulas and a chart.
- How-toBuild a Cash Position Report from Bank StatementsProduce a daily cash position across several banks and currencies from their statement exports with pandas: map each bank's own CSV layout to one standard, prove every statement adds up from opening to closing balance, convert to the reporting currency, compare each account with its minimum balance and overdraft limit, and write a one-page position with the largest movements of the day.
- How-toBuild a Credit Control Dunning Letter RunRun credit control reminders from an open receivables export with pandas: decide each customer's reminder level from their most overdue invoice and their letter history, leave out disputed invoices, payment plans and small balances, never escalate faster than the policy allows, produce a run workbook for review and a letter for each customer listing what they owe, and record what was sent.
- How-toBuild a Customer Credit Limit ReportCheck every customer's exposure against their credit limit with pandas: add open invoices to orders accepted but not yet invoiced, measure utilisation of the limit, combine it with how overdue the account is, decide which accounts to put on hold, which to review and which to leave alone, show what headroom remains for new orders, and write a colour-coded Excel report for the credit team.
- How-toBuild a Customer Profitability ReportFind out which customers actually make money with pandas: start from revenue and product margin, then subtract the costs that differ by customer — discounts and rebates, delivery, the cost of handling many small orders and returns, and the cost of slow payment — build a profit waterfall for each customer and a whale curve for the portfolio, and write a ranked Excel report.
- How-toBuild a Deferred Revenue ScheduleSpread prepaid subscription and contract revenue over the service period with pandas: daily pro-rata recognition for contracts that start mid-month, a contract-by-month schedule, the deferred revenue balance at each month end, the monthly journal, a check that invoiced equals recognised plus deferred, and the schedule written to Excel.
- How-toBuild a Discount Leakage ReportFind out how much margin is lost to discounts beyond policy with pandas: compare every invoice line's discount with the approved maximum for the customer tier and product group, value the excess, separate approved exceptions from unapproved ones, show who and which customers account for it, measure how widely discounts vary for the same product and tier, and write the findings to Excel.
- How-toBuild a Fixed Asset Depreciation Schedule with PythonTurn a fixed asset register into a monthly depreciation schedule: straight-line and double-declining balance with the switch to straight line, a start-of-depreciation convention, disposals part way through the year, net book values, a cost and depreciation roll-forward by category, and a check against Excel's SLN and VDB functions.
- How-toBuild a Fleet Mileage and Fuel ReportTurn a fuel card export into a fleet report with pandas: distance driven from odometer readings at each fill-up, fuel economy per vehicle in litres per 100 km and miles per gallon, cost per kilometre, and the checks fleet managers rely on — odometers that go backwards, fills larger than the tank, the wrong fuel for the vehicle, and vehicles whose economy suddenly gets worse — written to a monthly Excel report.
- How-toBuild a Foreign Exchange Revaluation ReportRevalue open foreign-currency invoices and bank balances at the month-end rate with pandas: join each item to its booking rate and the closing rate, calculate the unrealised gain or loss per item and per currency, get the sign right for receivables, payables and cash, produce the revaluation journal and its reversal, and write a report that reconciles to the ledger.
- How-toBuild a Headcount and Attrition Report in Excel with PythonTurn an HR export of hire and leave dates into a monthly headcount and attrition report: point-in-time headcount at each month end, hires and leavers, attrition against average headcount, a rolling twelve-month rate, voluntary versus involuntary exits, first-year leavers, and a department breakdown in Excel with a chart.
- How-toBuild a Lease Liability ScheduleCalculate a lease liability and right-of-use asset the way IFRS 16 does, with pandas: discount the payments at the incremental borrowing rate, unwind interest month by month with payments in advance, depreciate the asset straight line, split the liability into current and non-current portions, produce the month's journals and a maturity analysis, and check the opening liability against Excel's PV function.
- How-toBuild a Loan Amortization Schedule in Excel with PythonGenerate a month-by-month repayment schedule: the payment from the annuity formula, interest and principal split per period, cent rounding and a final payment that clears the balance exactly, overpayments that shorten the term, totals and payoff date, and a workbook with live PMT formulas checked against the Python result.
- How-toBuild a Loan Covenant Compliance ReportTest bank loan covenants every quarter with pandas: last-twelve-month EBITDA and interest from monthly accounts, net debt at the quarter end, leverage, interest cover and minimum liquidity against their limits, the headroom on each covenant expressed as how far EBITDA or cash could fall before a breach, a forward look using the forecast, and a compliance workbook that shows its formulas.
- How-toBuild a Maintenance Schedule in ExcelPlan preventive maintenance for machines and vehicles with pandas: tasks due every so many days or every so many running hours, next due dates from the last service and the average daily usage, overdue jobs, every occurrence over the next twelve weeks, the weekly workload against technician hours, and a week-by-asset grid in Excel with the due jobs marked.
- How-toBuild a Month-End Close Checklist TrackerTurn a month-end close checklist into a tracker that updates itself: express each task's deadline as a working day relative to month end, convert those to real dates with a holiday calendar, read the team's status updates, flag overdue tasks and tasks blocked by unfinished dependencies, measure progress against plan day by day, and write a colour-coded Excel tracker.
- How-toBuild a Monthly KPI Scorecard with TargetsBuild a one-page monthly KPI scorecard in Excel with pandas and XlsxWriter: KPI definitions with targets, direction and tolerance kept as data, red-amber-green status that knows when lower is better, variance against target and last month, year-to-date figures that sum or average correctly, six-month sparklines, and a status check that matches the colours.
- How-toBuild a Price-Volume-Mix AnalysisExplain why revenue changed between two periods by splitting the change into price, volume and mix effects with pandas: per-product price and quantity, the standard PVM formulas, separate lines for new and discontinued products, a check that the effects add up exactly to the change, a breakdown by product, and an Excel report with a bridge table.
- How-toBuild a Product Margin ReportWork out what each product and customer really earns from sales lines with pandas: net revenue after discounts and returns, the standard cost in force on each sale date with merge_asof, freight allocated by weight, gross margin by product, category and customer, loss-making lines, a price-versus-cost explanation of margin change, and an Excel report with margin colour coding.
- How-toBuild a Production Yield and Scrap ReportTurn production order records into a yield and scrap report with pandas: yield and first-pass yield per line, product and shift, scrap units and scrap cost at standard cost, a Pareto of scrap reasons showing the few that matter, weekly trends against a target, and checks that the recorded quantities add up — written to an Excel report for the production meeting.
- How-toBuild a Profit and Loss Statement from a General LedgerTurn a general ledger export into a monthly profit and loss statement in Excel: check the ledger balances, map account numbers to P&L lines with a chart of accounts, flip signs so revenue and costs read naturally, add gross, operating and net profit with margins, and write a workbook with collapsible account detail and live subtotal formulas.
- 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 Purchase Order Tracking ReportTrack every purchase order line from order to receipt to invoice with pandas: combine PO, goods-receipt and supplier-invoice exports, compute what is still to arrive and what has been received but not invoiced, flag late deliveries and invoices without receipts, value the open commitments, and write a status workbook buyers and accounts payable can both work from.
- How-toBuild a Returns Analysis ReportAnalyse product returns properly with pandas: link each return to the sale it came from, calculate return rates by the month of sale rather than the month of return, compare products and channels, rank the reasons customers give, find the few products that cause most refund value, allow for returns still to come in recent months, and write the analysis to Excel.
- How-toBuild a Sales Commission Report with Python and ExcelCalculate commissions from a month of closed deals: tiered rates applied the way the plan defines them — marginal like tax bands or on the whole amount once a tier is reached — clawbacks for cancelled deals, reps close to the next tier flagged, a summary for payroll and one statement sheet per rep showing the working.
- How-toBuild a Sales Forecast with Exponential SmoothingForecast monthly sales from an Excel history with Holt-Winters exponential smoothing in Python: fit trend and seasonality with statsmodels, test the model on the months it did not see, compare it with a naive seasonal forecast, produce a twelve-month forecast with a range, and write the actuals, forecast and chart into a workbook the business can read.
- How-toBuild a Sales Pipeline Report from a CRM ExportTurn a CRM opportunity export into a pipeline report: value and weighted value by stage, expected close by month, coverage of the remaining quota, deals past their close date or with no recent activity, and what changed since last week by comparing two snapshots — written to Excel for the Monday sales meeting.
- How-toBuild a Staff Utilisation Report from TimesheetsMeasure how much of your team's available time goes on billable work with pandas: capacity from contracted hours minus holidays and leave, billable and non-billable hours from timesheet codes, utilisation against a target per role, a person-by-week heatmap with a colour scale, flags for missing timesheets and overload, and a formatted Excel report.
- How-toBuild a Supplier Payment Run ProposalTurn an open accounts payable listing into a payment run proposal workbook with pandas and openpyxl: select invoices due before the next run, take early-payment discounts that are still available, net off credit notes per supplier, hold back disputed invoices and suppliers without bank details, fit the run to the cash available, and lay out proposal, detail, exclusions and approval sheets.
- How-toBuild a Supplier ScorecardScore suppliers on delivery, quality and price from purchase order, receipt and inspection data with pandas: on-time and in-full delivery against the promised date, defect rates from goods inspection, price variance against the agreed price, a weighted overall score with clear thresholds, the trend against last quarter, and a colour-coded scorecard in Excel.
- How-toBuild a Support Ticket SLA ReportMeasure a helpdesk against its service levels from a ticket export with pandas: SLA targets per priority, elapsed time in business hours that skips nights, weekends and holidays, first-response and resolution breaches, open tickets close to breaching, attainment by priority, team and week, and an Excel report with the at-risk list on top.
- How-toBuild a Training Compliance MatrixBuild the people-by-course matrix that shows who is trained, who is due and who is out of date: work out which courses each person needs from their role, find their latest completion of each, apply each course's validity period to get an expiry date, classify every cell as valid, expiring, expired, missing or not required, and write a colour-coded Excel matrix with compliance by department and an action list.
- How-toBuild a Trial Balance from a General LedgerProduce a month-end trial balance in Excel from general ledger journal lines with pandas: opening balances plus period movements to closing balances for every account, debit and credit columns, a check that the ledger balances, flags for accounts on their wrong side and unmapped or suspense accounts, a prior-month comparison, and an openpyxl layout with live totals.
- How-toBuild a VAT Return Summary from TransactionsSummarise a quarter's sales and purchase transactions into VAT return boxes with pandas: map tax codes to boxes through a table rather than code, handle standard, reduced, zero-rated, exempt, out-of-scope and reverse-charge lines, check every line's VAT against its rate, reconcile the boxes to the ledger, and write a summary workbook a reviewer can trace back to the transactions.
- How-toBuild a Warehouse Pick List from OrdersTurn the day's open orders into printable pick lists with pandas: allocate each order line to stock in specific bins, oldest stock first, record shortages instead of over-allocating, combine identical items across orders into one batch pick, sort the picks in a snake path through the aisles so the picker never doubles back, and write one sheet per picker with tick boxes and a put-to-order table.
- How-toBuild a Warranty Claims ReportAnalyse warranty claims with pandas the way quality and finance teams need: claims per thousand units sold by product and by month of sale, how long after sale products fail, the failure modes that cost most, a production batch that fails more often than the rest, and an estimate of the claims still to come on units under warranty to support the warranty provision — written to an Excel report.
- How-toBuild a Weekly Sales Report in Excel with PythonBuild the Monday-morning sales report from daily transactions: the week just ended against the previous week and the same ISO week last year, like-for-like stores only, year to date, an eight-week trend with sparklines, the biggest movers, and a one-page Excel layout with arrows and conditional formatting.
- How-toBuild a Work in Progress Report for Unbilled TimeValue the time a professional services firm has worked but not yet billed with pandas: price timesheet hours at each person's rate, subtract what has been invoiced, cap fixed-fee projects at their remaining fee, age the unbilled value by when the work was done, flag old WIP and projects over budget, and write the month-end WIP report that finance and project managers use to decide what to bill.
- How-toBuild an Accounts Receivable Aging Report with PythonTurn an open-invoice export into the aging report credit control works from: balances bucketed into current, 1–30, 31–60, 61–90 and over 90 days past due as at the period end, one row per customer with totals, credit notes netted, customers over their credit limit flagged and the total tied back to the ledger.
- How-toBuild an Accruals and Prepayments ScheduleAutomate two month-end schedules with pandas: a prepayments schedule that spreads invoices paid in advance across the months they cover by days, with the release for the month and the balance left on the balance sheet, and an accruals schedule from goods received but not yet invoiced that reverses the next month — both reconciled to the ledger and written to Excel with the journals.
- How-toBuild an Aged Creditors ReportBuild an aged creditors (accounts payable ageing) report from an open payables export with pandas and openpyxl: age every open invoice, credit note and unallocated payment by due date into current, 1–30, 31–60, 61–90 and over-90-day buckets, show credits as negatives, mark disputed items, summarise by supplier, reconcile to the payables control account and highlight what is overdue.
- How-toBuild an Employee Leave Balance ReportCalculate annual leave balances for every employee from an HR list and a leave bookings export with pandas and NumPy: pro-rata entitlement for starters and part-timers, carried-over days with an expiry date, bookings counted in working days using each person's work pattern and the public holiday calendar, half days, bookings that span the year end, and a workbook that flags negative and unusually high balances.
- How-toBuild an Energy Consumption Report from Meter ReadingsTurn irregular meter readings typed into a spreadsheet into monthly energy use per site with pandas: convert cumulative readings into consumption, handle a meter that was replaced and one that rolled over, spread each reading interval across calendar months by days, flag suspicious jumps, add cost from tariffs and emissions from a conversion factor, and write a site-by-month report to Excel.
- How-toBuild an Intercompany ReconciliationReconcile what one group company says another owes it against what the other company says it owes, with pandas: match intercompany transactions from both ledgers by reference, convert to a common currency, separate timing differences, exchange differences and genuine mismatches, check that balances eliminate on consolidation, and write a reconciliation both finance teams can work from.
- How-toBuild an Inventory Reorder Report with Python and ExcelDecide what to order this week from a stock export and recent sales: average daily usage, reorder points from lead time and safety stock, stock already on order counted, order quantities rounded up to pack sizes and minimums, days of cover, stock-out risks flagged and a purchase list grouped by supplier.
- How-toBuild an Inventory Turnover and Days on Hand ReportMeasure how hard stock is working with pandas: annual cost of goods sold against average inventory value for inventory turnover, days on hand from current stock and recent usage, slow-moving and dead stock classification, the cash tied up in each class, a category summary against targets, and an Excel report that shows where to act.
- How-toBuild an On-Time Delivery (OTIF) Report with PythonMeasure delivery performance from order and shipment exports: combine split shipments per order line, count lateness in working days with holidays, score on time, in full and OTIF, break results down by carrier, customer and week against a target, and write an Excel report with the late orders listed for follow-up.
- How-toBuild an Overtime Report from Shift DataCalculate overtime from clock-in and clock-out records with pandas: shifts that cross midnight, unpaid breaks, hours rounded to the quarter hour, overtime above contracted weekly hours, premium rates for Sundays and bank holidays, overtime cost by department, and a check of average weekly hours against a 48-hour limit — written to an Excel report for payroll and managers.
- How-toCalculate FIFO Inventory Valuation with PythonValue stock and cost of goods sold first in, first out from purchase and sales ledgers: consume the oldest cost layers per SKU with a deque, cost each sale across several layers, value the layers left at the period end, stop on sales that exceed stock, compare with weighted average cost, and write the results to Excel with a reconciliation.
- How-toCalculate Weighted Average Cost Inventory ValuationValue stock and cost of goods sold on a moving (perpetual) weighted average cost basis from a stock movements export with pandas: recalculate the average on every receipt, cost issues and returns at the current average, spread late freight and cost corrections over the stock on hand, handle the last unit without leaving a stray penny, flag negative stock, reconcile, and write a stock card per item.
- How-toCheck Supplier Invoices Against Contract PricesCheck every supplier invoice line before payment with pandas: look up the contract price in force on the invoice date, allow a small tolerance, compare the quantity invoiced with the quantity received, check the line arithmetic and the invoice total, value every overcharge, and produce a list of invoices to hold with the reason and the amount to query — written to Excel for the accounts payable team.
- How-toCompare Supplier Quotes with PythonCompare supplier quotes that arrive as differently laid-out Excel files: map each supplier's columns to one schema, convert currencies and pack sizes to a per-unit price, apply quantity price breaks with merge_asof, add delivery and minimum-order rules, score price, lead time and payment terms with weights, and write a comparison matrix that highlights the best offer per item.
- How-toConsolidate Employee Expense Claims into a ReportTurn a folder of employee expense claim forms into one checked report with pandas and openpyxl: read header cells and the claim table from each form, convert foreign-currency lines, apply policy rules such as category limits, missing receipts and duplicate claims, summarise by employee and cost centre, and write a workbook with an exceptions sheet for the approver.
- How-toGenerate a Staff Rota in Excel with PythonBuild a weekly shift rota automatically from staff availability and cover requirements: model it as a small optimisation with SciPy's milp solver, enforce one shift a day, contracted maximums, no late-then-early turnarounds and fair weekends, respect shift preferences, then write a colour-coded Excel rota with live coverage checks and verify every rule.
- How-toModel the Impact of a Price IncreaseEstimate what a price increase will do to revenue and gross profit with pandas before announcing it: apply candidate increases per product group, model lost volume with simple elasticity assumptions, respect customer contracts that fix prices until a date, calculate the break-even volume loss for each product, compare scenarios side by side and write an Excel workbook whose assumptions sales and finance can change.
- How-toReconcile a Stock Count Against System InventoryCompare a physical stock count with the inventory system in pandas: combine count sheets from several counters, normalise SKU codes, total items found in more than one place, outer-join to the system quantities with an indicator, value each variance at cost, pick lines to recount by value and percentage thresholds, and produce an adjustment summary and Excel workbook.
- How-toReconcile Payroll to the General LedgerProve each month that payroll was posted to the ledger correctly with pandas: check every payslip adds up, map the payroll register to the expense and liability accounts it should hit, compare with the general ledger journal by account and cost centre, tie net pay to the bank payment file, and produce a reconciliation workbook that explains every difference.
- How-toSend Out Budget Templates and Consolidate the ReturnsRun the annual budget round from Python: generate one pre-filled budget workbook per cost centre with locked formulas, validated input cells and a hidden identity sheet, then read the returned files back, check that each is the right template for the right cost centre with sensible numbers, consolidate them into one table and keep a tracker of who has returned what.
- How-toUpdate a Price List with Effective DatesApply a sheet of price changes to a price list that keeps history: close each current price the day before its replacement takes effect, add the new row, reject changes that are backdated into a closed period or that would overlap, check that every product has exactly one price on every day, look up any product's price on any date, and produce a change report showing old and new prices side by side.
- How-toAdd a Data Dictionary Sheet to an Excel ReportDocument every column of a multi-sheet Excel report in a generated data dictionary sheet: descriptions kept next to the code, types, units and number formats, example values, blank counts and ranges profiled from the data, links to each sheet, a check that fails on undocumented columns, and a dictionary for a workbook you did not build.
- How-toAdd a KPI Summary Block to an Excel DashboardGenerate the top strip of a dashboard from Python: merged tiles with label, value and signed comparison, drawn by one reusable function in xlsxwriter or openpyxl.
- How-toAdd a Table of Contents Sheet with Hyperlinks in ExcelGive a multi-sheet workbook a front door — an index sheet with internal hyperlinks, row counts and back-links, generated from whatever sheets the workbook happens to contain.
- How-toAdd a Dropdown Filter That Drives an Excel DashboardGive an Excel dashboard a slicer-like control from Python — a data validation dropdown fed by a hidden list, an "All" option, SUMIFS formulas and a chart that follow the selection, and a second filter combined with the first — because native slicers cannot be written by openpyxl or XlsxWriter.
- How-toAdd a Summary Sheet to an Excel Report with PythonCompute KPIs with pandas groupby, write a styled Summary sheet first, then detail sheets, and make the Summary the active leftmost tab — all with openpyxl.
- How-toAdd Variance Arrows and Trend Indicators to a DashboardShow at a glance whether each dashboard figure moved the right way: custom number formats that put a coloured up or down arrow next to a variance without changing the value, separate formats for measures where down is good, a dead band so tiny changes show as flat, a three-period trend indicator built with formulas, and a check of the displayed text with LibreOffice.
- 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-toLink Dashboard Cells to Data Sheets with FormulasBuild an Excel dashboard whose figures are live formulas pointing at the data sheets — SUMIFS and COUNTIFS over a table, a named report-month cell readers can change, quoted sheet names, errors handled with IFERROR — so the dashboard updates when the data does, and check the results without Excel.
- How-toBuild a Word Report with Tables from Excel DataTurn Excel data into a Word report with python-docx: headings and generated commentary, DataFrames written as properly styled Word tables with right-aligned numbers, fixed column widths and a header row that repeats on every page, a landscape section for a wide table, a chart image with a caption, and checks of the finished document.
- How-toFill an Excel Template with Python and openpyxlLoad a prepared .xlsx template with openpyxl, set named cells for title, date and totals, fill a repeating data table from a list or DataFrame, and save a new file.
- How-toGenerate an HTML Report from Excel Data with Jinja2Build a complete, self-contained HTML report from an Excel workbook with pandas and Jinja2 templates: shape the data in Python and keep the layout in a template, autoescape cell text, format numbers with custom filters, loop over sections, draw bar charts with plain HTML and CSS, share a base layout through template inheritance, fail fast on missing values, and test the output.
- How-toGenerate Customer Account Statements from a LedgerProduce a statement workbook for every customer from one receivables ledger with pandas and XlsxWriter: opening balance brought forward, the period transactions with a running balance, closing balance, an ageing summary built by allocating payments to the oldest invoices, a statement layout with formats, and reconciliation checks against the ledger.
- How-toGenerate Invoices from an Excel Template with PythonProduce one invoice workbook per customer from a designed Excel template — named cells instead of hard-coded addresses, money calculated with Decimal and a stated VAT rounding rule, invoice numbers that never repeat or skip, a fixed block of line rows with an overflow check, a register of everything issued and a PDF for each invoice.
- How-toGenerate One Excel Report per Region in a LoopSplit one dataset into many workbooks safely: a filename that cannot collide or break a filesystem, one failure that does not stop the batch, atomic writes, an empty-group decision, and a manifest of what was produced.
- 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-toGenerate Word Documents from Excel Rows with PythonDo a mail merge in Python: one Word letter, statement or certificate per row of a spreadsheet, filled from a .docx template. Replace placeholders even when Word has split them across formatting runs, fill header, footer and table placeholders, add a row per line item, format money and dates, catch unfilled fields and convert the results to PDF.
- How-toPopulate an Excel Template Without Losing FormattingWhy pandas.to_excel destroys template styling and openpyxl preserves it — what load_workbook keeps, what it can drop, and how to set values without touching styles.
- How-toPrint Mailing Labels from an Excel Address ListTurn an Excel address list into a print-ready PDF of mailing labels with pandas and reportlab: describe a label sheet such as Avery L7160 or 5160 by its measurements, tidy addresses by dropping blank lines, shrink long lines to fit, start on a partly used sheet, print an alignment test, and check every address landed inside its label.
- How-toRepeat a Template Block for Each Record in an Excel ReportStamp a designed block of rows — a customer statement, a card, a mini-report — once per record down a single sheet with openpyxl, copying styles, merged cells, row heights and conditional formatting, translating formulas to their new rows, growing the block for records with more line items and starting each record on a new printed page.
- How-toReplace Placeholders in an Excel Template with PythonLet report designers type named markers such as customer and period straight into the template, then fill them from Python: whole-cell placeholders that keep numbers numeric, placeholders inside text, a repeating row for line items, and checks for anything left unfilled.
- How-toAdd Text Boxes and Shapes to Excel with XlsxWriterFloat commentary, notes and labels over an Excel report with XlsxWriter insert_textbox: size, fill, border and font options, multi-line and rotated text, gradient fills and hyperlinks, text linked to a cell (and why it shows empty in LibreOffice), placing a box below a table of any length, and when a formatted cell is the better choice.
- How-toBuild a Calendar Sheet in Excel with PythonGenerate a printable month-grid calendar in a workbook: the grid from Python's calendar module, weekends shaded, public holidays marked, events from a spreadsheet list written into their day cells, ISO week numbers down the side, twelve months on one sheet or one per tab, and a layout that prints on one page.
- How-toSet the Active Sheet, Selected Cell and Tab Colours with openpyxlDecide what a reader sees when a generated workbook opens: which sheet is in front, which cell is selected, where the view is scrolled to, and which tabs are coloured. Includes the trap that leaves two sheets grouped after wb.active changes, and the xlsxwriter equivalents.
- How-toSet Workbook Properties Like Author and Title with PythonGive generated workbooks a proper title, author, company, keywords and custom properties such as the data source or row count. See what openpyxl and xlsxwriter can set, which fields each one overwrites on save, and how to read the properties back for auditing.
- How-toHighlight Top and Bottom Values in Excel with openpyxlMark the top three sales, the bottom 10%, everything above average, or the best rep in each region with conditional formatting written from Python. Covers the top10 and aboveAverage rules, how ties behave, highlighting whole rows, top-N per group with COUNTIFS, and checking the result without Excel.
- How-toBuild a Data Quality Scorecard for an Excel FileScore an incoming Excel file on data quality with pandas, column by column: completeness, validity against a type, pattern or range, uniqueness for key columns, consistency with an allowed list and freshness of dates — declared once in a small rules table, combined into a score per column and for the file, compared with last month's file, and written as a colour-coded scorecard with sample failing values.
- How-toBuild a Date Table for Excel Reports with pandasGenerate a calendar table — one row per day with year, quarter, month, ISO week, weekday, fiscal year, public holidays and working-day numbers — and use it to fill days with no sales, report by fiscal period, find the last working day of each month, and write it as an Excel table that lookups, pivots and Power Pivot can use.
- How-toCreate a Shift Calendar with Rotating PatternsGenerate a shift rota for teams on a rotating pattern — such as four teams on a 2-2-3 day and night cycle — with pandas: store each team's pattern as a repeating sequence anchored to a start date, expand it to any date range, prove every day has exactly one team on days and one on nights, total hours per team per month, mark public holidays, and write a colour-coded calendar grid to Excel.
- How-toCopy a Template Workbook in SharePoint with Microsoft GraphCreate new Excel workbooks from a template that lives in SharePoint or OneDrive using Microsoft Graph from Python: find the template by path, start a server-side copy into a destination folder, poll the asynchronous monitor URL until the copy finishes, handle name clashes, then fill in the copy through the workbook API without downloading anything.
- How-toConsolidate Timesheets from Many Workbooks with PythonEveryone fills in the same timesheet template and sends it back; you need hours by project and person for the week. Read the name and week from each file's header cells, unpivot the day grid into rows, spot who has not submitted, catch unknown project codes, impossible days and short weeks, and write one consolidated workbook.
- How-toConvert Multi-Currency Transactions with an FX Rate TableReport transactions in several currencies in one base currency: turn euro-based reference rates into base-currency rates by triangulation, apply the rate in force on each transaction date, round to each currency's minor units, use monthly average rates for the P&L and closing rates for balances, and calculate the unrealised gain or loss on open invoices.
- 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-toSend a Weekly Digest of Several Reports in One EmailReplace five separate report emails with one weekly digest: collect the week's report files from a manifest, pull a headline figure from each workbook, build one HTML email with a section per report and a plain-text alternative, attach the files while keeping under a size limit (linking the large ones instead), state clearly which reports were not produced, and preview the message before sending.
- How-toSend Each Recipient Their Own Excel Report with PythonEmail every regional manager a workbook with only their region's figures — built in memory, sent over one SMTP connection, logged per recipient and safe to rerun. Check the recipient mapping before anything is sent, preview the plan, handle a rejected address without stopping the batch, and verify nobody received someone else's data.
- How-toAdd Bookmarks and a Cover Page to an Excel PDF ExportFinish a multi-sheet Excel PDF export like a real report: read the sheet bookmarks LibreOffice already writes, prepend a cover page with a contents list built with reportlab, rebuild a nested bookmark tree with pypdf, number pages so the cover is not page 1, open the PDF with the bookmarks panel showing, and test the result.
- How-toFill PDF Forms from Excel Rows with PythonFill a fillable PDF form — an expense claim, an order form, a government template — once per spreadsheet row with pypdf: list the form's field names, map sheet columns to fields, format numbers and dates as text, tick checkboxes with the right state name, flatten the result so it cannot be edited, and verify every filled file.
- How-toGenerate One PDF per Excel RowTurn every row of a sheet into its own PDF — customer statements, certificates, remittance advices — either drawn directly with reportlab or by filling an Excel template per row and converting all of them in one LibreOffice call; with safe file names, a combined print file, and a check that each PDF contains the right customer and amount.
- 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-toLocalise an Excel Report for Other Languages and LocalesProduce one Excel report for readers in Germany, France and Poland without breaking it: let number formats follow each reader's separators, use the locale-aware short date, pin month names to a language with LCID tags, place currency symbols, translate labels from one table, set right-to-left sheets, and check the result under each locale.
- How-toFreeze Panes and Set Zoom with XlsxWriterControl how each sheet of an XlsxWriter report opens: freeze the header row and the first column, split panes instead of freezing, set the zoom per sheet, hide gridlines, choose which sheet and cell are active, scroll the view to a starting cell, colour the tabs, and check every view setting by reading the file back with openpyxl.
- How-toInsert Checkboxes in Excel with xlsxwriterTurn a column of TRUE/FALSE values into clickable checkboxes — a month-end close checklist, a review sign-off column, a to-do list. Use xlsxwriter's insert_checkbox, count and percentage-complete formulas that show the right value before recalculation, strike through finished rows with conditional formatting, read the ticks back, and fall back gracefully where checkboxes are not supported.
- How-toSet Print Options, Headers and Footers with XlsxWriterMake an XlsxWriter report print properly: landscape A4, fit to one page wide, repeat the header row on every page, set the print area and margins, add page breaks between groups, and write headers and footers with the report name, page X of Y, the print date and a logo — then check the printed result by exporting it to PDF.
- 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-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-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-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-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-toMake an Excel Report Accessible with PythonGenerate workbooks that screen-reader users and people with low vision can use: meaningful sheet names and a title, data in real tables with header rows, alternative text on charts and images, no merged cells, meaning that does not rely on colour alone, colours that pass contrast checks, and an automated audit that flags the problems in any workbook.
- How-toIndent Cells to Show a Hierarchy with openpyxlLay out a chart of accounts, an organisation or a product tree the way people read hierarchies: children indented under their parent, parents in bold with rolled-up totals, and rows grouped so each branch can be collapsed. Work out each row's depth from a parent–child table, set Alignment(indent=...), roll values up the tree and add outline levels with openpyxl.
- How-toInsert Page Breaks Between Groups with openpyxlMake each region, customer or department start on a new printed page: find where each group ends, add manual row breaks with openpyxl, repeat the header on every page, add column breaks for wide sheets, clear old breaks, do the same with XlsxWriter, and prove the result by rendering a PDF and checking which group starts each page.
- How-toSave a Workbook as an Excel Template with PythonCreate a real .xltx template with openpyxl — styled headers, input cells, dropdowns, formulas and protection — by setting workbook.template = True, check the content type that makes Excel treat it as a template, and avoid the trap of loading an .xltx and saving it as .xlsx without switching the flag back, which produces a file Excel refuses to open.
- How-toClear Cell Contents and Formatting with openpyxlReproduce Excel's Clear menu in Python: clear values but keep formatting, reset formatting but keep values, remove comments and hyperlinks, or clear everything. Covers clearing a template's input area while keeping its formulas, and why merged cells make a simple clearing loop fail.
- How-toMerge Repeated Group Labels into Single Cells with openpyxlTurn a report where the region name repeats on every row into one merged, vertically centred label per group: find runs of equal values, merge them with openpyxl, handle nested groups so an inner label never spans two outer groups, keep borders tidy, and know when to hide repeats with conditional formatting instead so the sheet stays sortable.
- How-toMove a Range of Cells with openpyxl move_rangeShift a block of cells to a new position with ws.move_range: values and styles move, translate=True rewrites formulas inside the block, and everything else — formulas that point at the block, merged cells, conditional formats — stays where it was. Learn the rules and a safe way to move a table.
- How-toWrite Grouped Data with Blank Rows Between GroupsWrite a pandas DataFrame to Excel as a readable grouped report — a heading row for each group, its detail rows, a subtotal row with a live SUBTOTAL formula and a blank row before the next group — by planning the row positions first and writing once, instead of inserting rows afterwards; add a grand total that does not double count, optional outline levels, and check the formulas recalculate correctly.
Compare the options
Which tool or approach to pick, and why.
Follow it through a track
Prefer an ordered route? These tracks cover report layouts & templates 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.
Report layouts & templates is part of Formatting & presenting reports.