The following step-by-step instructions and examples make use of a sample spreadsheet we have created. While you will ultimately learn how to set up your own data spreadsheet so that it can be filtered, the following examples make a lot more sense if you try them out first on our sample spreadsheet.

So, before moving ahead, please download the sample spreadsheet.

Also, we should note that data filters and analytical functions are available in virtually any spreadsheet program. Our examples though make use of Microsoft Excel. If you are using another program — such as AppleWorks, -- you will need to review your program’s documentation to find the equivalent features.

Filtering Data

Data filtering is a way of organizing spreadsheet data for ease of analysis. Often, spreadsheet filters are used to "hide" rows of data that do not meet certain specified criteria. This allows you to analyze (e.g., count, observe, etc.) only certain "filtered" groups of data.

For example (from our sample spreadsheet), if a group of students were gathering data on males and females of ages 13 - 19, they might want to filter their data based on gender. In order to activate the data filtering function of Excel, we will use a set of commands included in the "AutoFilters" menu. Here’s how to activate Excel's "AutoFilters" feature.

To Turn on AutoFilters:

1. In the sample spreadsheet, click on any of the empty cells just below the header row.

2. Choose Data, Filter, and AutoFilter from the pull-down menus.

3. Selection arrows will appear at the top of each column.

4. In our example, you should click on the Gender down-arrow and choose Male or Female. Notice that this action causes the spreadsheet to show only records with the words "Male" or "Female" in the Gender column. For instance, to find out how many females chose English as their favorite subject, you would first choose "Female" as the Gender filter, and "English" as the Favorite Subject filter. Then, you would count the remaining (filtered) records.

5. To show all the records again, choose the Gender down-arrow and select All. To turn off AutoFilters (and thereby remove the selection arrows from the tops of the columns), simply repeat step #2, above.

Custom Filters

You are not limited to the choices that AutoFilters provides (e.g., filtering by "Male" or "Female" gender in the above example). You can use Custom Filters to specify any sub-set of records you choose by selecting a range of values from within a particular data set (spreadsheet).

For example, to create a custom filter for "males older than 15" in our sample spreadsheet you would: Using our example above to custom filter males older than 15, try the following:

1. Turn on AutoFilters (using the procedure described above), if it is off.

2. Using the down arrow for Gender, choose "Male" as your filter

3. Using the down arrow for Age, choose custom

4. The Custom AutoFilter dialog box will appear.

5. You will see four fields with down arrows. In the upper left one choose "is greater than". In the upper right field, type in 15.

Creating Functions

Spreadsheet programs such as Excel offer a range of mathematical functions — such as sums, averaging, standard deviation, etc. — that can be easily applied to ranges of data. The functions available in Excel can help students perform easy mathematical/statistical analysis on the numerical data they have gathered for many kinds of curriculum projects. For example, you can use the SUM function to add the number of hours per week a student spent on homework vs. watching TV (always an interesting comparison for both students and their parents). You might use the AVERAGE function to discover the average height of all the boys in the class vs. the average height of the girls. Maybe students are learning to create and manage a household budget and want to be able to multiply numbers using the PRODUCT function. Any numerical data in a spreadsheet can be analyzed using functions.

Here’s how you can easily access the data analysis functions of Excel:

Excel's Formula Palette (on Macintosh version of Excel) or the Function Wizard (on Windows versions) makes entering functions an easy process. This saves students from the possibility of making mistakes in spelling or syntax, or having to refer to a manual. Of course, we should also note that many teachers actually use spreadsheet functions to teach students how to create proper mathematical equations. If that’s where you’re headed, you should bypass the Palette/Wizard and have students enter their functions directly on the command line. But for everyone else, the following is a step-by-step list of instructions on entering a spreadsheet function or formula:

1. Make sure "Formula Bar" in the View/Toolbars menu is checked. (If it is not checked, the formula bar is not turned on, and the following directions will not work!) When checked, the formula bar should appear as one of your toolbars. It shows (from left to right) the "name box", showing the address of the selected cell, (i.e. B2), a down arrow, an equal sign ("Edit Formula" symbol), and an empty box where the value of the selected cell will show.

2. Select the cell (by clicking on it) in which you want to put the function or formula by clicking on it.formula.

3. Click the "=" (Edit Formula) button in the formula bar. An ‘X’ and a checkmark will now appear in the function bar. The ‘X’ is for canceling your action, and the checkmark is for accepting it when you are satisfied.

4. Choose the function you want to insert by clicking the down arrow on the Functions drop-down list (to the left of the "=" button). IMPORTANT: if you accidentally choose the wrong function from the list, you must cancel the entire action and start from the beginning of step 2 again.

5. A dialog box with various types of fill-in fields (dependent on the function chosen) will appear. You now must enter the arguments for the function. Cell addresses (row, column) can be specified and separated by ":" to delineate a range. An example would be B4:B9, which would use all the numbers in B4 to B9, inclusively. If the sum of the numbers in these cells was desired, the SUM function would be chosen from the drop-down function list. and B4:B9 would be entered in the Number 1 field of the palette or wizard. Clicking OK would create the formula =SUM(B4:B9) for the desired cell.

The functions used in Excel can be combined with specific cell names. For example: =SUM(A1, A6, A10) will add the contents of the three cells. Be sure to separate the cell names with commas.

Explore other functions (IF, SUMIF, LOOKUP, DATE & TIME…) from the drop-down list by selecting them, and read about their uses in the Function Palette or Wizard that opens. Once you get familiar with more of these functions, you will better understand the various tasks that Excel can accomplish

Information on this site that has been produced by Sun Associates is Copyright 1997 - 2013 Sun Associates and is available for individual, one-time, use by educators. Duplication is prohibited without permission. All other material is the property of its authors and Sun Associates makes no warranty for its use or accuracy.