formatiing top values in each column accross a worksheet

  • Thread starter Thread starter light
  • Start date Start date
L

light

I have several columns in a worksheet and want excel to pick out the to
3 values in each column and format those cells in color . I know how t
do that with the highest value in a column.,but don't know how to tel
excel to also take the next lowest value and third lowest value at th
same time in each colum accross the entire worksheet and format thos
as well
 
Hi
try for column A something like the following in the conditional format
dialog
=OR(A1=MAX($A$1:$A$100),A1=LARGE($A$1:$A$100,2),A1=LARGE($A$1:$A$100,3)
)
 
Select all the cells that you want to format (e.g. A1:F20)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, enter a formula that refers to the
active cell, e.g.: =A1>=LARGE(A$1:A$20,3)
Click the Format button, and choose a colour on the Pattern tab
Click OK, click OK
 
That worked great. Now how can I copy that formula and use it i
different parts of the worksheet .Format painter does not adjust to
new column range when I use it in different places and F4 is worse(I'
kind of new at this)
 
Adapting slightly from Debra's suggestion,
try these steps:

Select col A

Click Format > Conditional Formatting [ CF ]

For Condition 1, put:
Fornula Is | =NOT(ISNUMBER(A1))
Leave it as "No Format Set"

Click Add >>

For Condition 2, put:
Fornula Is | =A1>=LARGE(A:A,3)
Click the Format buton and format to taste > OK

Click OK at the main CF dialog

The top 3 numeric values in col A will be highlighted
as per CF format given. Text will be ignored.

----
To propagate the CF to other cols,
you can use the Format painter
[ the formulae above *will* adjust relatively ]

Select col A

Double-click Format Painter icon
[ cursor will turn into a "brush" ]

Now just click on the other col headers desired
to "paint" the CF on the other cols

Press Esc when done to revert cursor to normal
 
Thanks for the replies. I just want to point out that Frank's formul
did not work and Debra's did. On my 2nd question,Max's suggestion di
not work.

What happens in format painter using Debras formula when I try to cop
it and go elsewhere to apply the formatting ,is that the ranges i
parethesis do not change in another location.This is the problem. Th
range outside the parethesis does change in another location
 
well, what I suggested worked ok for me,
so I'm really not sure what could have happened at your end..

The CF formulas suggested for conditions 1 & 2
in col A [e.g. in A1]:

=NOT(ISNUMBER(A1)

=A1>=LARGE(A:A,3

are both *relative* and will adjust when you copy the CF to other col

For example, if you copy the CF in col A to col B
via the Format painter following the steps given,
the formulas above will show in col B [e.g. in B1] as

=NOT(ISNUMBER(B1)

=B1>=LARGE(B:B,3

with all the col A's references changed to col B'
-
Rgd
Ma
xl 9
-
Please respond, in newsgrou
xdemechanik <at>yahoo<dot>co
--
----- light > wrote: ----

Thanks for the replies. I just want to point out that Frank's formul
did not work and Debra's did. On my 2nd question,Max's suggestion di
not work.

What happens in format painter using Debras formula when I try to cop
it and go elsewhere to apply the formatting ,is that the ranges i
parethesis do not change in another location.This is the problem. Th
range outside the parethesis does change in another location
 
The rows in my formula are absolute references (A$1:A$20)
If you use the format painter, the column reference should change, but
the rows would remain the same. So, if you copy the format to column G,
the formula would change to: =G1>=LARGE(G$1:G$20,3)
 
well, what I suggested worked ok for me,
so I'm really not sure what could have happened at your
end...

The CF formulas suggested for conditions 1 & 2
in col A [e.g. in A1]:

=NOT(ISNUMBER(A1))

=A1>=LARGE(A:A,3)

are both *relative* and will adjust when you copy the CF
to other cols

For example, if you copy the CF in col A to col B
via the Format painter following the steps given,
the formulas above will show in col B [e.g. in B1] as:

=NOT(ISNUMBER(B1))

=B1>=LARGE(B:B,3)

with all the col A's references changed to col B's
 
Max

I'm a real beginner,so maybe I'm doing something wrong cause i
still doesn't work for me, I'll tell you what I have so maybe you ca
walk me thru this:

The data I want to copy the formatting to are in little "packets" i
the worksheet surrounded by headings on top and to the left.

The first data that the formula applies to is in C6.It goes down t
C16.The other columns that need to be dealt with simultaneously ar
D6:D16,E6:E16,F6:F16.Debras formula worked great after highlightin
C6:F16 . It gave me the 3 highest values in columns C,D.E and F.

Now my next data"packet" is in range C21 to F27.Again surrounded b
headings on the top and left.There are many "packets" in the workshee
with a different number of rows from eachother.The number of column
remain the same throughout the worksheet.But the headings reappear i
each new "packet" in the column section and row section. Like Debra
says the absolute values wont change over with her formula.

So can you start me from scratch and tell me what specifically t
highlight with the ranges and the specific cell addresses to put i
the formulas now that I have told you exactly what I have
 
So can you start me from scratch and tell me what specifically to
highlight with the ranges and the specific cell addresses to put in
the formulas now that I have told you exactly what I have.
Click to expand...

The specifics would have helped if they had been given upfront.

My suggestion presumed your layout would be amenable to
"entire column" processes. Wrong guess.

ok, because of the specific layout
- different number of rows per block that needs the CF
I'd think you would need to repeat the CF set-up per block

For example, for the C21:F27 block

Select C21:F27 and set-up the CF via
Formula Is | =C21>=LARGE(C$21:C$27,3)

And repeat for all the other blocks in the same columns C to F
adjusting to suit the exact rows within each block

But after you've completed the above, if the structure in cols C to F
is *identical* to that elsewhere, say the structure in cols H to K,
then you could just select say the entire col C, and copy the CF
via Format Painter to the other columns
 
Thanks for trying Max,but I do between 40-60 new packets a weekend an
it would be quite burdumesome to
adjust the formula for that many blocks on a regular basis
 
You're welcome, Light.
Thanks for the feedback.

Perhaps others might step in
with better alternatives for you.

Good luck!
 
Back
Top