Excel, a most under used application. Tips that you can use, Auto-filtering
Okay, I’ve got this Excel series going in full swing now. Last night (yawn) I talked about sorting. Okay, okay sorting multiple columns isn’t very exciting or sexy (I guess it might depend on what you’re sorting) and you might be thinking, oh that’s so yesterday. Fine. How about Auto-filtering?
Got your attention now? Yep, Auto-filtering doesn’t sound cool at first, but man I swear is it one of the most powerful and easy to use features in Excel. Frankly, I can’t live without it and once you try it, neither will you.
So imagine you’ve got a big data set. Let’s say it’s customer data. Names, addresses (city, state, zip), phone numbers, products purchased, sales rep, amount purchased, and say last purchase date. Okay. Now, you need all the customers in California who live in San Francisco (area code 415) and spent more than $1,000. Wow … now your data is unsorted and rather large. What do you do? Auto-filter! It’s so simple. First select the upper left cell in your data set. Then go to the data menu and select Auto-filter. You’ll see little menu-like down triangles at the top of each column. So start selecting. First, let’s pick out just California … from the State column, select CA (or whatever you’re calling it …. Excel goes through the data and finds all the options for you!). Then, go to the phone column. Instead of picking something offered, let’s do a custom filter. Choose custom and choose the best fit here. If your phone numbers are entered like 415-xxx-xxxx the choose starts with 415- if it’s (415) xxx-xxxx then choose starts with (415). Cool, now you’ve got California customers with a 415 area code (yes, I know you could have done this first and skipped the state step … but work with me here people!).
Dollars spent … same idea. Custom filter greater than or equal to 1000. Now you have a list that can be selected and pasted into a new sheet with just that data and no gaps.
I use Auto-filter (and Pivot Tables … more on those later) all the time in my work with Qumana. See, I’m the guy who figures out who gets paid how much from the Q-Ads network every month. Our cut-off is $20. So I get the data from the database, look at who’s earned more than $20 total and start from there. I couldn’t do it (and keep my sanity) without using Excel and these cool features.
Next up, Pivot Tables. These are a little more difficult to master, but oh so fun once you’ve gotten the hang of it.
Tags: MS Office, MS Excel, Excel tips and trips, PimpYourWork, PYW
Related Stories
POSTED IN: Excel, MS Office, Software tips, Time savers
1 opinion for Excel, a most under used application. Tips that you can use, Auto-filtering
pimpyourwork.com » Blog Archive » Excel, a most under used application. Tips that you can use, the series
Oct 4, 2020 at 11:42 am
[…] Auto-filter. OMG, this is the coolest feature, and frankly I don’t think it gets nearly enough credit or use […]
Have an opinion? Leave a comment: