How-to

Formatting And Charting Excel Reports With PythonDeep dive

Add Alternating Row Colors with openpyxl

Band 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.

Cell styling & layout Conditional formatting openpyxl

A long list of rows is hard to read across: the eye slips from one row to the next halfway along. Banded rows — a light shade on every other row — fix that, which is why every table style in Excel has them. From Python there are several ways to add them, and the obvious one, filling every second row, is the one that breaks first: readers sort the sheet and the stripes scramble. This guide, part of Styling Excel Cells with openpyxl, compares the approaches and shows the ones that keep working after sorting, inserting and filtering, all checked by rendering the workbook.

Prerequisites

Bash

pip install openpyxl

An order list to band:

Python

from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

ORDERS = [(101, "North", 120), (102, "South", 90), (103, "North", 75), (104, "East", 640),
          (105, "South", 820), (106, "North", 310), (107, "West", 150), (108, "East", 60)]
BAND = PatternFill("solid", fgColor="E7EBEF")

def orders_sheet(wb, title):
    ws = wb.create_sheet(title)
    ws.append(["Order", "Region", "Amount"])
    for row in ORDERS:
        ws.append(row)
    return ws

wb = Workbook()
wb.remove(wb.active)

Step 1 — Static fills, and why they break

Filling every even row directly is the first thing most people write:

Python

static = orders_sheet(wb, "Static")
for row in static.iter_rows(min_row=2):
    if row[0].row % 2 == 0:
        for cell in row:
            cell.fill = BAND
print([r for r in range(2, 10) if static.cell(r, 1).fill.fgColor.rgb == "00E7EBEF"])   # [2, 4, 6, 8]

It looks right until a reader sorts by amount: the fills travel with their cells, and the stripes end up in random places. Inserting a row shifts the pattern, and filtering hides some shaded rows but not others. Static fills are fine only for a finished printout nobody will touch — a PDF export, for instance, where the file is never sorted again.

Step 2 — A rule based on the row number

A conditional formatting rule is evaluated where the cell is, not where it came from, so sorting cannot scramble it. MOD(ROW(),2)=0 is true on every even sheet row:

Python

by_row = orders_sheet(wb, "ByRow")
by_row.conditional_formatting.add("A2:C9", FormulaRule(formula=["MOD(ROW(),2)=0"], fill=BAND))

Sort the data, insert a row in the middle — the stripes stay perfect. They break in one situation: when rows are hidden, by a filter or by hand. Rendering this sheet with rows 3, 5 and 6 hidden shows orders 101 and 103 both shaded and next to each other, because their sheet rows, 2 and 4, are both even. For reports that people filter, the rule needs to count visible rows instead.

Step 3 — Banding that follows filters

SUBTOTAL(103, range) counts non-empty visible cells. Anchor the start of the range and let the end move with the row, and it numbers the visible rows 1, 2, 3 — whatever is hidden:

Python

visible = orders_sheet(wb, "Visible")
visible.conditional_formatting.add(
    "A2:C9", FormulaRule(formula=["MOD(SUBTOTAL(103,$A$2:$A2),2)=0"], fill=BAND))
for r in (3, 5, 6):                         # as if a filter had hidden them
    visible.row_dimensions[r].hidden = True
wb.save("banded.xlsx")

Rendered with the same rows hidden, the visible orders 101, 103, 106, 107 and 108 alternate plain, shaded, plain, shaded, plain. The counting column — A here — must never be empty in the data, because SUBTOTAL(103, …) counts non-empty cells; use a key column such as an order number. The formula recalculates on every filter change, which is fine for thousands of rows; for very large sheets, a table (Step 4) is lighter. Filters themselves are covered in Apply Autofilter Criteria and Hide the Filtered Rows with openpyxl.

Step 4 — Or let an Excel table do it

When the data is a proper Excel table, banding is a table style option. Stripes, filtering and new rows are all handled by Excel:

Python

from openpyxl.worksheet.table import Table, TableStyleInfo

tabled = orders_sheet(wb, "Tabled")
table = Table(displayName="Orders", ref="A1:C9")
table.tableStyleInfo = TableStyleInfo(name="TableStyleLight9", showRowStripes=True)
tabled.add_table(table)
wb.save("banded.xlsx")
print(table.tableStyleInfo.showRowStripes)       # True

The stripe colours come from the table style — TableStyleLight1 to Light21, Medium1 to Medium28 and Dark1 to Dark11 — rather than from your palette. If the report must use exact brand colours, use the conditional formatting rules with a table style that has no stripes. Tables are covered in Create an Excel Table with Python.

Step 5 — Alternate by group, not by row

In a list sorted by region, shading whole groups — all North rows one shade, all South rows the next — shows where each group starts and ends. A helper column flips between 0 and 1 whenever the region changes; the fill rule reads it, and the column is hidden:

Python

grouped = orders_sheet(wb, "ByGroup")
rows = sorted(ORDERS, key=lambda r: r[1])
for r, values in enumerate(rows, start=2):
    for c, value in enumerate(values, start=1):
        grouped.cell(r, c, value)
    grouped.cell(r, 4, 0 if r == 2 else f"=IF(B{r}=B{r - 1},D{r - 1},1-D{r - 1})")
grouped.column_dimensions["D"].hidden = True
grouped.conditional_formatting.add("A2:C9", FormulaRule(formula=["$D2=1"], fill=BAND))
wb.save("banded.xlsx")
print([grouped.cell(r, 2).value for r in range(2, 10)])
# ['East', 'East', 'North', 'North', 'North', 'South', 'South', 'West']

Rendered, the two East rows are plain, the three North rows shaded, the South rows plain and West shaded. Because the flip is a formula, re-sorting the sheet by region keeps the groups banded; sorting by something else breaks the grouping, as it should. The same idea without formulas — compute the group parity in pandas and write it — works for finished reports.

Step 6 — Band a report written by pandas

For a DataFrame written with to_excel, add the rule in the same ExcelWriter block. Size the range from the frame, leave room for growth, and guard against empty rows so the space below the data stays clean:

Python

import pandas as pd
from openpyxl.utils import get_column_letter

frame = pd.DataFrame(ORDERS, columns=["Order", "Region", "Amount"])
with pd.ExcelWriter("banded_pd.xlsx", engine="openpyxl") as writer:
    frame.to_excel(writer, sheet_name="Orders", index=False)
    sheet = writer.sheets["Orders"]
    last_col = get_column_letter(len(frame.columns))
    band_rule = FormulaRule(formula=['AND($A2<>"",MOD(SUBTOTAL(103,$A$2:$A2),2)=0)'], fill=BAND)
    sheet.conditional_formatting.add(f"A2:{last_col}{len(frame) + 500}", band_rule)
    sheet.auto_filter.ref = f"A1:{last_col}{len(frame) + 1}"
print(len(frame) + 500)                           # 508

The rule covers 500 rows beyond the data, so a reader pasting in more orders gets banding without anyone touching the file, and the AND($A2<>"", …) keeps those empty rows plain until something is written in them. The autofilter makes the SUBTOTAL version worthwhile: readers can filter by region and the stripes follow the visible rows. When the same report is regenerated each month, the rule is rebuilt with the file, so there is no risk of rules piling up as there is when an existing workbook is edited in place.

Common pitfalls

Dark bands. A band dark enough to notice on screen can make text hard to read and prints heavily. Use a very light tint, such as E7EBEF, and keep text black.
Rules that miss new rows. A rule on A2:C9 does not shade row 10. Apply it to a generous range such as A2:C5000, and add a test for an empty key cell — AND($A2<>"", MOD(ROW(),2)=0) — so the empty rows below the data stay plain.
Colour as the only cue. Some screens and projectors wash out light tints entirely. Banding is a reading aid, not information; never use the band colour to mean something.
Banding plus a full grid. Stripes already separate rows; a heavy grid on top looks busy. Keep borders to the header and outline.

Conclusion

Static fills are the least robust way to band rows. Use a conditional formatting rule — MOD(ROW(),2)=0 for sheets that are sorted but not filtered, MOD(SUBTOTAL(103,$A$2:$A2),2)=0 for sheets that are filtered — or an Excel table with showRowStripes=True. For grouped lists, drive the fill from a hidden helper column that flips when the group changes, and keep the band colour light.

Frequently asked questions

How do I shade every other row with openpyxl? The robust way is a conditional formatting rule, FormulaRule(formula="MOD(ROW(),2)=0", fill=...), applied to the table range. Unlike fills set cell by cell, the rule keeps alternating after readers sort or insert rows.

Why does the banding break when I filter? MOD(ROW()) looks at the sheet row number, so hiding rows leaves two shaded or two plain rows next to each other. Use MOD(SUBTOTAL(103,$A$2:$A2),2)=0 instead, which counts only visible rows.

Should I use an Excel table instead? If the data is a table anyway, yes — a table style with showRowStripes=True bands the rows and handles filtering and new rows automatically. Conditional formatting is for ranges that are not tables.

How do I alternate colours by group, such as by region? Sort by the group column, add a helper column that flips between 0 and 1 whenever the group changes, hide it, and base the fill rule on it. Each group then shares one shade.

Up one level: Styling Excel Cells with openpyxl — fonts, fills, borders and layout.
Add Borders to a Range with openpyxl — structure to pair with banding.
Highlight an Entire Row Based on a Cell Value with openpyxl — formula rules for whole rows.
List and Remove Conditional Formatting with openpyxl — keeping rules tidy on reruns.

More on cell styling & layout