Conditional formatting greatest value in each row of multiple columns, that ignores text/empty cells/0 values

heretolearnexcel

Board Regular
Joined
Jan 22, 2019
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm trying to use conditional formatting to highlight the smallest value in each row, but there are some cells with text values that are getting highlighted. I'm currently using this formula:
=D2=MIN($D2:$Z2)

applies to: =$D:$Z

What could be an efficient formula to ignore those cells?

Thanks in advance.

gallina_feliz_comparativo.xlsx
DEFGHIJ
1abcd
2$1,077.58N/DN/DN/D
3$58.00N/D5364
4$70.0060N/D86
5$180.00135150166
6$200.00159175194
7$31.00N/D26.5N/D
8$35.00N/DN/DN/D
9$50.00N/D4454
10$485.00N/DN/DN/D
11$90.00N/DN/DN/D
12$110.00118105119
13$120.00N/DN/DN/D
14$22.00N/DN/DN/D
15$18.50N/D1830
16$34.00N/DN/DN/D
17$48.00N/D4348
18$46.30N/D46N/D
19$69.44N/DN/DN/D
20$55.00N/DN/D56
21$440.00N/D370450
22$325.00N/D105144
23$1,300.00N/DN/DN/D
24$25.00N/DN/DN/D
25$46.58N/DN/DN/D
26$55.56N/DN/DN/D
27$21.30N/DN/DN/D
28$130.00150140163
29$75.00825086
30$95.00N/DN/DN/D
31$90.0085N/DN/D
32$224.14N/DN/DN/D
33$75.00N/DN/DN/D
34$65.00N/DN/DN/D
35$95.00908386
36$145.00N/DN/DN/D
37$90.00N/DN/DN/D
38$95.00N/DN/DN/D
39$24.00N/DN/DN/D
40$30.00N/DN/DN/D
41$157.41135100N/D
42$41.67N/DN/DN/D
43$26.00N/DN/DN/D
44$37.04N/DN/DN/D
45$80.00N/DN/DN/D
46$193.00N/DN/DN/D
47$79.63N/DN/DN/D
48$94.00N/D68N/D
49$38.00N/DN/DN/D
50$42.00N/DN/DN/D
51$38.00N/DN/DN/D
52$125.00N/DN/DN/D
53$310.00N/DN/DN/D
54$160.00N/DN/DN/D
55$160.00150115117
56$130.00N/DN/DN/D
57$155.00N/DN/DN/D
58$128.00N/DN/DN/D
59$28.00N/D28N/D
60$28.00N/D28N/D
61$44.00524455
62$20.25N/D19N/D
63$203.70170150264
64$165.00N/DN/DN/D
65$100.00N/DN/DN/D
66$240.74255N/D211
67$203.70N/DN/D131
68$275.00N/DN/D292
69$90.00N/DN/DN/D
70$105.00N/DN/D109
71$14.00N/DN/DN/D
72$135.00N/D123N/D
73$140.00N/DN/DN/D
74$208.33155N/D233
75$250.00233240225
76$160.00N/DN/DN/D
77$45.00525092
78$100.00N/DN/DN/D
79$27.00N/DN/DN/D
80$88.00829097
81$50.00N/DN/DN/D
82$29.00N/D28N/D
83$52.00N/DN/D55
84$30.00N/DN/D35
85$50.00N/D4762
86$150.00N/DN/DN/D
87$115.00N/DN/DN/D
88$14.00N/DN/DN/D
89$44.00463754
90$400.00N/DN/DN/D
91$148.15155N/DN/D
92
93
94
95
Sheet1
Cell Formulas
RangeFormula
D2:D90D2=XLOOKUP(A2,cat!C:C,cat!J:J,"not found")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D:ZExpression=D2=MIN($D2:$Z2)textNO
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Possibly this:
Excel Formula:
=AND(ISNUMBER(D2),D2 =MIN($D2:$Z2))
 
Upvote 0
Hint for next time: Be more careful with your wording. ;)
Thread title: "Conditional formatting greatest value in each row"
Post #1: "conditional formatting to highlight the smallest value in each row"
 
Upvote 0
Try this instead.

Excel Formula:
=AND(ISNUMBER(D1),D1=MIN($D1:$Z1))
 
Upvote 0
Solution
Because it is the first row in your range "D:Z"
 
Upvote 0
Woah, it works. Thank you. What is the logic behind why it didn't work unless you changed the rows to 1?
Whenever you apply Conditional Formatting to a whole range of cells at once, you write the CF formula as it applies to the FIRST cell in your selection.

Since there is a header row in row 1, if your selected data range started in row 2 (where the actual data starts), then rlv01's original formula would have worked perfectly for you.
But I am guessing that maybe you selected entire columns, which means your FIRST selected cell is in row 1, not row 2.
So then your formula would have to reference row 1 instead of row 2, as shown in his second formula.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top