Master Excel‑Style Conditional Formatting in Pandas: Highlight, Color‑Scale, Data Bars & More

1. Overview

We introduce table conditional‑formatting visualization, using Excel as a reference and showing how Pandas can achieve similar effects through its df.style API.

The core idea is to apply various conditional‑formatting rules (highlight cells, top/bottom rules, data bars, color scales, icon sets, rule manager) to make data tables more informative.

2. Highlight Cells

Pandas provides functions to highlight missing values, maximum, minimum, and range values, mirroring Excel's built‑in styles.

2.1 Highlight Missing Values

df.style.highlight_null()

Signature:
df.style.highlight_null(
    null_color: 'str' = 'red',
    subset: 'Subset | None' = None,
    props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight missing values with a style.

null_color specifies the background color (default red). subset limits the operation to specific rows or columns. props allows custom CSS properties.

You can change the highlight color, e.g., to orange or a hex code.

2.2 Highlight Maximum Value

df.style.highlight_max()

Signature:
df.style.highlight_max(
    subset: 'Subset | None' = None,
    color: 'str' = 'yellow',
    axis: 'Axis | None' = 0,
    props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight the maximum with a style.

subset limits rows/columns. color sets the highlight color (default yellow). axis chooses row, column, or all (default column).

For Chinese columns, you may need to set the index or specify subset to avoid unexpected behavior.

2.3 Highlight Minimum Value

df.style.highlight_min()

2.4 Highlight Range Values

df.style.highlight_between()

Signature:
df.style.highlight_between(
    subset: 'Subset | None' = None,
    color: 'str' = 'yellow',
    axis: 'Axis | None' = 0,
    left: 'Scalar | Sequence | None' = None,
    right: 'Scalar | Sequence | None' = None,
    inclusive: 'str' = 'both',
    props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight a defined range with a style.

left and right define the interval boundaries. inclusive controls closed/open interval (both, neither, left, right). props custom CSS.

Example: highlight cells with values in [20, 30] using white font on a purple background.

2.5 Highlight Quantiles

df.style.highlight_quantile()

Signature:
df.style.highlight_quantile(
    subset: 'Subset | None' = None,
    color: 'str' = 'yellow',
    axis: 'Axis | None' = 0,
    q_left: 'float' = 0.0,
    q_right: 'float' = 1.0,
    interpolation: 'str' = 'linear',
    inclusive: 'str' = 'both',
    props: 'str | None' = None,
) -> 'Styler'
Docstring:
Highlight values defined by a quantile with a style.

q_left and q_right set quantile bounds.

Example: highlight the top 15% of each column.

3. Color Scales (Background & Text Gradients)

3.1 Background Gradient

In Excel you use Conditional Formatting → Color Scale. In Pandas you can apply df.style.background_gradient():

Signature:
df.style.background_gradient(
    cmap='PuBu',
    low: 'float' = 0,
    high: 'float' = 0,
    axis: 'Axis | None' = 0,
    subset: 'Subset | None' = None,
    text_color_threshold: 'float' = 0.408,
    vmin: 'float | None' = None,
    vmax: 'float | None' = None,
    gmap: 'Sequence | None' = None,
) -> 'Styler'
Docstring:
Color the background in a gradient style.

cmap selects a Matplotlib colormap. low and high define color range (0‑1). text_color_threshold controls when text switches color.

3.2 Text Gradient

Use df.style.text_gradient() with similar parameters to color the text itself.

4. Data Bars

Excel provides Conditional Formatting → Data Bars. Pandas offers df.style.bar():

Signature:
df.style.bar(
    subset: 'Subset | None' = None,
    axis: 'Axis | None' = 0,
    color='#d65f5f',
    width: 'float' = 100,
    align='left',
    vmin: 'float | None' = None,
    vmax: 'float | None' = None,
) -> 'Styler'
Docstring:
Draw bar chart in the cell backgrounds.

color sets bar color, width length (0‑100), align can be left, zero, or mid.

5. Data Formatting

Adjust display format with df.style.format():

Signature:
df.style.format(
    formatter: 'ExtFormatter | None' = None,
    subset: 'Subset | None' = None,
    na_rep: 'str | None' = None,
    precision: 'int | None' = None,
    decimal: 'str' = '.',
    thousands: 'str | None' = None,
    escape: 'str | None' = None,
) -> 'StylerRenderer'
Docstring:
Format the text display value of cells.

formatter custom format string. na_rep representation for missing values. precision number of decimal places. thousands thousands separator.

Examples: add a unit suffix, hide missing values, set decimal places to zero, format specific columns.

6. Custom Formatting Functions

Use applymap() for element‑wise styling or apply() for row/column/table‑wise styling. You can also combine with NumPy where and repeat for performance.

7. Other Utilities

Additional operations include adding a caption, hiding the index, hiding specific columns, and setting arbitrary CSS properties with df.style.set_properties().

Export the styled DataFrame to Excel with to_excel or to HTML with to_html to preserve the visual formatting.

Feel free to experiment with these styling options in your own projects to create more insightful data presentations.