Highlight Top and Bottom Performer using Conditional Formatting

You can highlight the top and bottom performer using conditional formatting in Excel so the best and the weakest employee light up the moment you pick an option from a drop-down. In this tutorial you build a small performance table where cell C1 offers Top Performer and Bottom Performer. Choose one and the whole row of the highest or lowest performance score turns green or red. Change any score and the highlight moves by itself. There is no VBA here, only a Data Validation list, two formula rules and the AND, MAX and MIN functions.

⬇️  Download the Practice File
Single-sheet workbook · .xlsx (no macros) · Excel 2010 and later

Watch the Video Tutorial

In the video, PK copies the employee table to a new sheet, adds the drop-down in C1 with Alt+D+L, and then writes one formula rule for the top performer and one for the bottom performer.

What You Will Build

• An employee table with EMP ID, EMP Name, Supervisor, Location and Performance Score.
• A drop-down in C1 with two choices: Top Performer and Bottom Performer.
• A rule that paints the whole row of the highest score with a green gradient.
• A rule that paints the whole row of the lowest score with a red gradient.
• A fully dynamic result: change a score or the drop-down and the highlight follows.

Part 1 – Prepare the Data and the Drop-Down

Step 1: Set up the performance table

The practice file has 17 employees in rows 4 to 20. The headers sit in row 3:

A3: EMP ID   B3: EMP Name   C3: Supervisor   D3: Location   E3: Performance Score
A4: 21899    B4: EMP-1      C4: Supervisor-1 D4: India      E4: 98%
...
A20: 12870   B20: EMP-17    C20: Supervisor-3 D20: UK       E20: 71%

Give the table borders, centre alignment and a header colour, and turn off the sheet gridlines from View > Gridlines. Select E4:E20 and press Ctrl+Shift+5 so the scores show as percentages. The scores are stored as decimals, such as 0.98 for 98%.

Step 2: Add the drop-down in C1

Type Select in B1 as a label. Then click C1 and open Data Validation, either with Data > Data Validation or with the keyboard shortcut Alt+D+L. Set:

• Allow: List
• Source: Top Performer,Bottom Performer

Click OK and pick Top Performer. Nothing is highlighted yet, because the rules do not exist yet. The exact spelling in this list matters: the formulas in the next part compare C1 with these same two words.

Part 2 – Highlight the Top Performer Using Conditional Formatting

Step 3: Select the full table with E4 as the active cell

Select the data rows A4:E20 so that E4 is the active (white) cell. Every formula you type in a conditional formatting rule is written for the active cell, and Excel then adjusts it for every other cell in the selection. Starting from E4 is what makes the row references below line up.

Step 4: Create the formula rule

Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. In Format values where this formula is true, enter:

=AND($C$1="Top Performer",$E4=MAX($E$4:$E$20))

When the formula returns TRUE for a cell, that cell gets the format. When it returns FALSE, nothing changes.

How the formula works

• $C$1="Top Performer" is the first test. It is TRUE only when the drop-down shows Top Performer. C1 is fully locked because every cell must look at the same drop-down.
• MAX($E$4:$E$20) returns the highest score in the table. The range is fully locked so it never shifts.
• $E4=MAX(...) is the second test. The dollar sign sits before the column only. The column stays on E for cells in A, B, C and D, so the whole row checks the score in its own column E. The row number is free, so row 5 checks E5, row 6 checks E6 and so on.
• AND(...) returns TRUE only when both tests are TRUE, so the row is highlighted only when Top Performer is selected and that row holds the highest score.

Step 5: Choose a gradient format

Click Format. On the Fill tab click Fill Effects, choose Two colors with green as Color 1 and light green as Color 2, keep the Horizontal shading style and pick a variant. On the Font tab choose Bold Italic and a white font colour. Click OK twice.

In the practice file the highest score is 99% for EMP-9 in row 12, so that row turns green. Type a higher score in any other row and the green bar jumps there.

Part 3 – Highlight the Bottom Performer

Step 6: Add a second rule with MIN

Select A4:E20 again, with E4 active. Open Home > Conditional Formatting > Manage Rules (the video uses the legacy shortcut Alt+O+D), click New Rule, choose Use a formula to determine which cells to format and enter:

=AND($C$1="Bottom Performer",$E4=MIN($E$4:$E$20))

It is the same logic as the top rule. Only the drop-down text changes, and MIN replaces MAX so the test finds the lowest score.

Step 7: Format it in red

Click Format, set the font to Bold Italic and white, then go to Fill > Fill Effects, choose Two colors with two shades of red and pick a variant. Click OK until you are back on the sheet.

Now switch C1 to Bottom Performer. In the practice file the lowest score is 20% for EMP-14 in row 17, so that row turns red. Switch back to Top Performer and the green row returns. Both rules apply to the same range, but only one can be TRUE at a time because C1 can hold only one value.

What happens with a tie?

If two employees share the highest score, both rows are highlighted, because both of them are equal to the MAX. The same is true for two employees who share the lowest score. In the video PK sets two scores to 99% and both rows turn green. That is usually what you want in a review, as both people really are the top performers.

Common Errors and Fixes

• Excel says the formula is missing a parenthesis. You need two closing brackets at the end: one for MAX or MIN and one for AND. This is exactly the message PK gets in the video before adding the second bracket.
• Only column E is highlighted, not the whole row. The rule was applied to E4:E20 only. Open Manage Rules and set Applies to to =$A$4:$E$20.
• The wrong row is highlighted, or nothing is. The row reference is locked ($E$4 instead of $E4), or E4 was not the active cell when you typed the rule. Re-enter it with E4 active.
• The drop-down changes but the colour never appears. The text in the formula does not match the list item exactly, for example an extra space. Copy the words from the Data Validation source.

Tips

• Keep the score range in both rules identical to your data. If you add employees, extend Applies to and the MAX or MIN range together. (Addition)
• To show the top three instead of only the top one, a rule such as =$E4>=LARGE($E$4:$E$20,3) works the same way; use SMALL for the bottom three. (Addition, not in the video)
• The built-in Top/Bottom Rules menu can colour the single highest cell, but it cannot colour the whole row or react to a drop-down. That is why a formula rule is used here. (Addition)
• Soft gradients with white bold text look clean, but a plain fill prints better on black-and-white printers.
• Microsoft explains every rule type in Use conditional formatting to highlight information in Excel.

Want It Ready-Made?

If you track people every month, the Employee Performance Tracking Report in Excel and the Employee Performance Tracking Dashboard in Excel on NextGenTemplates.com give you a finished layout to fill in. You can also browse all HR and payroll templates in Excel. To get fluent with AND, MAX, MIN and the other formulas behind rules like these, join Excel AI Mastery: 100 Formulas on NextGenTemplates Academy.

Related Tutorials

• Excel Conditional Formatting Formula to Highlight Rows
• Drop-Down List in Excel with a Data Validation List
• Dynamic Conditional Formatting in Excel
• Highlight Unmatched Cells using Conditional Formatting
• Complete Conditional Formatting Course

Frequently Asked Questions

How do I highlight the entire row of the top performer in Excel?

Select all data rows from column A to E with the first score cell active, add a formula rule that compares the score in column E with the MAX of the score range, and lock the column with a dollar sign but leave the row free.

Why do I need the AND function in this rule?

AND combines two tests: the drop-down must show the matching option and the row must hold the highest or lowest score. Only when both are true is the row coloured, which is what makes the highlight switch with the drop-down.

Can I do this without VBA?

Yes. The whole effect uses a Data Validation list and two conditional formatting rules. No macros are needed and the file stays a normal .xlsx workbook.

What happens if two employees have the same top score?

Both rows are highlighted, because both scores are equal to the maximum. The same applies to a tie for the lowest score.

How do I extend the rules when I add more employees?

Open Home, Conditional Formatting, Manage Rules, and change both the Applies to range and the score range inside each formula so they cover the new rows.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

To highlight the top and bottom performer using conditional formatting, you only need a drop-down in C1 and two formula rules built with AND, MAX and MIN. The mixed reference $E4 colours the whole row, the locked $C$1 ties both rules to the drop-down, and the result updates the moment a score changes. Download the practice file, rebuild the two rules yourself, and then reuse the same pattern for sales reps, products or any other ranking.