Ranking within a Filtered Worksheet

  • Thread starter Thread starter Ralph Noble
  • Start date Start date
R

Ralph Noble

Anyone know if you can rank filtered data in Excel? I want to filter
out some rows and rank the rest but the hidden data seems to be
screwing up the ranking. Essentially, the worksheet has three sets of
data that I would like to rank separately.

Thanks,

Ralph Noble
 
You can add columns to your table, to calculate the rank.

For example, to rank an item in column G, with data starting in row 2:
--In cell H1, add the heading RankAmt1
--In cell H2, enter the following formula: =SUBTOTAL(9,G2)
--In cell I1, add the heading Rank1
--In cell I2, enter the following formula: =RANK(H2,$H$2:$H$400)
where 400 is the last row of data
--Copy the formulas down to the last row of data

Create similar columns for other columns to be ranked
 
Back
Top