Cutting a herd is only half the job. (As we covered in our last post, "cutting" a herd means separating out the matching cattle — not cutting and pasting them.) Once the matching cattle are cut from the larger group, a good trail boss doesn't leave them scattered — he lines them up so they're easy to count, easy to manage, and easy to move. Excel works the same way. In our last post, we introduced FILTER, a spill function that cuts out every row matching your criteria — but on its own, that group can still come back in no particular order. Put the FILTER function inside a SORT function, and Excel will line up your results the moment they spill.
Cut the Herd, Then Line It Up in One Step
- Click the cell where you want the results to spill. As always, make sure there's plenty of empty space around it.
- Type =SORT(FILTER(A2:C50, B2:B50>1000), 2, -1). Here, FILTER pulls every row where column B is greater than 1,000, and SORT arranges those results by the second column (in this example, sales amount), largest to smallest.
- Press Enter.
Outcome: Instead of a scattered list you'd have to sort by hand, you get a single formula that cuts out the matching group and lines them up by rank — your top performers, front and center, updated automatically any time your data changes.
Surrounding one function with another like this is what separates a basic Excel user from a power user: instead of running one formula, checking the result, then running a second formula on top of it, you build the whole process into a single, self-updating formula.
Bonus Trail Tip: FILTER + UNIQUE
Sometimes cutting the herd turns up a few animals wearing the same brand — in spreadsheet terms, duplicate entries. If you want a filtered list with no repeats, put the FILTER function inside a UNIQUE function instead of SORT:
- Type =UNIQUE(FILTER(A2:C50, B2:B50>1000)) to pull every matching row, then strip out any exact duplicates.
Outcome: You get a clean, filtered list with no repeat entries — useful any time your filtered results might include the same customer, product, or record more than once.
Want the best of both worlds? You can put the FILTER function inside a UNIQUE function, and put that whole thing inside a SORT function — =SORT(UNIQUE(FILTER(A2:C50, B2:B50>1000)), 2, -1) — to cut the herd, remove duplicates, and line everything up in rank order, all in a single formula.
Curious how formulas like this — with one function surrounding another — could simplify your own reports? Reach out to us and we'd be happy to brainstorm ideas with you.
