Excel 2007 : Conditional Formatting with Subtotals

[mttlltt75]

Hello, I have a spreadsheet that has subtotals and I would like to have a conditional format that would highlight the top 10/bottom 10 without including the actual subtotal rows in thie conditional format. Is this possible?

[DonkeyOte]

that should be possible but it would help if you could provide a sample file such that we can determine the layout.

also what happens if there are multiples of the last number making up top / bottom n ?

[NBVC]

Which column contains the numbers to highlight? And how does one identify the Subtotals row, so that it can be excluded?

[mttlltt75]

I have added an attachment that shows what I am trying to explain. The column that the numbers are in that I would like to be highlighted are in E. The subtotals are in grey

[DonkeyOte]

You didn't answer the question in regard to what happens if there is more than 1 instance of the 10th largest/smallest value... ie do all instances of that value get formatted ?

First, if we assume you're running a version of XL pre XL2007 you're limited to 3 conditional formats.

Presently you have 2 setup:

1. To format the subtotal/grandtotal rows

2. To set 0 values to white font.

Obviously in order to add the Top & Bottom 10 conditions you must delete one of the existing rules.

You could delete your 2nd (0) rule given you can replicate the same (with exception of 0's in SubTotal rows) using a standard Custom Format, eg:

Highlight your number range (C3:E63) and apply a Custom Format (via CTRL + 1) of: 0.000;-0.000;;

If you do that you then have space for the two new conditional formats....

With the range E3:E63 highlighted apply rules:

Please Login or Register  to view this content.

in the above scenario should multiple instances of the 10th largest/smallest value appear in the column they will be highlighted accordingly.

EDIT: of course 0's would generally form part of the bottom 10 results...