Software

We need a better solution: How would you extract the top n records in Excel?

Excel's strange advanced filter behavior has me scratching my head. Have you got a better solution?

Excel's advanced filtering feature is a great tool for extracting just the data you want to see, but it needs a bit of coaxing. Knowing just the right formula is often the key to working with this feature. For instance, how would you return the top n records in a sheet? My solution is to use a formula criterion with the advanced filter. First, you need a criteria range. It can be as simple as two cells, but in this example, the criteria range A1:F4 already exists, so lets see what happens! (This data is from Northwind, the example database that comes with Access.) Now, let's suppose I want to extract the five highest priced items in column C, Unit Price. In that case, the criteria range is C1:C2.

z

In cell C2, enter the following formula:

=C6>=LARGE($C$6:$C$82,5)

This formula will return true or false. It compares the current cell in the list (column C) to the results of the LARGE() function. When applying this technique to your own workbook, be sure that the C6 reference is relative and the range argument is absolute. The C6 reference refers to the first cell in the column you're filtering and the $C$6:$C$82 reference identifies the entire range of values in the same column. Replace 5 in the LARGE() function to change the n factor. For instance, if you want to see the top 3 values, use 3; to see the top 10 values, use 10, and so on.

Now you're ready to run the filter to see the results, as follows:

  1. Select cell C6 (or any cell in C6:C82).
  2. Then, from the Data menu, choose Filter and then Advanced Filter. In Excel 2007 and 2010, click the Data tab. Then, click Advanced in the Sort & Filter group.
  3. In the Action section, specify whether you want to filter in place or copy to another location. For this example, we'll filter in place.
  4. The list range should be correct (if you selected a cell in the list before executing the feature).
  5. Enter $C$1:$C$2 for the criteria range.
  6. Click OK.

Oh… wait… it didn't work. Instead of extracting the five records you expected, it hid them all! Now, here's the trick that will make it work, but I don't know why. Delete the heading text in C1 and repeat steps 1 through 6. Now, it works. Frankly, this puzzles me as it violates the rule that the criteria and list header ranges must match!

The above fix is quick and easy, but if you don't know about it, you're just stuck. You could play around with different ranges and formulas all day and still not be successful! You simply can't present this type of solution to most users—and not end up fielding calls on it later and by then, you might not remember the trick either!

We need a better solution… A better solution would leave the criteria range headings intact and still work! Is this possible? I'd like to hear your techniques. Remember, you don't want to force users to remember that in this particular case… they have to do something special like delete header text! Working with the list and criteria ranges, already in place, how would you get the desired results? Or, if that's not possible, come up with a completely different solution—just remember that I'm looking for a solution that users can remember and implement.

The first one to come up with a reasonable working solution, given the guidelines above, will win something cool from the techrepublic.com stash of cool PR stuff, like a mug or a mouse pad! Impress your colleagues—See what TR sent me for being so smart????

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks