How can I make filtering rows on an Excel spreadsheet easy?

Question

I have an Excel 2010 spreadsheet that looks like this:

Department      Location     Staff
Accounting      SF           10
IT              NYC          20
Finance         NYC          15
Design          SF           20
Engineering     SF           30

(except a lot longer and wider)

I want to make it easy for others viewing this spreadsheet to apply or clear a fixed filter and see only the rows they're interested in. E.g., click to see departments in SF, or click to see departments larger than 15 staff, and see only the rows below that meet those criteria.

Is there any way to do this?

Thanks.

Answer 1

Will AutoFilter do what you need? It basically makes every column header into a dropdown listing all the unique values in that column. Select a value to filter by it, or there's a "Custom" option for greater than/less than filters.

(What I don't know is where they've hidden this in 2007/2010. In older versions, go to Data > Filter > AutoFilter.)

Edit: in Excel 2007, click on Data, then the big funnel. The actual dropdowns have become... annoying, but they can be made to work with a few extra clicks. I assume 2010 is similar.

Answer 2

you could copy & paste (with transpose option) your data to a new spreadsheat. than rows become columns and you can use the usual filter for columns..