Monday, July 9, 2012

Excel and you: Filtering, or getting the junk out

Long time, no Excel tips, but I’m back with another fun function that you can use to quicken your day!
So, sorting.  It’s a mainstay of Excel use, and pretty awesome at that.  But there are some things it just can’t do.  You can put things in ascending order, descending order…and that’s about it.  But what happens when you want to view only a certain chunk of the info?  You could hide the individual rows you don’t want to see, but that is so time-consuming, especially if you don’t want to hide chunks on top or bottom, but randoms in the middle.

You could copy the data you want to use to a different section for printing purposes, but that would be an even bigger pain the the butt.

What you need is a way to differentiate your data, a way to tell Excel which pieces you want to see and which not to, you need a way to filter things…


And Excel comes through again!

The filter button is one of my all-time favorites.  I use it all the time at work in most of my spreadsheets.

One caveat before we begin:  your spreadsheets will need a header row for filter to work properly.

Say you have a spreadsheet of dates, but you only want to see the ones in a specific month.
All you need to do is click the filter button in the data tab.

Everybody's happy now!
You will now have happy triangles on each of the headers that will allow you to filter and sort to your hearts content.  Yes, you no longer have to sort by the relatively clunky sort button in the data tab, unless you want to sort by more than one variable.  It’s super nice.

So, here we have a filter tab!

As you can see, there are many options for filtering.  You can choose any number of dates, from a year, to a month, to a day , and have only that chosen range show.  You also have the option to sort and filter by color, if you’ve color-coded your table at all.

Once you have filtered a column, the happy triangle turns into a mini-filter, so you know which columns are filtered.  You can filter more than one column at a time, which can get a little wonky, especially if you have filtered out data you know is there by filtering data in another column.

If you want to remove the filter from the data, just chose the clear filter button, which would be activated if you actually had data filtered.

And finally, if you want to clear the filtering option from your spreadsheet altogether, just click the big filter button a second time, and your happy triangles will vanish.  You may need to do this on occasion even if you don’t want to remove the filter permanently, because sometime excel doesn’t play nice when you add new data to a currently filtered table.
Are there any items in Excel that just have you flummoxed?  Do you have a conundrum that you know can be solved, but have no idea where to even start?  Leave me a comment, and I may be able to find a solution for you in my next column!

No comments:

Post a Comment