Filtering made easy - Image Source: Arce
Filtering made easy

I do quite a lot of floor walking around companies. This allows users who have difficulties or problems with their software to stop me and ask me to help them out of their problem. When I walk around I can see that many people do not know the existence of filtering in a list in Excel. This is a wonderful tool that can really help with the analysis of data.

Filtering will show you just the items you are interested in and hide the ones you’re not. Once you have filtered you can sort and calculate the items on show.

So here is an example of a large list that is cumbersome to read off the information that you may require as you see all of the information at the same time. You can also make silly mistakes reading off the wrong thing.filter1

It is important to note that your list (Data) needs to be one block of information. What I mean is that there are no entire columns or rows that are empty. the filter will only work in the area you are in and miss out the rest of the data past the empty row or column. The odd cell is fine. If you do have empty row or columns, then you need to select the whole area including these for the filters to work on all of your data.

On the Home Tab filter2

Select the sort and Filter filter3

Now select Filter from the list. filter4

Excel places filter buttons in each heading of your list. filter5

The first thing you need to make your mind up about is what is the most important thing you want from this list.

I am going to suggest that we say country is what we are most interested in.

Select the drop down filter button next to the word Country in cell B1

This is what you’ll see. filter6

You’ll see all the countries selected with a tick.

Select the first tick that is next to the (select All)

This deselects all the countries so you can choose which one or more than one country you are interested in.

I suggest we start with selecting one. I am going to select Argentina as it is at the top of my list. filter7

The spread sheet now looks like this with only Argentinian records showing. filter8

You will notice two things. The filter button image has changed so that you can tell which columns you have filtered in. Secondly the numbers of the rows are now in blue with lots missing as they are hidden. Using this simple method you can significantly cut down the time browsing through and checking your data. I will cover some other cool features about tips soon.


Please enter your comment!
Please enter your name here