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?
1 Reply
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.
