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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A [/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

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.