Apply Conditional Format If Any Cells On Same Row Contain Top 10% Values

As a tech neophyte, I am quite flustered by inability to apply conditional formatting to first cell on each row if any other cell(s) on that same row contain a top 10% value. For instance,

NOW: All cells with top 10% values conditionally formatted by green fill and bold-faced gold text

Excel Sample Table 1 IMAGE.png

DESIRED RESULT: "HOUR" Column cells on same row as any others that contain top 10% values auto format

Excel Sample Table 2 IMAGE.png

OR Entire ROW with any top 10% cell value auto formats as below:

Excel Sample Table 3 IMAGE.png
Thanks in advance for all helpful tips. And I do apologize for the huge pics but can't figure out how to post tables per your published Forum Rules.

Welcome to the forum.

You should have attached the workbook - there are instructions in the yellow banner up top.

However, I can explain what to do.

1. Select the range you wish to apply the formatting to (in my example it's B2:H11).
2. Click on Conditional Formatting > New Rule > Formula.
3. Type this into the formula box: =B2>=LARGE($B$2:$H$11,10).
4. Set the formatting you require.
5. Save (and apply if you wish to see it before saving).

What you get is the top 10 highest scored highlighted (this is not strictly speaking the top 10 percent, however it matches what you seem to want).

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
2	1	85	8	20	25	15	131	15
3	2					68	10	30
4	3				205	40	20	25
5	4	40	43		20	10	16	35
6	5	53	38	61	48	65	10	10
7	6	108	129	75	113	80	50	36
8	7		31	65	82	54	71	40
9	8	52	15	85	80	140	40	10
10	9	20	25	51	46	20	50	20
11	10	60	94	35	10	80	35	10

Sheet: Sheet1

For the times, select the time section (in my example that's A2:A11) and use this rule:

=COUNTIF($B2:$H2,">="&LARGE($B$2:$H$11,10))>0

That's it.

Administrative Note:

Unfortunately, it has come to our attention you have violated Rule #8 of our Forum RULES:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offence could lead to a permanent ban, so do take this caution to heart.

I don't know why you felt the need to prompt me to look again at your thread when I gave you a solution yesterday morning that you have not even acknowledged (see pots #2).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Yep! Works like a charm now! I now realize my first mistake was not removing Excel's built-in CF for Top 10 PERCENT by editing to use your LARGE fx before 2nd new CF rule applied to cells in "Hour" Column. So, I guess Excel couldn't find any cell below 7 Weekday columns to satisfy condition specified by "Hour" Column LARGE CF that looks for numerical v. percentile ranks?

Just thought it best to write in efforts to help fellow Excel neophyte users. I'm pleased to inform you of my new ability to perform a tweak on your CF formula so it will show Top 10 PERCENT value cells versus 10 highest by numerical rank. Just change '10' at end of the string to a '17,' which is 10% of 168 hours in one week! A big drawback I see is huge skew of any partial bird's eye view, though. Won't any fixed value entered for number of cells to highlight create this result? For instance, imagine what might appear on Jan 1, 2020, that brings in a New Year just past the next bend. Any ideas or suggestions are greatly appreciated.

PS: Thanks for a great side bonus that helped me learn to make zero values instantly vanish by CF applied to turn the text white.

If you want further help, please attach a sample workbook. Instructions in the yellow banner at the top of the page.