Making Conditional Formatting ignore hidden rows

[qnguyen]

I have a document with multiple filters that cause multiple rows to be hidden. The visible data looks similar to this:

	Column A
Row 1	15
Row 3	24
Row 4	13
Row 5	32
Row 11	12
Row 13	20

<tbody>
</tbody>

I am trying to create a conditional formatting to highlight the highest 3 numbers in column A. The problem I am having is that sometimes the highest numbers are in hidden rows, and these get highlighted instead. How do I make it so only the top 3 non-hidden numbers are highlighted? In the above example, A3, A5, and A13 should be highlighted even if the hidden rows have larger values.

 

[arthurbr]

Perhaps try the AGGREGATE function

 

[Peter_SSs]

To expand on arthurbr's suggestion a little ..
But first, since you have filters, your sample data can't be quite correct as you would have headings in row 1 with data starting in row 2.

Anyway, try this. With the filter set to 'Select All' ..
1. Select from A2 down to the end of the column A data
2. In Conditional Formatting, use use a formula to determine which cells to format & use this formula, adjusting the A$30 to match your actual range
=A2>=AGGREGATE(14,5,A$2:A$30,3)
3. Set your desired format then try filtering the data.