Excel – How to make filtering rows on an Excel spreadsheet easy

microsoft excelmicrosoft-excel-2010

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.

Best Answer

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.

Related Solutions

Excel – Easy way to put multiple columns in Excel on their own rows

Excel has a built in Transpose function that will switch row and columns but that won't work for your scenario. The only that I think will work is a VBA function. My VB is a little rusty but this one should work. You will need to modify the "A1:E5" range to cover the cells you want transform. And this copies the values from the first worksheet named "Sheet1" to the second named "Sheet2". If you have data in Sheet2 or have different named worksheets you will need to modify the code.

Sub Transform()

Dim rowStr As String
Dim rowIndex As Integer

rowIndex = 1

For Each Cell In Sheet1.Range("A1:E5")
    If Cell.Column = 1 Then
        rowStr = Cell.Value
    ElseIf Not IsEmpty(Cell.Value) Then
        Sheet2.Cells(rowIndex, 1) = rowStr
        Sheet2.Cells(rowIndex, 2) = Cell.Value
        rowIndex = rowIndex + 1
    End If
Next Cell

End Sub

Excel – rows compressed/squashed after filtering

I think it is almost certainly because you still have a filter applied on one or more of the other columns.

Make sure all filters are unchecked and it should return to normal

Best Answer

Related Solutions

Excel – Easy way to put multiple columns in Excel on their own rows

Excel – rows compressed/squashed after filtering

Related Question