Dealing with data: Sort and Filter


alternativesHave you ever been overwhelmed with data? How can you organize it? How can you make sense of it all? I’ve tried a lot of databases, and I keep coming back to Excel. The main reason is that databases are designed to give you fixed reports, and the whole thing is structured to give you those reports. Well, when I have a lot of data, I’ll look at it from multiple views, each of which will trigger other questions. I can’t predict what I’ll want to look for next month or the month after that. Excel allows you to look into the data dynamically. The two major techniques for doing this are Sort/Filter and Pivot Tables. Let’s explore Sort and Filter. I’ll show you how to use Pivot Tables next time.

However you plan to look into the data, the first thing you need to do is organize your data into rows and columns. The rows represent the individual items and the columns are the categories and amounts. For example, for tracking your spending, each row will represent a payment and the columns might be the date, amount, payee, and any categories you might be interested in.

spendingIt also works best if your rows represent things at the lowest, most detailed level. For example, if you are tracking the costs, diagnoses, and insurance payments of your doctor visits, each row will represent one visit to one doctor. If you visit the same doctor twice, that’s two different rows. If you see two different doctors on the same day, that’s two rows. Then these Excel tools will let you look at things from a higher level if you want to, say, look at a single doctor or specialty.

It’s easy to turn on Sort and Filter. Click anywhere in your table and then find Sort and Filter. It might be called simply “Filter” in the Data menu, or may be an icon that looks like a funnel. Try Home > Editing > Sort & Filter > Filter. It depends on your Excel version, and it may appear multiple places. When you select it, little arrows appear at the top of the columns. Notice that Excel figures out how big your table is, even if you don’t formally make it a table.

filterNow when you click on that little arrow, it opens a window that allows you to sort or filter the whole table based on the values in that column. For example, if you open the drop-down on Category, deselect “(Select All)” and then click on Clothing, You’ll see just your clothing purchases.

sort filter dialogClick “Clear Filter” to put it all back. You can sort your purchases by amount, simply by opening the drop-down on Amount and clicking Descending (to put your biggest purchases on top). You can even use multiple sorts and filters at once. For example, see all your January Visa purchases, or list your entertainment purchases in order of cost. There are much more complex filtering options that you can explore.

Next month I’ll show you how to do basic Pivot Tables. I’m now going to a monthly blog, the second Monday of the month. Thanks for reading.