Excel 2013 & 2016: File Management—Auto-Filtering

Updated 2015.03.16

 

Objectives:

·        Learn to filter data using equality criteria and the AutoFilter option.

·        Filter data using inequality criteria and the AutoFilter option.

·        Filter data using and and or Boolean expressions and the AutoFilter option.

 

Sample data file: Advising.txt. Note that this is a text file and must be "imported" using the Text Import Wizard. To see how to use the Text Import Wizard, see the lesson on importing text files into Excel.

AutoFiltering

Frequently we only want to see some of the data, such as all CSCI majors, or all BUAD majors. Selecting data based on some rule (criterion) is called filtering the data. To filter the data, you must give Excel a rule to use to decide whether to include or exclude a record from the selection.

 

All of the filtering commands that are discussed below can be done after doing this first:

·       Click on the Data tab.

·       In the Sort & Filter group, click on the Filter button.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image001.jpg

 

You can also apply all of the commands discussed below if you first convert your data to a table.

Example: Select all CSCI majors using AutoFilter

·       To select all CSCI majors, click on the drop-down arrow in the Major column.

·       Turn off the Select All check box.

·       Turn on the CSCI check box.

·       Click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image002.jpg

·       All CSCI majors will be selected.

·       The Major drop-down arrow will turn into a funnel (https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image003.jpg) to indicate that a filter has been applied to this column.

·       The row numbers will also turn blue as another visual indication that a filter has been applied.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image004.jpg

·       To return to the full list:

·       Click on the funnel in the Major column.

·       Click on Clear Filter From "Major".

Example: Select all BUAD majors whose advisor is Frangedakis.

·       The following instructions assume that you have already turned filtering on as described at the top of this page and that all filters have been removed.

·       Click on the drop-down arrow in the Major column.

·       Turn off the Select All check box.

·       Turn on the BUAD check box and click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image005.jpg

·       We now have selected all BUAD majors.

·       We can further restrict our selection by applying a filter to the Advisor column as well. Note that there are two names in the Advisor column. We only want the advisees of Frangedakis. Click on the drop-down arrow in the Advisor column.

·       Turn off the Select All check box.

·       Turn on the Frangedakis check box and click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image006.jpg

·       All BUAD majors (the current selection) who also have Frangedakis for an advisor will be selected and the Advisor drop-down arrow will turn into a funnel. Note that the second filter criterion is applied only to the records that have already been selected (the BUAD majors). If there are some students who are not BUAD majors but for whom Frangedakis is an advisor, they will not be selected. Each time a new filter is applied, it is applied only to the records that have already been selected.

·       To return to the full list:

·       Click on the funnel in the Advisor column.

·       Click on the Clear Filter From "Advisor".

·       Click on the funnel in the Major column.

·       Click on the Clear Filter From "Major".

Example: Select all Seniors

A senior is defined as somebody with 90 or more credits. Since there are a whole lot of numbers above 90, we can't list all of them. What we need is a way of telling Excel that we want to select the record of every student who has 90 or more credits. We can do so by using a relational operator. The relational operators are:

·       = equal

·       > greater than

·       < less than

·       >= greater than or equal to

·       <= less than or equal to

·       <> not equal to

However, Excel does not require us to memorize the relational operators. We can choose them from a drop-down list. To select the seniors, we want a list of all students for whom the "Credits" column is greater than or equal to 90:

·       The following instructions assume that you have already turned filtering on as described at the top of this page and that all filters have been removed.

·       Click on the drop-down arrow in the Credits column.

·       Click on Number Filters.

·       Click on Greater Than Or Equal To…

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image007.jpg

·       The Custom AutoFilter dialog box will open (below).

·       Enter the value 90 in the text box to the right of the “is greater than or equal to” condition.

·       Click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image008.jpg

·       All of the students with 90 or more hours will be selected.

·       To return to the full list:

·       Click on the funnel in the Credits column.

·       Click on the Clear Filter From "Credits".

Example: Select all CSCI and MATH majors.

At many schools, CSCI and Math majors are in the same department or division. If so, a common database task would be to select both the CSCI and Math majors in a single list. Although it is easier to just turn on the appropriate check boxes, we are going to consider another way: a custom filter.

·       The following instructions assume that you have already turned filtering on as described at the top of this page and that all filters have been removed.

·       Click on the drop-down arrow in the Major column.

·       Click on Text Filters.

·       Click on Custom Filter…

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image009.jpg

·       The Custom Filter dialog box will appear (below).

·       In the Custom AutoFilter dialog box, in the first drop-down box in the first row, click on the drop-down arrow and choose Equals if it is not already chosen.

·       In the first row, click on the rightmost drop-down arrow and choose Math.

·       In the area between rows, click on the And button.

·       In the second row, click on the left drop-down arrow and choose Equals.

·       In the second row, click on the right drop-down arrow and choose CSCI.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image010.jpg

·       Click on OK.

 

We get zero records! Why? Because Excel chose "And" as a connector between our two conditions. So our condition is "Select all records where Major equals Math and where Major equals CSCI". The word "and" means "both", so we are asking for Excel to select all records where the major field has both "CSCI" and "Math" in it. Since it is not possible to have both values in a major field, no records were chosen.

 

What we want is the following condition: "Select all records where Major equals CSCI or where Major equals Math.” The word "or" means "either", so we are asking Excel to select all records where the major field is either "CSCI" or "Math". This will give us the correct records. To make this change:

·       Click on the funnel in the Major column.

·       Click on Text Filters.

·       Click on Custom Filter.

·       In the Custom Autofilter dialog box, click on the option button next to the word Or.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-AutoFiltering_files/image011.jpg

·       Click on OK.

Now we have the correct selection.

Example: Select all CSCI and MATH majors, method #2

·       The following instructions assume that you have already turned filtering on as described at the top of this page and that all filters have been removed.

·       Click on the down-arrow in the Major column.

·       Turn off the Select All check box.

·       Turn on the CSCI check box.

·       Turn on the MATH check box.

·       Click on OK.

·       All students whose major is either CSCI or MATH will be displayed. Note that if you use the Custom AutoFilter dialog box, you can only select two values with an Or in between. If you use the check boxes, you can select as many values as you want, and if a cell value matches any of those values, the record will be displayed. In other words, it is as though you have multiple values that are all connected by Ors.

Boolean expressions

The words and and or are called Boolean operators or logical operators and are used to create filters that involve more than one condition. And means "both", or means "either".

 

Boolean expression: An expression that can be evaluated to true or false. However, Excel usually calls these logical tests rather than Boolean expressions.

Example: Select all sophomores

A sophomore is somebody who has at least 30 credits, but fewer than 60 credits. This is a compound condition where both conditions must be true. This means that we must use the word "and" to connect the conditions. If we allowed either condition to be true (an "or" condition), we would get all students with at least 30 credits (sophomores, juniors, and seniors) plus those students with fewer than 60 hours (freshmen and sophomores). In other words, we would get all students; there would be no filtering done at all! To select the sophomores:

·       Click on the drop-down arrow in the Credits column.

·       Click on Number Filters.

·       Click on Between. NOTE: The word “between” in this case means “including both endpoints”, so both the 30 and the 59 will be included.

·       In the second drop-down box in the first row, enter the number 30.

·       In the second drop-down box in the second row, enter the number 59.

·       Click on OK. All sophomores will be selected. Leave these records selected for the next example.

Example: Select all sophomore BUAD majors

Selecting all sophomore BUAD majors involves creating a compound condition for the "Credits" column and a simple condition for the "Major" field. After creating the list of sophomores as described above, do the following:

·       Click on the drop-down arrow in the Major column.

·       Turn off the Select All check box.

·       Turn on the BUAD check box.

The BUAD filter is applied to the existing sophomore filter, and all sophomore BUAD majors will be selected. Leave these records selected for the next example.

Example: Select all sophomore CSCI and Math majors

Since we already have a filter that selects all sophomore BUAD majors, all we need to do is change the selection criteria for the Major column.

·       Click on the funnel in the Major column.

·       Turn off the check box for BUAD.

·       Turn on the check box for CSCI.

·       Turn on the check box for Math.

·       Click on OK.

·       All CSCI sophomores and all Math sophomores will be listed.