Image by Editor | Midjourney

If you’ve worked with project tracking or task management in Excel, you know how valuable visual progress indicators can be. While Excel’s built-in conditional formatting makes this easy through the user interface, automating the creation of these progress bars programmatically requires a different approach. In this article, we’ll use openpyxl to create sophisticated progress bars that not only show completion rates but also incorporate color-coded alerts for different thresholds.

This article builds upon our introductory exploration of Excel automation with Python, diving specifically into openpyxl’s formatting capabilities. We’ll start with basic progress tracking and gradually add features like color-coding for low progress and over-completion warnings.

Setting Up the Basic Structure

Let’s begin with setting up our basic Excel structure and data. This first step creates a clean workbook with properly formatted headers and sample task data:

# 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 = "Progress Bars"

# Add headers with formatting
ws['A1'] = "Task"
ws['B1'] = "Progress"
header_font = Font(bold=True)
ws['A1'].font = header_font
ws['B1'].font = header_font

# Add sample data
tasks = [
    ("Task 1", 0.45),
    ("Task 2", 0.78),
    ("Task 3", 0.92),
    ("Task 4", 0.15),
    ("Task 5", 0.63)
]

for idx, (task, progress) in enumerate(tasks, start=2):
    ws[f'A{idx}'] = task
    ws[f'B{idx}'] = progress

wb.save('progress_bars_step1.xlsx')

This initial code establishes our worksheet’s foundation. We create a new workbook and set up two columns: one for task names and another for progress values. The progress values are stored as decimals between 0 and 1, where 0.45 represents 45% completion. Notice how we’ve formatted the headers in bold to make them stand out from the data. If you open this workbook in Excel, you should see: 

Image By Author

The code uses Python’s enumerate function with a start parameter of 2, ensuring our data begins in row 2 (after the headers). This organization gives us a clean base to build upon as we add more sophisticated formatting in the next steps.

Adding Visual Progress Bars

With our basic structure in place, we can now add the visual progress bars. The openpyxl library provides a powerful feature called DataBarRule that allows us to create dynamic progress bars based on cell values. These bars automatically adjust their length to represent the completion percentage.

# Block 2: Adding Basic Progress Bars
from openpyxl.formatting.rule import DataBarRule

# Create the data bar rule
data_bar = DataBarRule(
    start_type='num',
    start_value=0,
    end_type='num',
    end_value=1,
    color="00B050",  # Green color
    showValue=True,
    minLength=None,
    maxLength=None
)

# Apply the data bar formatting
ws.conditional_formatting.add(f'B2:B{len(tasks)+1}', data_bar)
wb.save('progress_bars_step2.xlsx')

In this code block, we configure the progress bars with specific parameters:

• The start_value and end_value parameters define our scale from 0 to 1
• We set the bar color to a professional green shade (00B050)
• The showValue parameter ensures we see both the bar and the numerical percentage
• By leaving minLength and maxLength as None, we allow Excel to automatically size the bars

When you open this second workbook in Excel, you should see: 

Image By Author

The conditional_formatting.add() method applies our formatting rule to the progress column. We use string formatting to dynamically set the range based on our data size, making the code adaptable to different numbers of tasks.

Enhancing with Color-Coded Thresholds

To make our progress tracking more informative, we can add color-coded alerts that highlight tasks needing attention. We’ll implement two specific scenarios: marking tasks with low completion rates in red and flagging over-allocated tasks in orange.

# Block 3: Adding Color Thresholds
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

# Add rule for values below 30%
low_progress_rule = CellIsRule(
    operator="lessThan",
    formula=['0.3'],
    stopIfTrue=True,
    fill=PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")  # Red
)
ws.conditional_formatting.add(f'B2:B{ws.max_row}', low_progress_rule)

# Add a task with >100% progress
ws[f'A7'] = "Task 6"
ws[f'B7'] = 1.25  # 125%

# Add rule for values over 100%
over_hundred_rule = CellIsRule(
    operator="greaterThan",
    formula=['1'],
    stopIfTrue=True,
    fill=PatternFill(start_color="FFC000", end_color="FFC000", fill_type="solid")  # Orange
)
ws.conditional_formatting.add(f'B2:B{ws.max_row}', over_hundred_rule)

wb.save('progress_bars_final.xlsx')

The final code block introduces two new conditional formatting rules using CellIsRule. Unlike the data bars we created earlier, these rules change the cell’s background color based on specific conditions:

1. The first rule identifies tasks with less than 30% completion, marking them in red to highlight tasks that might need immediate attention.

2. The second rule spots tasks exceeding 100% progress, marking them in orange. This helps identify potential over-allocation or estimation issues in your project tracking.

When you open this third workbook in Excel, you should see the following : 

Image By Author

Openpyxl processes these rules in order, with later rules taking precedence over earlier ones. This allows the color alerts to work alongside our progress bars, creating a rich visual tracking system.

Conclusion

By combining openpyxl’s formatting capabilities with conditional rules, we’ve created an automated system for visualizing task progress. This approach scales easily – simply add more tasks to the data list, and the formatting rules will automatically apply to the new entries. You can also adjust the thresholds and colors to match your specific project management needs.

The complete code provides a template that you can adapt for various tracking scenarios, from project management to performance monitoring. For more complex scenarios, you might consider adding additional thresholds or incorporating data validation rules to ensure progress values stay within expected ranges.