Formatting Issues

How to Create Conditional Formatting Rules for a Training Matrix in Excel

Tauseeq Magsi Oct 8, 2026 868 views

Question details

The user needs to set up conditional formatting in an Excel training matrix to identify whether training courses are current, expired, or due for renewal within the next three months.

How to Create Conditional Formatting Rules for a Training Matrix in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an employee training tracking spreadsheet where each training item is valid for a specific period (e.g., two years), requiring automatic status updates based on the current date.
Observed behavior: Requires dynamic color-coded highlighting (red, orange, green) based on calculated date formulas relative to today's date, along with proper handling of blank cells.

Before you start

Ensure that your training dates are formatted as actual date values in Excel rather than plain text, so the date calculation formulas can accurately process them.

Solution 1Recommended

Apply Formula-Based Conditional Formatting Rules

Use Excel's New Rule feature along with the TODAY() and EDATE() functions to automatically format your training dates based on their expiration status.

To accurately color-code a training matrix, you will need to apply multiple conditional formatting rules. The order in which these rules are arranged in the Manage Rules dialog is crucial because Excel processes them from top to bottom.

Select the target date range

Highlight the specific range of cells in your training matrix that contain the training expiration dates.

Create a rule for expired training (Red)

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule'. Choose 'Format only cells that contain'. Set the rule description to 'Cell Value' is 'less than' and enter `=TODAY()` in the formula box. Click 'Format', choose a red fill color, and click OK.

Create a rule for training expiring soon (Orange)

With the cells still selected, go back to 'Conditional Formatting' > 'New Rule' > 'Format only cells that contain'. Set it to 'Cell Value' is 'less than' and enter `=EDATE(TODAY(),3)`. Click 'Format', choose an orange fill color, and click OK.

Create a rule for current valid training (Green)

Add a third rule following the same steps. Set the condition to 'Cell Value' is 'greater than or equal to' and enter `=EDATE(TODAY(),3)`. Click 'Format', choose a green fill color, and click OK.

Manage rule priority and handle blank cells

Go to 'Conditional Formatting' > 'Manage Rules'. Ensure the rules are ordered exactly from top to bottom: Red, Orange, then Green. If blank cells are turning red, add a new rule for 'Format only cells that contain' > 'Blanks', set no specific format, place it at the very top of the list, and check the 'Stop If True' box.

Apply Formula-Based Conditional Formatting Rules

Adjusting for Completion Dates: If your spreadsheet tracks the completion date rather than the expiration date, and training is valid for 2 years (24 months), you will need to adjust the rules. For example, to check if it has expired, use a formula rule: `=EDATE(A2, 24) < TODAY()`.

Track Training Matrices Easily with WPS Spreadsheet

WPS Office provides a robust spreadsheet application with comprehensive support for advanced conditional formatting, date functions, and rule management, making it incredibly easy to track training compliance.

1. Open your matrix in WPS Spreadsheet: Launch WPS Office and open your training matrix document.
2. Access Conditional Formatting: Select your date cells, navigate to the Home tab, and click on 'Conditional Formatting'.
3. Apply Rules: Select 'New Rule' and input the same `=TODAY()` and `=EDATE(TODAY(),3)` formulas to highlight expired and expiring training perfectly.

100% compatible with Microsoft Excel (.xlsx) file formatsSeamlessly supports advanced date formulas like TODAY() and EDATE()Intuitive Conditional Formatting rule manager to easily prioritize colorsFree, lightweight, and fast performance for large employee data matrices

Frequently Asked Questions

How do I account for a 2-year validity if my matrix only lists the completion date?

If your cells contain the completion date rather than the expiration date, you need to adjust your formulas to add two years (24 months). Instead of checking if the cell is less than TODAY(), use the 'Use a formula to determine which cells to format' option and enter `=EDATE(A2, 24) < TODAY()` (assuming A2 is your first date cell).

Why are empty cells in my training matrix turning red?

Excel treats blank cells as a zero value, which corresponds to a date in the year 1900. Since 1900 is strictly less than today's date, the red conditional formatting rule is applied. To fix this, add a rule to format 'Blanks' with no fill color, move it to the top of your rules manager, and check the 'Stop If True' box.

Can I use conditional formatting to highlight the entire row instead of just the date cell?

Yes. Select your entire data range (e.g., A2:F100) instead of just the date column. Go to Conditional Formatting > New Rule > 'Use a formula to determine which cells to format'. Enter your formula with an absolute column reference locking the date column (for example, `=$C2<TODAY()`). This will apply the highlight color across the entire row.

Document & File Issues