Formatting Issues

How to Use Excel Conditional Formatting to Rank Next Eligible Athletes

Elise Williams Sep 27, 2026 869 views

Question details

The user needs to apply conditional formatting in Excel to highlight the next six eligible athletes (ranked 1 through 6) in red, based on specific criteria such as national rank and existing qualification status.

How to Use Excel Conditional Formatting to Rank Next Eligible Athletes

Product: Excel
Device & OS: not provided
Scenario: Ranking and highlighting specific rows based on eligibility and multiple conditions in an athletic ranking worksheet.
Observed behavior: The user has working cyan formatting for already qualified athletes, but needs a new formula-based rule to rank and format the next 6 eligible, unqualified athletes in column G with red formatting.

Before you start

Ensure your athlete dataset is properly sorted by points or national rank, and identify the exact columns containing the qualification status (e.g., Column I) and the target ranking position (e.g., Column G).

Solution 1Recommended

Apply Formula-Based Conditional Formatting to Highlight Eligible Athletes

Use a custom formula in the conditional formatting rules to evaluate both the qualification status and the target rank simultaneously.

When dealing with multiple conditions—such as checking if an athlete is not yet qualified in Column I and ranks between 1 and 6 in Column G—a formula-based conditional formatting rule is the most effective approach. This allows you to apply formatting dynamically based on data across multiple columns.

The exact formula depends on what values indicate 'qualified' or 'eligible' in your specific dataset. The formula must use mixed cell references so it applies correctly down the entire column.

Select the target range

Highlight the range of cells in Column G (e.g., G2:G100) where the red formatting should appear.

Create a new formatting rule

Go to the 'Home' tab on the Excel ribbon, click 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Choose the formula option

In the dialog box, select 'Use a formula to determine which cells to format'.

Enter the eligibility formula

Input a formula that checks both conditions. For example: =AND($I2<>"Qualified", $G2<=6, $G2>=1). Adjust 'Qualified' to match the exact text or value in Column I that indicates a pilot is already qualified.

Apply the red formatting

Click the 'Format' button, navigate to the 'Fill' or 'Font' tab, choose your desired red color, and click 'OK'. Click 'OK' again to apply the rule to your worksheet.

Apply Formula-Based Conditional Formatting to Highlight Eligible Athletes

Relative vs. Absolute References: Ensure you use mixed references (like $I2 and $G2) rather than absolute references (like $I$2). This ensures the formula evaluates the correct row dynamically as it moves down the dataset.

Efficient Data Formatting

Easily Rank and Highlight Data with WPS Spreadsheet

WPS Spreadsheet offers robust conditional formatting tools that make it simple to apply complex, formula-based rules to large datasets. It perfectly supports advanced formulas for dynamic rankings and athlete eligibility.

1. Open your dataset: Launch WPS Spreadsheet and open your athlete ranking workbook.
2. Select the ranking column: Highlight the cells in your target ranking column (e.g., Column G).
3. Access conditional formatting: Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
4. Input the formula: Select 'Use a formula to determine which cells to format', input your multi-condition eligibility formula, set the red format, and click 'OK'.

100% compatible with Microsoft Excel conditional formatting rules and formulasIntuitive interface for managing multiple formatting conditions without freezingLightweight application that handles complex sorting and ranking datasets smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong athlete rows?

This usually happens due to incorrect cell references. Make sure your formula uses mixed references (e.g., $G2) rather than absolute references (e.g., $G$2) for the row numbers, and ensure the row number in the formula matches the very first row of your selected range.

Can I highlight the entire athlete row instead of just the rank column?

Yes. To highlight the entire row, select the entire dataset range (e.g., A2:Z100) before creating the rule, instead of just Column G. Ensure your formula locks the column reference with a dollar sign (like $G2 and $I2) so every cell in the row looks at the correct columns for evaluation.

How do I manage the cyan and red conditional formatting rules together?

Go to the 'Home' tab, click 'Conditional Formatting', and select 'Manage Rules'. Here, you can view both the cyan and red rules, edit them, change their order of execution using the up/down arrows, and set priorities by checking the 'Stop If True' box if necessary.

Document & File Issues