conditional formatting

sgth · Aug 17, 2012

Well i've been trying to figure this one out for about a day and a half now, but just cant seem to get it to work at all. I want to add conditional formatting for the day that has the highest attendance, but everytime i try it, it either adds the formatting to all the days or none.

this is what i've been trying =Max($C$7:$AG$18) then the formatting, applies too =$C6$:$AG$6

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
6	Month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
7	January	5,580	5,646	4,989	4,601	4,746	4,213	4,046	4,150	4,235	4,160	4,185	4,235	4,122	4,154	4,416	4,380	4,249	4,234	4,276	4,219	4,441	4,200	4,249	4,093	4,227	4,040	4,204	4,060	3,876	3,781	3,897
8	February	4,207	4,482	4,454	4,321	4,423	4,480	4,489	4,234	4,267	4,096	4,186	4,369	4,341	4,137	4,068	3,925	4,102	4,130	4,097	4,072	4,198	4,108	3,996	3,940	3,975	4,029	3,861	3,936
9	March	4,586	4,586	4,350	4,384	4,518	4,296	4,362	4,426	4,524	4,350	4,069	4,162	4,221	4,320	4,367	4,371	4,392	3,960	4,485	4,472	4,429	4,144	4,025	3,892	4,138	4,176	4,042	4,126	4,006	4,139	3,893
10	April	4,483	4,570	4,539	4,534	4,599	4,497	4,541	4,228	4,239	4,340	4,324	4,232	4,129	4,038	4,536	4,700	4,660	4,621	4,620	4,392	4,404	4,806	4,936	4,876	4,771	4,920	4,870	4,697	4,932	5,067
11	May	5,958	5,851	5,962	5,964	6,016	5,690	5,703	6,354	6,593	6,451	6,720	6,444	6,375	6,480	7,127	7,089	6,839	7,001	7,079	7,129	6,869	7,180	7,287	7,180	7,218	7,087	7,293	9,109	8,534	7,572	7,518
12	June	8,663	8,828	8,554	8,780	8,516	8,685	8,524	8,837	9,155	9,003	9,040	9,262	9,089	9,053	9,526	9,803	9,744	9,594	9,767	9,860	9,767	10,075	9,941	10,193	10,071	10,129	10,197	9,947	11,306	11,289
13	July	12,392	14,106	14,764	15,254	14,524	13,682	12,530	13,136	12,704	12,770	13,111	13,122	12,661	12,616	13,260	13,300	13,701	13,467	13,687	13,570	13,668	13,884	14,360	14,257	14,228	14,353	14,011	13,831	14,260	14,203	14,155
14	August	13,372	13,827	13,324	13,466	13,468	13,560	13,810	12,884	13,123	12,674	12,931	12,764	12,856	12,843	12,425	12,626	12,499	12,691	12,519	12,426	12,596	11,375	11,529	11,049	11,283	11,213	11,118	11,420	10,342	10,373	10,437
15	September	9,067	9,223	9,871	10,414	10,676	8,143	8,845	8,526	8,877	8,524	8,716	8,502	8,661	8,446	7,683	7,823	7,958	7,629	7,524	7,768	7,682	7,568	7,920	7,723	7,593	7,612	7,612	7,551	7,361	7,525
16	October	6,213	6,315	6,408	6,375	6,316	6,201	6,513	5,893	6,011	6,048	6,067	5,913	6,013	5,790	5,720	5,762	5,848	5,685	5,701	5,481	5,561	5,616	5,418	5,509	5,364	5,553	5,474	5,486	5,428	5,384	5,267
17	November	4,747	4,651	4,637	4,747	4,669	4,676	4,473	4,207	4,047	4,051	4,152	4,267	4,144	4,104	4,200	4,088	4,000	4,191	5,268	5,421	5,452	5,221	5,396	5,208	5,350	5,457	5,449	5,181	5,176	4,812
18	December	4,725	4,568	4,676	4,678	4,998	5,214	5,048	5,087	5,238	5,565	5,404	5,582	5,366	5,546	5,453	5,486	5,808	5,823	5,657	5,743	6,299	6,289	6,074	6,219	6,224	6,440	6,349	6,566	6,441	6,612	6,625

2013 Attendance

<o

></o

>

brendanr · Aug 17, 2012

You don't need any formulas for this one. Use the built in Conditional Formatting tool.

Highlight one row. Click Conditional Formatting > Top/Bottom Rules > Top 10 Items...

Change it from Top 10 to Top 1, and set your output formatting. Repeat for all rows.

Voila.

Vidar · Aug 17, 2012

Hi

Select the whole range C7:AG18, starting from C7, so C7 is the first cell in the selection.
In conditional formatting paste this formula and set the formatting to what you want.
=AND(C7=MAX($C$7:$AG$18),COLUMNS($C7:C7)=MIN(IF($C$7:$AG$18=MAX($C$7:$AG$18),COLUMN($C$7:$AG$18)-COLUMN($C$7)+1)))
Your formatting will aplly to the cell with the highest value (F13).

Vidar

T. Valko · Aug 17, 2012

sgth said:
Well i've been trying to figure this one out for about a day and a half now, but just cant seem to get it to work at all. I want to add conditional formatting for the day that has the highest attendance, but everytime i try it, it either adds the formatting to all the days or none.

this is what i've been trying =Max($C$7:$AG$18) then the formatting, applies too =$C6$:$AG$6

B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG

6
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

7
January
5,580
5,646
4,989
4,601
4,746
4,213
4,046
4,150
4,235
4,160
4,185
4,235
4,122
4,154
4,416
4,380
4,249
4,234
4,276
4,219
4,441
4,200
4,249
4,093
4,227
4,040
4,204
4,060
3,876
3,781
3,897

8
February
4,207
4,482
4,454
4,321
4,423
4,480
4,489
4,234
4,267
4,096
4,186
4,369
4,341
4,137
4,068
3,925
4,102
4,130
4,097
4,072
4,198
4,108
3,996
3,940
3,975
4,029
3,861
3,936

9
March
4,586
4,586
4,350
4,384
4,518
4,296
4,362
4,426
4,524
4,350
4,069
4,162
4,221
4,320
4,367
4,371
4,392
3,960
4,485
4,472
4,429
4,144
4,025
3,892
4,138
4,176
4,042
4,126
4,006
4,139
3,893

10
April
4,483
4,570
4,539
4,534
4,599
4,497
4,541
4,228
4,239
4,340
4,324
4,232
4,129
4,038
4,536
4,700
4,660
4,621
4,620
4,392
4,404
4,806
4,936
4,876
4,771
4,920
4,870
4,697
4,932
5,067

11
May
5,958
5,851
5,962
5,964
6,016
5,690
5,703
6,354
6,593
6,451
6,720
6,444
6,375
6,480
7,127
7,089
6,839
7,001
7,079
7,129
6,869
7,180
7,287
7,180
7,218
7,087
7,293
9,109
8,534
7,572
7,518

12
June
8,663
8,828
8,554
8,780
8,516
8,685
8,524
8,837
9,155
9,003
9,040
9,262
9,089
9,053
9,526
9,803
9,744
9,594
9,767
9,860
9,767
10,075
9,941
10,193
10,071
10,129
10,197
9,947
11,306
11,289

13
July
12,392
14,106
14,764
15,254
14,524
13,682
12,530
13,136
12,704
12,770
13,111
13,122
12,661
12,616
13,260
13,300
13,701
13,467
13,687
13,570
13,668
13,884
14,360
14,257
14,228
14,353
14,011
13,831
14,260
14,203
14,155

14
August
13,372
13,827
13,324
13,466
13,468
13,560
13,810
12,884
13,123
12,674
12,931
12,764
12,856
12,843
12,425
12,626
12,499
12,691
12,519
12,426
12,596
11,375
11,529
11,049
11,283
11,213
11,118
11,420
10,342
10,373
10,437

15
September
9,067
9,223
9,871
10,414
10,676
8,143
8,845
8,526
8,877
8,524
8,716
8,502
8,661
8,446
7,683
7,823
7,958
7,629
7,524
7,768
7,682
7,568
7,920
7,723
7,593
7,612
7,612
7,551
7,361
7,525

16
October
6,213
6,315
6,408
6,375
6,316
6,201
6,513
5,893
6,011
6,048
6,067
5,913
6,013
5,790
5,720
5,762
5,848
5,685
5,701
5,481
5,561
5,616
5,418
5,509
5,364
5,553
5,474
5,486
5,428
5,384
5,267

17
November
4,747
4,651
4,637
4,747
4,669
4,676
4,473
4,207
4,047
4,051
4,152
4,267
4,144
4,104
4,200
4,088
4,000
4,191
5,268
5,421
5,452
5,221
5,396
5,208
5,350
5,457
5,449
5,181
5,176
4,812

18
December
4,725
4,568
4,676
4,678
4,998
5,214
5,048
5,087
5,238
5,565
5,404
5,582
5,366
5,546
5,453
5,486
5,808
5,823
5,657
5,743
6,299
6,289
6,074
6,219
6,224
6,440
6,349
6,566
6,441
6,612
6,625

<TBODY>
</TBODY>
2013 Attendance

<o></o>

Use this formula:

=C7=Max($C$7:$AG$18)

What version of Excel are you using?

sgth · Aug 17, 2012

RI'm using excel 2010 and I'm wanting to add the formatting to the day that has the highest value that been said yes it is f13 but the day on the top has to be the formatted cell (f4)

T. Valko · Aug 17, 2012

sgth said:
RI'm using excel 2010 and I'm wanting to add the formatting to the day that has the highest value that been said yes it is f13 but the day on the top has to be the formatted cell (f4)

OK, now I understand.

Use the "Use a formula..." option.

Use this formula:

=COLUMN()=MAX(($C$7:$AG$18=MAX($C$7:$AG$18))*COLUMN($C$7:$AG$18))

sgth · Aug 17, 2012

Yeah sorry if it didn't make since when i first posted and I will try that when I get a chance I'm on my iPad right now and will try it and let you know how it went

sgth · Aug 17, 2012

your formula did work but it adds the formatting to the cell ran f6:f18

T. Valko · Aug 17, 2012

sgth said:
your formula did work but it adds the formatting to the cell ran f6:f18

Here are the steps to take...

Select the *entire* range C6:AG6 starting from cell C6.
Cell C6 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.
<br />
Goto the Home tab-Styles-Conditional Formatting-Manage rules-
New rule-Use a formula to determine which cells to format
<br />
Enter this formula in the box below:
<br />
=COLUMN()=MAX(($C$7:$AG$18=MAX($C$7:$AG$18))*COLUMN($C$7:$AG$18))
<br />
Click the Format button
Select the desired style(s)
OK out

sgth · Aug 17, 2012

thanks i will do this

conditional formatting

sgth

New Member

Excel Facts

brendanr

New Member

Vidar

Well-known Member

T. Valko

Well-known Member

sgth

New Member

T. Valko

Well-known Member

sgth

New Member

sgth

New Member

T. Valko

Well-known Member

sgth

New Member

Forum statistics

Share this page

conditional formatting

New Member

Excel Facts

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock