Forum Discussion
Conditional Formatting for multiple range
How about this:
1. Select the First Table's Percentage Columns (e.g., B2:B10, D2:D10, F2:F10).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose `"Use a formula to determine which cells to format"`.
4. Use below formula
=B2>=LARGE(B$2:B$10,3)
- Replace B$2:B$10 with the appropriate range in each table.
- Change column letters (B, D, F) accordingly for other percentage columns.
- The LARGE() function retrieves the top 3 values in the specified range.
5. Click Format, choose a highlighting style (like a bold background color), and OK.
6. Repeat the process for Table 2 and Table 3, adjusting the formula ranges.
Thank you for your reply, yes that can be done, but what i actually want is a single rule that could be applied once and it will automatically calculate for all future tables, as such conditional formatting manager would not be full with same rule being apply over and over again.
I tried using INDEX and MATCH function to force Range in LARGE function (=LARGE(INDEX(C3;MATCH("Percentage of Total";C3;0)):INDEX(C3;MATCH(100%;C3;0));2)) unfortunately it only recognised the first table.
Thank you for ur reply, yes that can be do, but what i want is a single formula that can be apply automatically for all future table without we don`t need to apply the formula for each table over and over again, so conditional formatting rules manager with not full with same rule.
is there a way to do that? i tried using INDEX and MATCH function to force Range in LARGE rule(=LARGE(INDEX(C3;MATCH("Percentage of Total";C3;0)):INDEX(C3;MATCH(100%;C3;0));2)), but it only recognised the first table
