Excel File Management: Auto-Filtering

 

Objectives:

 

Using Excel help (F1):

In the Search For box, type Filter, and press the Enter key.

In the Search Results list, click on Filtering, and read the topics.

In the Search Results list, click on All about AutoFiltering, and watch the tutorial.

 

Excel Bible: Chapter 19

 

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 sorting.

 

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. To filter data, we must use the "Data" | "Filter" | "AutoFilter" command.

 

Example: Select all CSCI majors using AutoFilter

·         Place the cursor somewhere in the data.

·         If you want the filter results sorted, sort the data as described above.

·         Select "Data" | "Filter" | "AutoFilter". Note that drop-down arrows appear at the right of each column heading.

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

·         Select CSCI from the drop-down list. All CSCI majors will be selected. The "Major" drop-down arrow will turn blue, as will the row numbers, as a visual indication that a filter has been applied.

·         To return to the full list, click on the drop-down arrow in the "Major" column and select "All".

 

Example: Select all BUAD majors whose advisor is Frangedakis.

·         Place the cursor somewhere in the data.

·         If you want the filter results sorted, sort the data first.

·         If the drop-down arrows are not at the right of each column heading, select "Data" | "Filter" | "AutoFilter".

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

·         Select BUAD from the drop-down list. All BUAD majors will be selected and the "Major" drop-down arrow will turn blue.

·         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.

·         Select Frangedakis from the drop-down list. All BUAD majors (the current selection) who also have Frangedakis for an advisor will be selected and the "Advisor" drop-down arrow will turn blue. 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 drop-down arrow in the "Advisor" column and select "All". Do the same for the "Major" column.

 

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 (<=)

·         does not equal (<>)

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:

·         Place the cursor somewhere in the data.

·         If you want the filter results sorted, sort the data first.

·         If the drop-down arrows are not at the right of each column heading, select "Data" | "Filter" | "AutoFilter".

·         If a filter is currently being applied (blue arrow), click on the blue drop-down arrow and choose "All" from each filtered column.

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

·         Select "Custom" from the drop-down list.

·         In the first drop-down box in the first row, click on the drop-down arrow and choose "is greater than or equal to".

·         In the second drop-down box in the first row, enter the value 90.

·         Click on "OK". All of the students with 90 or more hours will be selected.

 

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. Since such a selection requires two different values to be selected in the "Major" column, we must create a custom filter.

·         Place the cursor somewhere in the data.

·         If you want the filter results sorted, sort the data first.

·         If the drop-down arrows are not at the right of each column heading, select "Data" | "Filter" | "AutoFilter".

·         If a filter is currently being applied (blue arrow), click on the blue drop-down arrow and choose "All" from each filtered column.

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

·         Choose "Custom".

·         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 "CSCI".

·         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 "Math".

·         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 "CSCI" and where Major equals "Math". 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 drop-down arrow in the "Major" column.

·         Choose "Custom"

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

·         Click on "OK".

 

Now we have the correct selection.

 

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:

·         Place the cursor somewhere in the data.

·         If you want the filter results sorted, sort the data first.

·         If the drop-down arrows are not at the right of each column heading, select "Data" | "Filter" | "AutoFilter".

·         If a filter is currently being applied (blue drop-down arrow), click on the blue drop-down arrow and choose "All" from each filtered column.

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

·         Select "Custom" from the drop-down list.

·         In the first drop-down box in the first row, click on the drop-down arrow and choose "is greater than or equal to".

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

·         In the first drop-down box in the second row, click on the drop-down arrow and choose "is less than".

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

·         Click on the option button labeled "And".

·         Click on "OK". All sophomores will be selected.

 

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.

·         Select "BUAD" from the drop-down list.

The "BUAD" filter is applied to the existing sophomore filter, and all sophomore BUAD majors will be selected.

 

Example: Select all sophomore CSCI and Math majors

Selecting all sophomore CSCI and Math majors involved creating a compound condition for both the "Credits" column (an and condition, already done) and a compound condition for the "Major" column (an or condition). After selecting the sophomore BUAD majors as described above, do the following:

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

·         Select "Custom" from the drop-down list.

·         In the first drop-down box in the first row, click on the drop-down arrow and choose "equals".

·         In the second drop-down box in the first row, click on the drop-down arrow and choose "CSCI".

·         In the first drop-down box in the second row, click on the drop-down arrow and choose "equals".

·         In the second drop-down box in the second row, click on the drop-down arrow and choose "Math".

·         Click on the option button labeled "or".

·         Click on "OK". All CSCI sophomores and all Math sophomores will be listed.

 

 

Updated 2007.04.05