Read Cell Colours and Font Styles as Data with openpyxl

Plenty of real trackers never got a Status column. Instead, somebody shades a task green when it is done, red when it is blocked and yellow while it waits for sign-off, and strikes through the owner's name when that person has left. To everyone who opens the file that is perfectly clear. To pd.read_excel() it is invisible, because pandas reads values and throws formatting away. This guide, part of Extracting Data from Messy Excel Sheets, reads that formatting with openpyxl and turns it into columns you can filter, count and report on.

Prerequisites

pip install openpyxl pandas

This builds tracker.xlsx with the same fills and fonts as the diagram, using Excel's own "Good", "Bad" and "Neutral" cell-style colours:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Tracker"
ws.append(["Task", "Owner", "Due"])
for row in [("Invoice run", "Ana", "2026-09-01"), ("Audit prep", "Ben", "2026-09-05"),
            ("Close books", "Cara", "2026-09-10"), ("Payroll", "Dev", "2026-09-12")]:
    ws.append(row)

ws["A2"].fill = PatternFill("solid", start_color="C6EFCE")   # green
ws["A3"].fill = PatternFill("solid", start_color="FFC7CE")   # red
ws["A4"].fill = PatternFill("solid", start_color="FFEB9C")   # amber
ws["A5"].fill = PatternFill("solid", start_color="C6EFCE")   # green
ws["B4"].font = Font(bold=True, strike=True)
wb.save("tracker.xlsx")

Step 1 — Load the workbook with styles intact

openpyxl keeps every cell's style when it loads a workbook normally. The one mode to avoid is read_only=True: it is fast because it streams cells, but the objects it yields carry a much thinner style model, and relying on it for colours is asking for surprises.

from openpyxl import load_workbook

wb = load_workbook("tracker.xlsx")          # not read_only
ws = wb["Tracker"]

cell = ws["A2"]
print(cell.value)                  # Invoice run
print(cell.fill.fill_type)         # solid
print(cell.fill.fgColor.type)      # rgb
print(cell.fill.fgColor.rgb)       # 00C6EFCE

Two parts of that output need explaining. A solid fill stores its colour as the foreground colour of the pattern, so the attribute to read is fgColor (aliased as start_color), not bgColor. And the colour comes back as eight hex digits — an alpha byte followed by red, green and blue — so compare on the last six characters.

Step 2 — Handle the three kinds of colour

fgColor.type tells you how the colour was stored, and each kind needs different handling. Explicit RGB is the easy case. A theme colour is an index into the workbook's palette plus a tint, and asking for its .rgb returns an error string rather than a hex value. An indexed colour points into Excel's legacy 64-colour table, which you mostly meet in files that started life as .xls.

from openpyxl.styles.colors import COLOR_INDEX

def fill_key(cell) -> str | None:
    """Return a stable identifier for a cell's fill, or None when it is unfilled."""
    fill = cell.fill
    if fill is None or fill.fill_type is None:
        return None
    color = fill.fgColor
    if color.type == "rgb":
        return color.rgb[-6:].upper()
    if color.type == "theme":
        return f"theme{color.theme}{color.tint:+.2f}"
    if color.type == "indexed" and color.indexed < len(COLOR_INDEX):
        return COLOR_INDEX[color.indexed][-6:].upper()
    return None

for row in ws.iter_rows(min_row=2, max_col=1):
    print(row[0].value, fill_key(row[0]))

Resolving a theme colour to actual RGB means reading the theme XML and applying the tint yourself, which is rarely worth it. The key only needs to be stable: once you have seen that theme5+0.40 is what this workbook uses for "done", map that key to a status exactly as you would a hex code.

Step 3 — Read font styles the same way

Fonts are simpler because the properties that carry meaning are booleans. font.b is bold, font.i italic, font.strike strikethrough and font.u the underline style. Font colour works exactly like fill colour, through font.color, which can be None when the cell uses the default.

def font_flags(cell) -> dict:
    font = cell.font
    color = font.color.rgb[-6:] if font.color is not None and font.color.type == "rgb" else None
    return {"bold": bool(font.b), "struck": bool(font.strike), "font_color": color}

print(font_flags(ws["B4"]))   # {'bold': True, 'struck': True, 'font_color': None}
print(font_flags(ws["B2"]))   # {'bold': False, 'struck': False, 'font_color': None}

Step 4 — Build a DataFrame with the formatting as columns

Now combine the two: walk the rows once, keep the values, and add one column per piece of formatting that carries meaning. Map the colour keys to readable statuses in a dictionary, and let anything unexpected come through as its raw key so that a new colour is visible rather than silently dropped.

import pandas as pd

STATUS_BY_FILL = {"C6EFCE": "done", "FFC7CE": "blocked", "FFEB9C": "waiting"}

records = []
for task, owner, due in ws.iter_rows(min_row=2, max_col=3):
    key = fill_key(task)
    records.append({
        "task": task.value,
        "owner": owner.value,
        "due": pd.to_datetime(due.value),
        "status": STATUS_BY_FILL.get(key, key or "not started"),
        "owner_left": bool(owner.font.strike),
    })

tracker = pd.DataFrame(records)
print(tracker)
print(tracker["status"].value_counts())

From this point the sheet behaves like any other data source. You can filter blocked tasks, count work still assigned to people who have left, or write the result back as a proper table with a real Status column — which is often the most useful outcome, because it retires the colour convention for good.

Step 5 — Watch for conditional formatting

This is the trap that catches almost everyone. A cell shaded by a conditional format rule has no fill of its own; the colour is computed by Excel at display time from a rule stored on the worksheet. openpyxl reads the rule but does not evaluate it, so cell.fill reports the cell as unfilled even though it is bright red on screen.

for cf_range in ws.conditional_formatting:
    for rule in cf_range.rules:
        print(cf_range.sqref, rule.type, rule.operator, rule.formula)

If that loop prints anything covering your columns, the colours you care about are derived, and the right move is to re-implement the rule's condition in pandas instead of reading colours at all. The rule is usually simple — a threshold, a date comparison, a duplicate check — and reproducing it gives you a column that is correct even for rows added after the rule was written. The rule types themselves are described in Applying Conditional Formatting with openpyxl.

Step 6 — Write the decoded status back as a real column

Reading colours is a rescue operation; the lasting fix is a workbook where the status is a value. Add a Status column next to the tasks, fill it from the decoded frame, and — if people still want the colour — let a conditional format paint it from that column. The file then looks the same to its readers, and the next script that reads it can use pd.read_excel like any other sheet.

from openpyxl.formatting.rule import FormulaRule

ws.insert_cols(2)
ws["B1"] = "Status"
for offset, status in enumerate(tracker["status"], start=2):
    ws.cell(row=offset, column=2, value=status)

last = ws.max_row
for word, color in (("done", "C6EFCE"), ("blocked", "FFC7CE"), ("waiting", "FFEB9C")):
    ws.conditional_formatting.add(
        f"A2:A{last}",
        FormulaRule(formula=[f'$B2="{word}"'], fill=PatternFill("solid", start_color=color)),
    )
wb.save("tracker_with_status.xlsx")
print(pd.read_excel("tracker_with_status.xlsx").head())

The hand-applied fills are still present on the copied cells, so clear them if you want the rule to be the only source of colour: assign PatternFill(fill_type=None) to each task cell before saving.

Common pitfalls

Before writing the mapping dictionary, it pays to inventory what is actually in the file. A one-liner over the column — Counter(fill_key(c) for c in ws["A"][1:]) from collections — lists every distinct key and how often it appears, which catches the near-identical greens before they turn into a row labelled C6EFCF in your report.

Performance and scale notes

Styles are the expensive part of an .xlsx to load, and there is no streaming mode that keeps them in full. For a tracker of a few thousand rows a normal load takes well under a second. For a very large sheet, read the values with pandas or calamine for speed and use openpyxl only for the one or two columns whose formatting you need, or pre-convert the colours once and store the result.

Conclusion

Formatting is data whenever people use it that way, and openpyxl gives you everything needed to read it: fill.fgColor for shading, with a branch for RGB, theme and indexed colours, and the boolean properties of font for bold and strikethrough. Build a stable key per style, map it to a meaningful value, keep unknown keys visible, and check for conditional formatting before trusting any colour you read. Then write the result back with a real column so the next reader never has to decode colours again.

Frequently asked questions

Can pandas read cell colours? No. read_excel returns values only. You need openpyxl (or xlrd with formatting_info=True for old .xls files) to read fills and fonts, then merge those columns into your DataFrame.

Why does a red cell show as unfilled in openpyxl? Because the red comes from a conditional formatting rule, which Excel evaluates when it displays the sheet. openpyxl stores the rule but does not evaluate it, so the cell's own fill is empty.

How do I get the actual RGB value of a theme colour? You have to read the workbook's theme XML, find the palette slot and apply the tint. For classification a key built from the theme index and tint is enough and far simpler.

Can I write the status back as colours in a new file? Yes — assign PatternFill objects in openpyxl, or better, add a conditional format driven by the new Status column so the colour stays in step with the data.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting reliable data out of sheets built for people.
• Skip Hidden and Filtered Rows When Reading Excel — another piece of meaning stored outside the cell values.
• Styling Excel Cells with openpyxl — the write side of the same style objects.
• Highlight Cells Above a Threshold with openpyxl — replacing hand-applied colour with a rule.
• Iterate over Rows and Columns with openpyxl — the row-walking patterns used above.