Conditional formatting

Conditional formatting is a technique where you highlight or format data differently based on certain rules. It’s commonly used in spreadsheets, dashboards, and even programming to make patterns, trends, or outliers stand out.

For a novice, think of it as:

“If this value is above X, make it green; if it’s below Y, make it red.”

1️⃣ Why Use Conditional Formatting?

• Quick insights: Instantly spot high/low values
• Highlight anomalies: Detect errors or outliers
• Visual storytelling: Makes data easy to understand
• Performance tracking: See KPIs visually

2️⃣ Conditional Formatting in Tables

Imagine this table of sales:

| Product | Sales |
|---------|-------|
| A       | 1000  |
| B       | 1500  |
| C       | 700   |

We can apply conditional formatting like:

• Green if Sales > 1200
• Red if Sales < 800

3️⃣ Conditional Formatting in Excel / Google Sheets

1. Select your data range
2. Go to Format → Conditional Formatting
3. Set a rule:

• Greater than 1200 → Green
• Less than 800 → Red

1. Apply

✅ This instantly highlights high/low sales.

4️⃣ Conditional Formatting in Python (Pandas)

We can use the .style property of a DataFrame.

Example:

import pandas as pd

data = {
    "Product": ["A", "B", "C"],
    "Sales": [1000, 1500, 700]
}

df = pd.DataFrame(data)

# Conditional formatting: highlight sales > 1200 in green
def highlight_high(val):
    color = 'green' if val > 1200 else ''
    return f'background-color: {color}'

df.style.applymap(highlight_high, subset=['Sales'])

Explanation:

• applymap() applies a function to each cell
• highlight_high() sets a background color for cells > 1200

5️⃣ Multiple Conditions Example

We can highlight high, medium, low values differently:

def sales_color(val):
    if val > 1200:
        color = 'green'
    elif val < 800:
        color = 'red'
    else:
        color = 'yellow'
    return f'background-color: {color}'

df.style.applymap(sales_color, subset=['Sales'])

ProductSalesA1000B1500C700

6️⃣ Conditional Formatting in Dashboards

Conditional formatting isn’t just for tables. You can use it in:

• KPI cards → change color if target met
• Charts → color bars differently based on value
• Gauge charts → color zones green/yellow/red

Example Concept: KPI Card

KPIValueColorTotal Sales1500GreenAvg Sales700RedGrowth Rate %12Yellow

7️⃣ Using Conditional Colors in Matplotlib / Plotly

Example: Color bars based on value

import matplotlib.pyplot as plt

sales = [1000, 1500, 700]
products = ['A', 'B', 'C']

colors = ['green' if x > 1200 else 'red' if x < 800 else 'yellow' for x in sales]

plt.bar(products, sales, color=colors)
plt.title("Sales with Conditional Colors")
plt.show()

✅ Bars will be colored according to rules.

8️⃣ Summary: When to Use Conditional Formatting

| Use Case | Purpose |
|----------|---------|
| Highlight top performers | Quickly identify best values |
| Highlight underperformers | Spot problems or outliers |
| KPI dashboards | Make performance visual and intuitive |
| Financial reports | Show profit/loss trends |
| Any table/chart | Add visual meaning to raw numbers |

FULL COMPILED PYTHON CODE (Conditional Formatting Examples)

import pandas as pd
import matplotlib.pyplot as plt

# ----------------------------
# Sample Data
# ----------------------------
data = {
    "Product": ["A", "B", "C"],
    "Sales": [1000, 1500, 700]
}

df = pd.DataFrame(data)

# ----------------------------
# Conditional Formatting: Single Rule (Sales > 1200)
# ----------------------------
def highlight_high(val):
    color = 'green' if val > 1200 else ''
    return f'background-color: {color}'

df.style.applymap(highlight_high, subset=['Sales'])

# ----------------------------
# Conditional Formatting: Multiple Rules
# ----------------------------
def sales_color(val):
    if val > 1200:
        color = 'green'
    elif val < 800:
        color = 'red'
    else:
        color = 'yellow'
    return f'background-color: {color}'

df.style.applymap(sales_color, subset=['Sales'])

# ----------------------------
# Conditional Formatting in Bar Chart
# ----------------------------
sales = [1000, 1500, 700]
products = ['A', 'B', 'C']

colors = ['green' if x > 1200 else 'red' if x < 800 else 'yellow' for x in sales]

plt.bar(products, sales, color=colors)
plt.title("Sales with Conditional Colors")
plt.show()