Formatting & presenting reports
Conditional formatting
Colour scales, data bars, thresholds, duplicates and whole-row highlighting.
Start with the full guides
The long-form treatment of conditional formatting, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAdd Data Bars and Colour Scales with openpyxlIn-cell visuals that survive edits: DataBarRule and ColorScaleRule, percentile versus fixed endpoints, negative values, icon sets, and how to pick between a bar, a scale and a plain threshold.
- How-toAdd Icon Sets to Excel Cells with openpyxlAdd traffic lights, arrows, flags and rating icons to an Excel report with openpyxl's IconSetRule — choose the icon set, set thresholds as numbers, percents, percentiles or cells the reader can edit, control inclusive boundaries, show icons alone, and drive status icons from a calculated column.
- How-toApply a Colour Scale per Row or per Column with openpyxlColour scales compare every cell in their range with every other cell, so one big product drowns out all the small ones. Apply a separate colour scale to each row or each column with openpyxl so every item is coloured against its own range, choose between min-max and percentile scaling, keep the number of rules manageable, and check the rendered result.
- How-toApply Conditional Formatting to an Excel Table That GrowsKeep highlighting working when a report table gets new rows every week: why conditional formatting ranges do not follow rows appended by Python, re-pointing every rule at the resized table with openpyxl, generous ranges as a simpler alternative and their cost, why structured references are not allowed in rule formulas, and a check that every rule covers every data row after an append.
- How-toControl Conditional Formatting Rule Order and Stop If TrueWhen several conditional-formatting rules match the same cell, their priority decides what the reader sees — and Excel and LibreOffice resolve overlaps differently. See how openpyxl numbers rules, set priority explicitly, use stopIfTrue so the first matching rule wins everywhere, design self-contained rules, and check the order in a saved file and a rendered page.
- How-toHighlight an Entire Row Based on a Cell Value with openpyxlColour a whole row when its Status is Overdue or its amount crosses a limit, using a formula-based conditional format with the right mix of absolute and relative references — so the colour follows the data after edits and sorts.
- How-toHighlight Blank Cells in Excel with openpyxlMake missing values impossible to overlook: highlight empty required fields, whole rows with a gap, or blanks only inside the data. See why ISBLANK misses cells that look empty, how LEN(TRIM()) catches spaces and formulas returning "", and how to stop the rule from colouring every empty row below the table.
- How-toHighlight Cells Above a Threshold with openpyxlDraw the eye to the numbers that matter — CellIsRule for fixed thresholds, FormulaRule for a threshold that lives in a cell, whole-row highlighting, and rule priority.
- How-toHighlight Cells Containing Specific Text with openpyxlColour every status that mentions "late", every reference that begins with INV, or every note containing any word from a list. Write containsText, beginsWith and formula rules with openpyxl, choose between case-insensitive SEARCH and case-sensitive FIND, match whole words so "late" does not match "calculate", and drive the keyword from a cell.
- How-toHighlight Differences Between Two Columns with openpyxlMark every row where system A and system B disagree, or where this month's value differs from last month's sheet. Write the comparison as a conditional format with openpyxl, and decide deliberately how case, blanks versus zero, trailing spaces and rounding differences should count.
- How-toHighlight Duplicate Values in Excel with openpyxlFlag repeated values in a worksheet: the built-in duplicateValues rule, formula rules for duplicates across columns, whole-row highlighting, and a pandas alternative.
- How-toHighlight Overdue Dates in Excel with openpyxlMark overdue and due-soon rows in an Excel tracker with conditional formatting that uses TODAY(), so the colours stay correct every day the file is opened — ignore blank and completed rows, order rules with stopIfTrue, add a days-overdue column and avoid the text-date trap.
- 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-toHighlight Values Outside a Tolerance of Target with openpyxlColour each actual figure by how far it is from its own target — green within tolerance, amber close to the limit, red outside it — with openpyxl conditional formatting: percentage and absolute tolerances, a tolerance setting readers can change, per-row tolerances, targets of zero, and a recalculated check that the rules flag exactly the rows pandas expects.
- How-toHighlight Weekends and Holidays in a Date ColumnShade weekend and public-holiday rows in an Excel log or timeline with openpyxl conditional formatting: WEEKDAY-based rules for Saturdays and Sundays, a holiday list generated with the holidays package on its own sheet, rule order so holidays win, the same rules across a date header row for planners, and a recalculated check of which dates each rule catches.
- How-toList and Remove Conditional Formatting with openpyxlSee every conditional formatting rule in a workbook — range, type, priority, formula and fill — as a table, then remove rules by range, by type or all at once, collapse duplicates that pile up when scripts rerun, and renumber priorities, all with openpyxl.
- How-toApply Conditional Formatting to a Range in openpyxlAttach a conditional formatting rule to a cell range in openpyxl with conditional_formatting.add(), covering range syntax, CellIsRule, and the FormulaRule fallback.
- How-toUse Conditional Formatting Rules That Reference Another SheetHighlight cells based on lists and settings kept on other sheets with openpyxl: flag orders from customers on a blocked list with COUNTIF across sheets, colour values above a threshold stored on a Settings sheet, use defined names so rules read clearly and work in older Excel versions, anchor references correctly, and verify the rule logic by recalculating a helper formula.
- How-toApply Conditional Formatting with xlsxwriterWrite rules Excel re-evaluates: cell and text criteria, colour scales and data bars, a formula rule that highlights whole rows, and the absolute-reference trap that makes a row rule follow the wrong column.
- How-toTurn a Pivot Table into a Heatmap in ExcelMake a pandas pivot readable at a glance by colouring it in Excel: a sequential colour scale for volumes, a diverging scale centred on zero for changes, totals left out of the scale, a separate scale per row when rows differ in size, numbers hidden for a pure heatmap, and the same rules in openpyxl and xlsxwriter.
- How-toVisualise Missing Data Patterns in an Excel FileSee where and why data is missing, not just how much: a missingness map sheet that shades every blank cell, the most common combinations of missing columns, missing rates by month and by source, columns that tend to go missing together, and a one-sheet Excel report built with pandas and openpyxl that shows a data owner where to look.
- How-toHighlight Invalid Cells in Excel with PythonMark the exact cells that failed validation with openpyxl — a red fill, a comment explaining the rule, a legend and an issues sheet — so a submitter can fix the file in minutes.
- 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 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 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 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-toAdd Alternating Row Colors with openpyxlBand the rows of an Excel report so long lists are easy to follow — with static fills, a MOD(ROW()) conditional format that survives sorting, a SUBTOTAL-based rule that keeps alternating when rows are filtered, table stripes, and banding by group instead of by row.
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 conditional formatting 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.
Conditional formatting is part of Formatting & presenting reports.