VIEWS: 14 PAGES: 6 CATEGORY: Technology POSTED ON: 9/4/2010 Public Domain
Using AutoFilter in Microsoft Excel 2003 Introduction The AutoFilter feature in Microsoft Excel allows you to filter (Hide) rows of data based on criteria you select from drop-down menus. It is important to understand that AutoFilter does not remove the rows that are hidden – all values, even those that are hidden, will still be included in any calculation that you perform, unless you use the SUBTOTAL function (more on this later). It is also important to understand what data is left visible as you enable multiple filters on different columns. Each filter acts as a logical AND with the next. Let‟s say that you have a spreadsheet for a Middle school that contains three columns, Grade Level, Gender, and ELA Performance. If you enable a filter in each column, Grade = 7, Gender = Female, and ELA Performance = 5 (Advanced), the rows that will remain be visible will be those that meet all of the conditions: Those students who are in Seventh Grade AND who are Female AND who tested Advanced in English Language Arts. A final note, if you start out with a file that is nicely arranged and you slice it, dice it, and sort it, you can always get your nicely organized file back again by doing one of two things; either save the file you sliced, diced and sorted under another name and reopen the original file or close the original file without saving the changes you made. Note: This tutorial was created using Microsoft 2003 for Windows. Previous versions of Excel and the Mac versions work in similar ways; however, they may not be identical in every respect. Using the AutoFilter Text Values Columns in Excel can essentially contain three things: text values, numbers, or formulas that produce some sort of result, either text or numeric. If the column that you are working with contains text values (Yes, No, N/A) or numbers that are formatted as text, you will be able to select values that meet the condition you want to look at in a couple of ways (generally, if the alignment has not been forced, anything that is saved as text will be left-aligned in the cell and „real‟ numbers will be right-aligned). Let‟s imagine that you are looking at a spreadsheet that contains regional performance data for schools and counties, part of which is shown here. Assuming that you want to examine the data for a single county in the region, you can do this by clicking on the small arrow to the bottom right corner of the header row cell. 1 When you click on the AutoFilter arrow, you will see a list of the unique values that are contained in the column. By clicking on one of the values shown, all other values in the list will be hidden. One other interesting possibility with text values is to use the Custom filter. Suppose that we want to look at the data for all of the region except Del Norte. To do this, click on the AutoFilter arrow, then click the Custom option, and click on the condition selector (the small arrow next to equals) and select does not equal. Finally, select county (the condition) that you do not want to see now. This is done by either clicking on the small arrow next to the selection box to the right of does not equal and selecting the value or by entering the text value directly. There are advantages to both of these methods. On the side of selecting the value from the drop-down list, you don‟t have to worry about misspelling anything and getting no results. On the side of entering the value directly, there is the option of being sneaky and eliminating many values that are similar. For example, you can hide all rows for Del Norte County Unified and Del Norte COE by entering Del Norte *, and clicking the OK button (the * is called a wildcard and means “anything else from here on – if there were a site called Del Norte - Humboldt Basic Skills Magnet District, it would be hidden as well in this situation. If we entered Del*, we would hide Del Norte, Delmar, and Del Monte, assuming that they were listed). In addition to equals and does not equal, there are some other interesting options for text values that you may find valuable. These include: begins with / does not begin with, ends with / does not end with, and contains / does not contain. You could perform the same trick done above with a wildcard by using either does not begin with or does not contain Del Norte. Always remember that it is wise to have a very good idea of what your data contains when using wild cards or the other text options listed, since they can have unforeseen (and occasionally embarrassing) results. As it seems with everything else in life, the less precise you are, the more likely it is that the Law of Untended Consequences will take a hand and bring along Mrs. Murphy‟s favorite son to your party. Note: To remove a filter, click on the AutoFilter selector for the column and select (All) from the dropdown list. Numeric Values There are also many interesting tricks that AutoFilter can do with columns of numbers. Assume we want to see which schools in Sonoma County had EL students who were making their AMO for English Language Arts. First, I would select the column that listed the counties and filter for those that Equal / Son (the abbreviation used for Sonoma). Next, I need to decide if I want to look at High Schools or Elementary and Middle Schools, since the AMO bar is not the same for both groups. For this example, I will be looking at Elementary (E) and Middle Schools (M), so I will need to filter for all the other schools, districts, and county programs in the column. 2 One way to do this is to use both of the filters available in the Custom AutoFilter to select only Elementary and Middle school sites. For this, we would enter equals E n one filter and equals M in the other. Notice that the Or radio button is selected between the two filter selections. This is because, in our data, sites cannot be identified as both E AND M at the same time. Given this, selecting the AND radio button, will hide all of the data and you will end up with only the header row showing. Click the OK button to apply the filter. Moving over to the column that shows the percentage of EL students who scored Proficient or Advanced in English – Language Arts, I would again use the AutoFilter selector to create a Custom filter. This time, I will be looking for schools where more than 13.6%, the ELA AMO target. To do this, I will select the “is greater than or equal to” option from the “Show rows where” list, and enter 13.6% in the value dropdown. Clicking OK will apply the filter and show only those sites who meet ALL of the filter criteria – they were in Sonoma County AND they were Elementary or Middle Schools AND they had 13.6% of their EL students who scored Proficient or Advanced in English – Language Arts. Another way to use AutoFilter is to find values that are between an upper and lower limit. For instance, you might want to see the sites where the Elementary / Middle school AMO for ELA had been met for this year, but not for the new bar coming up for 2004-05. Assuming you already have the County and Site Type filters in place from the last example, filtering for these sites would require that we again use both of the filter options that are available to us in the Custom AutoFilter. By selecting “is greater than or equal to” 13.6 and “is less than or equal to” 24.4, you can select only those sites who met both conditions (The “or equal to” part includes the value you enter in the value list – so both 13.6 and 24.4 are included. If you don‟t want to include the value you enter, use “is greater than” and “is less than” instead). In this case, if you had selected the Or radio button, you would have ended up with nothing filtered in the column, since all values have to be either greater that 13.6 or less than 24.4 (“greater than or equal to 13.6” means 13.6 to (+) infinity - “less that or equal to 24.4” means 24.4 to (-) infinity… this means that all the numbers from (+) infinity to (-) infinity are covered, so everything shows, nothing is filtered). Sorting Data can be sorted easily in Excel. Sorting can either be in Ascending order (1 - ∞ or A – Z) or Descending order. Be aware that if you sort a filtered list, only the data that is showing will be sorted. Please keep in mind the note at the beginning of this tutorial. One way to sort values is to select a column you have filtered and then click on the AutoFilter selector arrow again. Scroll to the top of the list and, in newer versions of Excel, you will see Sort Ascending and Sort Descending. Clicking on one of these 3 options will sort your list in place (this will sort all visible rows and will keep your data with the correct schools). For earlier versions of Excel, or if you want to sort by several criteria, click on Data, and then Sort. A Sort menu will appear, allowing you to sort by as many as three columns. At the bottom, there are two radio buttons that let you tell Excel if there is a Header Row (the titles at the top of the columns) or not. If you select the Header Row radio button, Excel will list the column names in the “Sort by” dropdown menus. In our example, you might want to sort by County (Ascending), and then by ELA Percent Proficient or Advanced (Descending), and then by Site Name (Ascending). This would create an alphabetical list of Counties starting with Del Norte and ending with Sonoma. The sites in each county would be sorted by their ELA Percent Proficient or Advanced percentage from highest percentage to lowest percentage, and finally the results would be sorted alphabetically by Site Name. One method that will allow you to return your file to its original order (besides the ones mentioned in the Introduction), is to use the Undo command located in the Edit menu or use the Undo button on the Standard menu. Excel allows you to Undo or Redo the last 16 actions you have taken with the spreadsheet by default. Undo button Redo button Notes on Using the SUBTOTAL Function The SUBTOTAL function in Excel has the advantage of only considering the cells that are visible when performing calculations. This presents some interesting possibilities when used in combination with the AutoFilter. For instance, let‟s say that you want to examine your data dynamically depending on which data is visible on your data sheet. By using the SUBTOTAL function, this is a simple proposition. The SUBTOTAL function layout looks like this: =SUBTOTAL(FunctionNumber, SheetName!ColumnRange) The Function Number tells Excel what kind of SUBTOTAL you want to calculate. These are listed below (I have not listed all of the functions available, only the ones I use. If you want more information, search in Help for SUBTOTAL). Function Function Description Num 1 AVERAGE – Calculates the Arithmetic Mean of the data values listed. Blank cells are not counted. 2 COUNT – Counts the number of cells in a range that contain a numeric value. 3 COUNTA – Counts the number of cells in the range that contain alpha-numeric data. If an entire column is selected, the header row title is counted as well, so you may have to subtract one from the result. 4 MAX – Finds the largest value in the range. 5 MIN – Finds the smallest number in the range. 9 SUM – Calculates the sum of all of the numeric values in the range. Text is ignored. 4 If I wanted to calculate the average of the values in Sheet1, Column B, I would place this formula on another sheet (Why? Since you are filtering data, it is possible that your result may be hidden as you change your filter options. Also, if you happen to place the formula in the column it is using for its calculation, the formula will refer to its result – an endless loop – and generally considered to be in Bad Form by most Excel experts. By placing the formula on another sheet, these problems are avoided.) The formula I might put in a cell on Sheet2 might look like: =SUBTOTAL(1, Sheet1!B:B) This formula would calculate the average of the visible numbers on Sheet1 in Column B (the “B:B” means everything from cell B1 to B65536). When doing calculations with the SUBTOTAL function, you are not limited to working with a single column. If you needed to find the average of all of the numbers in Column B and Column C, you could write: =SUBTOTAL(1, Sheet1!B:C) This formula would calculate the average of the visible numbers on Sheet1 from cell B1 to C65536. Another Way to Create Formulas In the example above, I created the formula by typing everything. If you are not comfortable doing this, Excel provides another way to create the formula using your mouse. Let‟s say that I have a workbook with two worksheets in it: Data and Summary. In the data sheet, column A is called Site and has 10 rows for ZZZ Charter and 10 rows for ABC Unified. In columns B through G, there is data for six different test areas Very Originally called Test Data 1 through Test Data 6. If I want to see how the average of Test Data 1 through Test Data 6 changes for the two sites, I can create an AutoFilter for column A (click on the A at the top of the column to highlight the entire column) and then go to Data / Filter / AutoFilter to enable the AutoFilter. Next, I would select the Summary sheet by clicking on the sheet tab at the bottom of the window. I am going to set my Summary sheet up like this: To create the first SUBTOTAL formula, I would select cell B2 on the Summary sheet by clicking in the cell. In the Formula Bar (the long white bar that begins with fx), I would type the beginning of the formula, which includes the formula name and the kind of SUBTOTAL I want (1 = Average): =SUBTOTAL(1, 5 Next, click on the tab for the sheet that contains the stuff we want to average (Data) and then click on the B at the top of the second column to highlight everything in that column. My formula now looks like this: Note that the column that I selected has “marching ants” surrounding it, showing that it is selected. The cursor should now be at the end of our formula in the Formula Bar. Close the parentheses “)” and then click on the green check that to the left of the Formula Bar to complete the formula (pressing Enter on your keyboard has the same effect). All that is left now is to complete the formulas for the rest of the Summary sheet. This can either be done by entering them by hand (Not Recommended) or you can Be Lazy and let Excel do it for you. I vote for Lazy. Click on cell B2 to select it. You should see a small black square in the bottom right corner of the cell. When you hover you cursor over it, it should change to a black +. Click and hold you mouse on this black square and drag to the right, highlighting cells B2 to G2. When you release your mouse button, Excel should have done all of the heavy lifting and created your formulas for you. The beauty of this is that the Summary sheet is now dynamic – if you engage the filter for a site on the Data sheet, averages for that site are displayed on the Summary sheet. If you create a graph using the data on the Summary sheet, the graph will be dynamic as well. Final Notes Excel is a program that will do lots of useful and time-saving work for you. Unfortunately, spreadsheets are a little like a foreign language, it may take a bit of time to understand what is going on. If you are interested in more information on using Excel, I have several other tutorials about using the various Office programs posted at: http://web2.delnorte.k12.ca.us/Tech/tech.html Chris York September 2004 6