Excel: Giving Me Only the Information I Want, aka, Filtering

I will be devoting three days to this topic.  Sorting and filtering information is one of the most efficient uses of Excel, especially for those of us who are not experts at database programs such as MS Access.

When you have a large list of items on your spreadsheet, sometimes you only want to look at certain items, say dogs with curly hair. Let’s make a spreadsheet of dog breeds and traits; you may copy and paste the list below into Excel. (I own a type of Belgian shepherd called a Belgian Tervuren whose story you can see on her website at sahzidog.blogspot.com.)

Once we have the table set up, we can either add filters to the headings or format it as a table. Today, we will simply add filters.

Highlight the row with the headings by clicking on the number next to the row.  In our example, it’s Row 1.  Then click on the Sort & Filter icon in the Editing section of the Home Ribbon.  Click on Filter in the drop down menu.  The Filter icon is a funnel or strainer because you are going to be straining out the items you want.

Now a small upside-down triangle will appear in the corner of every cell in the headings. This of this as a tiny version of the funnel to remind you that you can strain out your results.

To look at just the short hair dogs, click on the triangle in the “Short” column. When the drop-down filter menu appears, click on the check next to “(Blanks)”.  This will deselect or hid any cells below that are blank.  In this case, only the cells with an “x” will remain visible.  If the cells had x’s and o’s, you could click on “(Select All)” to clear everything, then just click on the one you want and click OK.  Now the down triangle will have a funnel next to it to show you that your are filtering that particular column.

Now the down triangle will have a funnel next to it to show you that your are filtering that particular column.

To get all your dogs back, click the filter, and check “Select All” and OK.