OzGrid

Level 2 - Lesson 23 - Filter

< Back to Search results

 Category: [General,Excel]  Demo Available 

Filter

Excels Filter is one Excels most useful features as it allows us to quickly and easily view data from within a table or list by hiding data we do not want to view. It does this by hiding rows that are within our data. One thing to keep in mind about the fact that it hides rows is that all columns within the row will be hidden. It not possible to hide only a specified amount of columns. So hiding a row means the entire row! 

There are three types of filters you can create: by list values, by format, or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both. To get the best results, it is best not to mix the different types of data within a column such as text and numbers or numbers and dates.

To apply Filters to your table or list click within any single cell of your data and go to Sort & Filter options under the Data tab.  Select the Filter option. Excel will detect your data by using what is known as the Current Region of the selected cell. So, as long as the table or list is set up correctly the Filters will be applied to all headings. If not, you can select your headings and then go to Sort & Filter options under the Data tab and select the Filter option. You will see the Filters as small grey boxes with arrows on them.  Click on any one of them and you will see a number of options.  We will look at the data in the large white box first.  You should see:

  • (Select All)

  • < A list of your data for that column>

Let's look at each.

Select All

No big secrets here! This will simply show all your data in the column chosen if your data is being filtered down.

While the (Select All) option is always available it will have no effect on unfiltered data. You can tell if a list or table is currently filtered as the arrow on the grey box changes and all visible row numbers will turn blue.

< A list of your data for that column>

This heading is in reference to all the items that you have in your currently selected column. The list will always be in ascending order, ie; A - Z, 1 - 100 regardless of how your data is sorted. You will only see one instance of each item regardless of how many times it appears within your list. In other words, you could have the name John in your list 20 times, but you will only see it appear in this list once. If however, you selected John from your list using a filter, you would see all 20 rows containing John and all other rows between hidden.

Lets look at the other options available to us under the Filter button.

Sort A to Z/Sort Smallest to Largest

Selecting this option sorts your filtered list in A to Z order if you have text in your columns or Smallest to Largest if you have numbers in your columns.  Note that even though your Filter switch is active, there are no hidden rows.

Sort Z to A/Sort Largest to Smallest

The reverse of the previous option, selecting this sorts your filtered list in Z to A order if you have text in your columns or Largest to Smallest if you have numbers in your columns. Again, note that even though your Filter switch is active, there are no hidden rows.

Sort by Color

Selecting this option will sort your list by either font or cell colour (Note that there is an Automatic option here.  Automatic is the main colour of your printer (usually, but not always black).  There is also an Custom Sort option here, that will enable you to easily customise your filters.  Again, no hidden rows here either.

Custom Sort

When you select Custom Sort from the Sort by Color options, you will see the Sort Dialog and the headings will be Column, Sort on, Order.  These are pretty self explanatory and allow you to sort your list by more than one column at a time. 

Clear Filter From ....

Selecting this option will clear the Filter from the column you are currently clicked in.

Filter by Color

Selecting this option will filter your list by either font or cell colour. 

Text/Date/Number Filters

Using this option you can drill down and glean much more detailed information from your data.  If you have text in your column, the option will read Text Filters, if you have dates in your column, the option will read Date Filters.  If you have numbers  in your column, the option will read Number Filters.  Options are different for each of the three data types.  Each data type has a Custom Filter option at the end of the list.

Once you have selected the setting for your criteria, a dialog box will pop up with your selection in the first white box on the left.  You would then move to the box immediately to the right. This is where you select the actual criteria that your setting will be applied to. If you click the drop arrow in this box, you will see a list of all unique items that are within the selected column.

So for instance if you selected the Custom Filter option from a column that contains a list of data, let's say the 12 months of the year, and selected the setting greater than and the criteria April, and clicked OK, then you will see all the months that are greater than April. The reason we have used this example is to again stress the importance of using real dates in your cells and formatting them accordingly. In other words, if you simply had text dates, or text month names in your cells, and custom filtered as described above, Excel would hide only the row containing the word April. In other words, you would not get the result you expected.

You will probably also have noticed (stated on the dialog box) that you can use a question mark (?) to represent any single character and the asterisk (*) to represent any series of characters. An example of using this might be if you had a list of people's names and you only want to view all the names containing the letter g. To do this you would choose equals from the setting area and in the criteria box you would type *g* and select OK. You would be presented with a list of names that contain the letter g within them. You should note that the criteria you set is not case sensitive.

If you have a list which actually contains either one or both of these wildcard characters, and you wish to filter your list to display them only, you need to first type ~ (Tilde) followed by the wildcard character.

You will also have noticed that there are two option buttons on the Custom Filter Dialog. These are And and Or. Using the same example as above (Names) we could select begins with, G, select the or option and in the two boxes below select begins with and L. This will produce a list of names beginning with either G or L. If we chose begins with and G, then selected the and option and selected does not contain and n, we would get a list of all names beginning with G and not containing the letter n.

The terms in the Criteria Settings Box that contain the words greater or less will apply to numeric data only. All other settings apply to both text and numeric data.

Compound Filtering

Compound filtering in this context refers to filtering a table by more than one column. Doing this enables us to filter down a table by many different criteria. As mentioned before, when you apply a second criteria to a column that is already filtered, the first criteria will be removed. This is as opposed to applying a second criteria to your table in another column.

By doing this, we are telling Excel that we only want to filter down all visible rows (or rows that have not been hidden by the Filter). An example of this could be a table with 10 columns and "X" amount of rows, with the first column being headed Names and the fourth column being headed Age. We could apply a filter criteria to the Names column to show only John, and then apply a filter to the Age column to show only ages greater than 20. Doing this our table would end up displaying only the information relating to people with the name John who are older than 20.

Clearing a Filter

It is really easy to clear Filters.  If you want to clear them all, select the Clear button from under Filter & Sort options on the Data tab.  Alternatively if you only want to clear a Filter on one column, select the Clear Filter From .... option.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and

Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples


Gallery



stars (0 Reviews)