Although I surely love coffee, I hope you don’t use “coffee filters” more than data sorting filters. My personal favorite filters in PPC are Excel filters. These make my day when it comes to reviewing data, yet too many people are unaware of this handy way of getting through mounds of information.
We’ve all been there…staring at data in Excel and trying to figure out where to start. It can be overwhelming. That’s where filters can come into play. They help simplify the data by taking some of it away based on what you want to look at. In other words, Excel filters basically work like a funnel to only show certain data sets while hiding the rest. You can filter on one column or multiple columns. Then, you can sort, highlight, or whatever else you want to do to the data. When you are done, you can change the filters to look at a different data set or clear them out to view the data in its entirety again.
How to Start Filtering
To get started, first get your data prepped in Excel by adding a header row (if needed) and sorting the data however it makes sense to you.
There are two primary ways of turning filters on:
- From the Home tab, select the icon all the way to the right, then scroll down to select Filter:
2. From the Data tab, select the Filter icon:
You’ll then see little boxes with upside-down triangles appear in your header row (Column B). If you have a filter on, you’ll see a little funnel in the box (Column C).
When you click on the box, a drop down will appear with all the data points in that column.
You can select as many or as few columns as you like, remembering that you’ll narrow down your data further with each filter you select.
Handy Filtering Features
My favorite filter feature is that you can quickly deselect everything by clicking on the “Select All” check mark box. This is a huge time-saver if you have a lot of varying data points in a column but only want to look at a particular few. Let’s say, using the data above, you want to only look at the two “corporate” data sets. You would click on the box to the left of “Select all” and all the boxes would become deselected. Then, just check off the two ad groups beginning with “corporate.”
My second favorite feature is that you can select or deselect all the “blanks.” Blanks are any rows that don’t have data for that column. I select or deselect the “blanks” often, but particularly when I have a column that I’ve added with notes. If I only want to see rows that have a note, I would deselect the “blanks” box and I am left with only the rows with my comments in them.
Here is an example. In this case, I added a column and listed “yes” next to keyword terms I wanted to exclude:
If I just want to see the terms that I want to add as negatives to get my list ready, I select “yes” only from the drop down box in my new Column B. Now, all the other terms are hidden and this is what I see:
Remember that the filter box in Column B changed from an upside-down triangle to a little funnel because the filter is turned on. You can look across your headers and see at a glance which column or columns have the filters on or not. And, if you hover the pointer over the box, Excel will tell you what you are filtering.
If you are done reviewing your data points, you can either make sure all your filters in each column are turned off. Or, if it’s easier, you can click “Clear” as seen in the very first picture of the post and all the filters will be reset.
Using Filters in Everyday Workflow
I tend to export data so I can play around with it easier than I could in the actual interface. Here are a few examples of reports that I export and then use filters to dissect:
- search query reports
- ad performance analysis
- location, device or ad schedule reports
- keyword performance
You can read even more about filters directly from Microsoft Office. Want to read about other Excel tips and tricks? Check out our Advanced Excel post.
Stay tuned for my next blog post on how I use filters to make decisions on search query reports.
How do you use filters in Microsoft Excel? We’d love to hear your tips and tricks in the comments!
