This tutorial demonstrates how to highlight top or bottom N values in an Excel dataset using conditional formatting. We'll explore Excel's built-in tools and create custom rules using formulas for greater flexibility.

Methods for Highlighting Top/Bottom Values:

We'll cover three approaches: using built-in rules, enhancing formatting options, and employing formulas for dynamic control.

1. Built-in Top/Bottom Rules:

This is the quickest method for highlighting a fixed number (e.g., top 3, bottom 10) of values.

1. Select your data range.
2. Go to the Home tab, click Conditional Formatting, then Top/Bottom Rules, and choose either Top 10 Items or Bottom 10 Items.
3. Specify the number of items to highlight and select a formatting style. You can customize the formatting using Custom Format.

2. Enhanced Formatting Options:

For more control over the appearance, create a new rule:

1. Select your data range.
2. Go to Conditional Formatting > New Rule.
3. Choose "Format only top or bottom ranked values".
4. Select Top or Bottom, enter the number of values, and click Format to customize the font, border, and fill.

3. Formula-Based Conditional Formatting:

This provides the most flexibility, allowing you to change the number of highlighted values without modifying the rule itself.

1. Create input cells (e.g., F2 for top N, F3 for bottom N). Enter the desired number of values.
2. Select your data range (e.g., A2:C8).
3. Go to Conditional Formatting > New Rule > "Use a formula to determine which cells to format".
4. Use the following formulas:
   • Top N: =A2>=LARGE($A$2:$C$8,$F$2)
   • Bottom N: =A2
5. Click Format to choose your formatting style.

These formulas use LARGE and SMALL functions to dynamically determine the threshold values. Absolute references ($) lock the range and input cells, while the relative reference (A2) allows the formula to apply correctly to each cell in the range.

Highlighting Rows Based on Column Values:

To highlight entire rows based on top/bottom N values in a specific column (e.g., column B):

• Top N Rows: =$B2>=LARGE($B$2:$B$15,$E$2)
• Bottom N Rows: =$B2

Apply these formulas to the entire table (excluding the header row).

Highlighting Top N Values in Each Row:

For highlighting the top N values within each row across multiple columns, use this formula:

=B2>=LARGE($B2:$G2,3)

Apply this to the entire numeric data range (e.g., B2:G10).

This formula uses relative row references and absolute column references to correctly compare values within each row.

Downloadable Practice Workbook: Highlight top or bottom values in Excel (.xlsx file)

The above is the detailed content of How to highlight top 3, 5, 10 values in Excel. For more information, please follow other related articles on the PHP Chinese website!