The simplest answer is generally, “Whichever you want.” I personally use filtering so rarely that it was only in the process of writing out these posts that I realized “Hey, filtering is kind of like a simpler sorting for some purposes — I should explain that, too!”
However, there are things that one or the other is better for, and sometimes you might want to combine them.
Sorting is good when:
- You want things in order. For example, when doing my budget spreadsheet I pre-enter scheduled expenses (rent, utilities, etc.) for each month. I then enter unscheduled expenses (groceries, gas, etc.) as they come. This way it’s easy to see how much I actually have left for the month. However, being a tidy person, I also like things that have an order (like days) to be in order.
- There are a great many different, non-duplicated values. For example, if you were working with a list of books, and you’re interested in the titles, then you’re not likely to have a whole lot of duplicates. Finding the one(s) you want to filter by would essentially be the same as finding them manually in the original list. What would be the point?
- You want to manipulate the results in some way. Say for example you’ll want to make a copy of the shortened list, to take with you shopping. Since many programs filter by hiding rows, if you copy the list and paste it in a new sheet, all the hidden rows will re-appear and you’lll be back where you started, with an un-filtered list.
Filtering is good when:
- You want to see a shorter list, with only some of the entries showing.
- You don’t want to disrupt the existing order, but you do want two or more lines that are related, but separated, to appear next to each other.
- You have a complicated set of rules to limit by, for example: Suppose you’re trying to get a list of all speculative fiction books by British authors for some reason. Filtering allows you to limit the author column to only authors you know are British (Neil Gaiman, Terry Pratchet, etc.), and simultaneously limit the genre column to sub-generes of specultive fiction (science fiction, fantasy, etc.). The result would be a quick and easy list that would exclude speculative fiction by non-British authors, and exclude books by British authors that aren’t speculative fiction.