Forum Discussion
Conditional Formatting for multiple range
I wonder can we make conditional formatting in excel to hightlight certain values in multiple ranges in a couple of certain columns?
| A | B | C | D | E | F | G |
| 1 | value | Percentages of Total | value | Percentages of Total | value | Percentages of Total |
| 109 | 61 | 122 | 61 | 145 | 61 | |
| 50 | 28 | 54 | 27 | 67 | 28 | |
| 19 | 10 | 21 | 10 | 25 | 10 | |
| 178 | 100 | 197 | 100 | 237 | 100 | |
| 2 | Values | Percentage of Total | Values | Percentage of Total | Values | Percentage of Total |
| 1 | 0 | 1 | 0 | 2 | 1 | |
| 0 | 0 | 0 | 0 | 1 | 0 | |
| 1 | 0 | 1 | 0 | 2 | 1 | |
| 4 | 3 | 4 | 2 | 6 | 3 | |
| 11 | 8 | 14 | 9 | 15 | 8 | |
| 17 | 13 | 18 | 12 | 23 | 13 | |
| 17 | 13 | 21 | 14 | 27 | 15 | |
| 20 | 15 | 22 | 15 | 27 | 15 | |
| 31 | 23 | 32 | 22 | 36 | 20 | |
| 28 | 21 | 31 | 21 | 34 | 19 | |
| 130 | 100 | 144 | 100 | 173 | 100 | |
| 3 | Values | Percentage of Total | Values | Percentage of Total | Values | Percentage of Total |
| 76 | 45 | 85 | 46 | 102 | 46 | |
| 4 | 2 | 4 | 2 | 4 | 1 | |
| 29 | 17 | 32 | 17 | 37 | 16 | |
| 7 | 4 | 7 | 3,83 | 8 | 3 | |
| 20 | 11 | 21 | 11 | 26 | 11 | |
| 6 | 3 | 6 | 3 | 6 | 2 | |
| 12 | 7 | 14 | 7 | 17 | 7 | |
| 3 | 1 | 3 | 1 | 3 | 1 | |
| 7 | 4 | 7 | 3 | 8 | 3 | |
| 1 | 0 | 1 | 0 | 5 | 2 | |
| 3 | 1 | 3 | 1 | 3 | 1 | |
| 0 | 0 | 0 | 0 | 0 | 0 | |
| 0 | 0 | 0 | 0 | 0 | 0 | |
| 168 | 100 | 183 | 100 | 219 | 100 |
so for example all 3 tables in a worksheet, all tables had different row lengths and i want to hightlight automatically 3 largest percentages values in range between "Percentage of Total" and "100" which is the sum of percentages in each tables.so for Table 1 all of cells between "Percentage of Total" and "100" will be hightlight,for table 2 highlight values are 23 21 15 (1st column), 22 21 15 (2nd column), 20 19 15 (3rd column)and for table 3 it would be 45 17 11 (1st column), 46 17 11 (2nd column) and 46 16 11 (3rd column)
Is there a way to do that?
5 Replies
As your "tables" are not consistent in size, the conditional formatting formula must dynamically determine which cells to include in the comparisons. It's a little more complicated than what you tried. See the attached workbook.
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.
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
