Highlight Top Three Values in Multiple Excel Ranges with Conditional Formatting
Question details
Highlighting the three largest percentage values within several individual tables of varying sizes.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has multiple tables with different row counts and needs a way to automatically highlight the top three percentage values inside each specific table up to 100%.
- Observed behavior
- Standard Top/Bottom rules evaluate all selected cells together, so a custom rule is required to evaluate each table's range individually.
Identify the exact cell ranges for each of your tables and ensure the data is formatted as percentages or numbers before applying the conditional formatting rules.
Use Conditional Formatting with the LARGE Function
Create a custom formula rule using the LARGE function to independently find and highlight the top three values in each specific table range.
When working with multiple tables of varying lengths, a single standard 'Top 10' rule cannot isolate the top values per table. By utilizing a custom conditional formatting formula based on the LARGE function, you can dynamically calculate the top three threshold for each separate data set.
Click and drag to highlight the cells containing the percentage data in your first table (for example, cells A2 through A15).
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.
Choose 'Use a formula to determine which cells to format'. In the formula box, type =A2>=LARGE($A$2:$A$15, 3).
Click the Format button, go to the Fill tab to pick a distinctive color (like light green), and click OK to apply the rule.
Because the tables have different row counts, repeat these steps for each table, making sure to adjust the formula references (e.g., =C2>=LARGE($C$2:$C$20, 3)) for each specific range.
Highlight Top Values Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced conditional formatting and logical functions like LARGE, making it incredibly easy to manage, visualize, and analyze data across complex multi-table layouts.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your tables.
- 2. Select the table data: Highlight the specific range in your first table that contains the percentage values you want to evaluate.
- 3. Apply Conditional Formatting: Go to Home > Conditional Formatting > New Rule and select 'Use a formula to determine which cells to format'.
- 4. Input the LARGE formula: Type the formula =A2>=LARGE($A$2:$A$15, 3), set your preferred fill color in the Format menu, and click OK.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong cells?
This generally happens if your cell references are incorrect. Ensure the first reference in your formula is relative (e.g., A2 without dollar signs) and the range inside the LARGE function is absolute (e.g., $A$2:$A$15 with dollar signs).
Can I highlight the bottom three values using the same method?
Yes, you can easily highlight the bottom three values by replacing the LARGE function with the SMALL function in your formula, and flipping the operator: =A2<=SMALL($A$2:$A$15, 3).
How do I apply this rule to multiple tables at once?
Because each table has a different row count and varying ranges, you cannot reliably select them all at once. You must create a separate conditional formatting rule for each table, adjusting the absolute range inside the LARGE function to match each specific table's dimensions.
What happens if there are duplicate values in the top three?
If the third and fourth largest values in your range are identical, the formula >=LARGE(range, 3) will evaluate as true for both, meaning it may highlight more than three cells to account for the duplicate values.
