Image by Editor | Midjourney
Excel’s IF functions help us create complex conditional logic in spreadsheets. While you can set these rules manually in Excel, automating them with Python and openpyxl lets you apply consistent logic across multiple workbooks and handle larger datasets efficiently. In this article, we’ll walk through creating an Excel workbook that analyzes basketball player statistics using different types of conditional logic.
Setting Up the Basic Structure
To start, we’ll create a simple Excel workbook containing basketball player statistics. We’ll track each player’s team, position, and points scored. This base structure will serve as the foundation for our conditional formatting examples.
Here’s the code to create our initial workbook:
# Block 1: Basic Setup and Data Entry
from openpyxl import Workbook
from openpyxl.styles import Font
# Create a new workbook and select the active sheet
wb = Workbook()
ws = wb.active
ws.title = "Player Stats"
# Add headers with formatting
headers = ['Team', 'Position', 'Points']
for col, header in enumerate(headers, 1):
ws.cell(row=1, column=col, value=header)
ws.cell(row=1, column=col).font = Font(bold=True)
# Add sample data
data = [
('Mavs', 'Guard', 28),
('Lakers', 'Forward', 12),
('Mavs', 'Guard', 15),
('Bulls', 'Center', 22),
('Mavs', 'Forward', 19)
]
for row, (team, position, points) in enumerate(data, 2):
ws.cell(row=row, column=1, value=team)
ws.cell(row=row, column=2, value=position)
ws.cell(row=row, column=3, value=points)
# Adjust column widths for better readability
for col in ws.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = (max_length + 2)
ws.column_dimensions[column].width = adjusted_width
wb.save('player_stats_step1.xlsx')
The code above:
- Creates a new Excel workbook.
- Sets up column headers with bold formatting.
- Adds sample player data.
- Adjusts column widths automatically based on content
- Saves the workbook
When you open the generated Excel file, you’ll see:
Image By Author
This gives us a clean starting point for adding our conditional logic in the next sections.
Adding Performance Ratings with Nested IF Logic
Let’s implement a performance rating system based on points scored. We’ll add a new column that rates each player’s performance as “Bad”, “OK”, “Good”, or “Great” using color-coded cells. This replicates Excel’s nested IF functions using Python logic.
The rules we’ll implement are:
- Less than 15 points = “Bad” (Red)
- 15-19 points = “OK” (Yellow)
- 20-24 points = “Good” (Green)
- 25+ points = “Great” (Green)
Here’s how to add these conditional ratings:
# Block 2: Adding Performance Ratings with Nested IF Logic
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
# Load the existing workbook
wb = load_workbook('player_stats_step1.xlsx')
ws = wb.active
# Add Performance column header
ws.cell(row=1, column=4, value='Performance')
ws.cell(row=1, column=4).font = Font(bold=True)
# Define fill patterns for different performance levels
red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
yellow_fill = PatternFill(start_color='FFFFFF00', end_color='FFFFFF00', fill_type='solid')
green_fill = PatternFill(start_color='FF00FF00', end_color='FF00FF00', fill_type='solid')
# Apply nested IF logic and formatting
for row in range(2, ws.max_row + 1):
points = ws.cell(row=row, column=3).value
# Implement nested IF logic
if points < 15:
rating = "Bad"
fill = red_fill
elif points < 20:
rating = "OK"
fill = yellow_fill
elif points < 25:
rating = "Good"
fill = green_fill
else:
rating = "Great"
fill = green_fill
# Apply rating and fill
cell = ws.cell(row=row, column=4, value=rating)
cell.fill = fill
# Adjust the new column width
ws.column_dimensions['D'].width = 12
wb.save('player_stats_step2.xlsx')
Let’s break down the key components:
- We use PatternFill to create three different cell colors.
- The for loop processes each player’s points and applies the appropriate rating.
- Each rating gets both a text value and a corresponding cell color.
- We use Python’s if/elif/else structure to replicate Excel’s nested IF logic.
The result shows each player’s performance with clear visual indicators:
Image By Author
This combination of conditional logic and cell formatting makes it easy to spot high and low performers at a glance. In the next section, we’ll add more complex conditions using AND logic to determine bonus eligibility.
Adding Bonus Eligibility with AND Logic
Now we’ll add a “Bonus Eligible” column that checks multiple conditions simultaneously. A player qualifies for a bonus if they meet all of these criteria:
- Plays for the Mavs team
- Position is Guard
- Scored more than 25 points
This example demonstrates how to implement Excel’s AND function logic using Python’s logical operators. Here’s the code:
# Block 3: Adding Bonus Eligibility with AND Conditions
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
# Load the workbook from previous step
wb = load_workbook('player_stats_step2.xlsx')
ws = wb.active
# Add Bonus Eligible column header
ws.cell(row=1, column=5, value='Bonus Eligible')
ws.cell(row=1, column=5).font = Font(bold=True)
# Define fill pattern for eligible players
green_fill = PatternFill(start_color='FF90EE90', end_color='FF90EE90', fill_type='solid')
# Apply AND conditions for each player
for row in range(2, ws.max_row + 1):
team = ws.cell(row=row, column=1).value
position = ws.cell(row=row, column=2).value
points = ws.cell(row=row, column=3).value
# Check if all conditions are met (equivalent to Excel's AND)
is_eligible = (
team == 'Mavs' and
position == 'Guard' and
points > 25
)
# Apply result and conditional formatting
cell = ws.cell(row=row, column=5, value='Yes' if is_eligible else 'No')
if is_eligible:
cell.fill = green_fill
# Adjust the new column width
ws.column_dimensions['E'].width = 13
wb.save('player_stats_step3.xlsx')
Key points about this implementation:
- We use Python’s and operator to combine multiple conditions.
- The code checks all three criteria in a single expression.
- We use a lighter green fill (FF90EE90) to distinguish bonus eligibility from performance ratings.
- The ternary operator (value=’Yes’ if is_eligible else ‘No’) provides a concise way to set cell values.
Here’s how the spreadsheet looks after adding bonus eligibility:
Image By Author
Looking at the results, only one player meets all three criteria – the Mavs guard who scored 28 points. This demonstrates how combining multiple conditions can help identify specific subsets of players who meet all required criteria.
In the next section, we’ll explore how to implement OR logic to identify players with special status based on meeting any one of several conditions.
Adding Special Status with OR Logic
Finally, we’ll add a “Special Status” column that marks players who meet any of these conditions:
- Plays for the Mavs team
- Position is Guard
- Scored more than 25 points
Unlike the AND logic used for bonus eligibility, this demonstrates Excel’s OR function by marking players who satisfy at least one condition. Here’s how to implement it:
# Block 4: Adding Special Status with OR Conditions
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
# Load the workbook from previous step
wb = load_workbook('player_stats_step3.xlsx')
ws = wb.active
# Add Special Status column header
ws.cell(row=1, column=6, value='Special Status')
ws.cell(row=1, column=6).font = Font(bold=True)
# Define fill pattern for special status
blue_fill = PatternFill(start_color='FFB0C4DE', end_color='FFB0C4DE', fill_type='solid')
# Apply OR conditions for each player
for row in range(2, ws.max_row + 1):
team = ws.cell(row=row, column=1).value
position = ws.cell(row=row, column=2).value
points = ws.cell(row=row, column=3).value
# Check if any condition is met (equivalent to Excel's OR)
has_special_status = (
team == 'Mavs' or
position == 'Guard' or
points > 25
)
# Apply result and conditional formatting
cell = ws.cell(row=row, column=6, value='Yes' if has_special_status else 'No')
if has_special_status:
cell.fill = blue_fill
# Adjust the new column width
ws.column_dimensions['F'].width = 13
wb.save('player_stats_final.xlsx')
Key aspects of this implementation:
- We use Python’s or operator to check multiple conditions.
- A light blue fill (FFB0C4DE) visually distinguishes special status from other indicators.
- Players qualify if they meet any single condition, not all conditions.
- The code structure mirrors our previous AND logic, but with more inclusive results.
Here’s the final spreadsheet with all our conditional logic applied:
Image By Author
Looking at the results, more players qualify for special status than for bonus eligibility. This makes sense since they only need to meet one condition rather than all conditions. The Mavs guards receive special status for multiple reasons (team and position), while other Mavs players qualify based on their team alone.
This completes our implementation of Excel-style conditional logic using openpyxl. We’ve shown how to:
- Apply nested IF conditions with color coding
- Implement AND logic for strict multi-criteria checks
- Use OR logic for flexible qualification rules
- Format cells based on various conditions
Each technique serves different analytical needs, from simple performance ratings to complex multi-criteria evaluations.
