Matthew King

ENC 4260

November 17, 2009

Summarizing Filtered Data Using Microsoft Excel 2003

This document will teach you how to filter and use the subtotal formula to make data more useful. Using a sample spreadsheet, this prompt will demonstrate how to use the filter option to narrow results based on criteria and how to use the subtotal function to get various results including counts and totals.

The instructions will be broken down into four sections. Section 1, Set up Filtering, includesthree steps explaining how to set up a Microsoft® Excel spreadsheet for filtering. Section 2, Subtotal Function, includes nine steps on how to set up and implement the Subtotal function. Section 3, Applying Filters, consists of sevensteps on using the filter option in Microsoft® Excel to narrow results in your spreadsheet. Finally, Section 4, Multiple Columns, consists of six steps on the use of applying filters and the subtotal function on multiple columns.

Some definitions used in this document are as follows:

Menu bar (Figure 1-A)

Tool bar (Figure 1-B)

Formula bar (Figure 1-C)

Column headings or Row 1 (Figure 1-D)

Cell (Figure 1-E)

Figure 1

Set up Filtering

To begin I have created a sample spreadsheet with the following data. (Figure 2)

Figure 2

1)Selectthe row to filter, in this case Row 1. (Figure 3) To select the row place your cursor over row 1 until you get an arrow pointing to the right then left click your mouse.

Figure 3

2)SelectData>Filter>AutoFilter from the menu bar. (Figure 4)

Figure 4

3)Widen the rows by placing your cursor between each column until you get the cross hairs as shown (Figure 5), and then double click. You can widen all rows at once by selecting the box in the upper left hand corner (to the left of the ‘A’ and above the ‘1’), then placing the cursor between the columns until you get the cross hairs and double click.

Figure 5

The column headings of Row 1 are now widened and ready to filter. (Figure 6)

Figure 6

We will return to using filters in the Applying Filters section of this document.

Subtotal Function

Following are instructions on using the Subtotal function.

1)Select the cell in which you want the formula results to display. (Figure 7)

Figure 7

2)Select the insert function button in the task bar to display the ‘Insert Function’ window. The insert function is also available through the menu bar by selecting Insert>Function. (Figure 8)

Figure 8

3)Enter Subtotal into the ‘Search for a function’ window and select the ‘Go’ button to find the SUBTOTAL function. (Figure 9)

Figure 9

4)Double Click ‘SUBTOTAL’ in the ‘Select a function’ window to open the ‘Function Arguments’ window, which will assist you in setting up the function. (Figure 10)The SUBTOTAL syntax displays in the ‘Insert Function’ window as well. (Figure 11)

Figure 10

Figure 11

5)Select ‘Help on this function’ link[cp1] in the bottom right hand corner of the “Insert Function” or “Function Arguments” window if you require more assistance or would like to know more about the SUBTOTAL function. (Figure 12)

Figure 12

6)Select the function number associated with the function you require. (Figure 13)

Figure 13

7)Enter the function number into the ‘Function_num’ cell of the Function Arguments window. (Figure 14) In this example we choose[cp2] function number (3) three (COUNTA) to count the number of alphanumeric characters in each cell.

Figure 14

8)Enter the appropriate cells into the Ref1 cell in the Function Arguments window. Notice the formula result in the Function Arguments window.

9)Select OK. (Figure 15) In this example, we select all cells between B2 and B10.

Figure 15

The function code, =SUBTOTAL(3,B2:B10), is present in the function window. (Figure 16, A) You may also type the same in the formula bar. (All formulas typed in the formula bar must start with the equals (=) sign.) The result of the SUBTOTAL function displays in the cell. (Figure 16, B)

Figure 16

Applying Filters

Below are instructions on how to use the filter option in Microsoft® Excel.

1)Select the dropdown arrow for the column to begin filtering. In this case, we will filter by gender. (Figure 17)

Figure 17

2)Select ‘F’ to filter data by the female gender. (Figure 18)

Figure 18

Observe that there are three (3) females in the sample spreadsheet. Notice that the filtered column dropdown arrow is now blue in color. (Figure 19)

Figure 19

3)Select the ‘Last Name’ dropdown arrow to filter by the Last Name heading. Select the last name for which you would like to filter the data. In this example, we select the last name ‘King’. (Figure 20)

Figure 20

Notice there are two (2) people, from our example data, that have the last name of ‘King’. (Figure 21)

Figure 21

4)Select the drop down arrow for the ‘First Name’ column heading. Apply the Custom Filter option. We will filter for first names that start with the letter ‘B’. (Figure 22)

Figure 22

5)Select the dropdown list to view the options with which to filter. (Figure 23)

6)Select“contains” option.

Figure 23

7)Enter into the second column the characters in which you wish to search. In this case, we type the character ‘B’. (Figure 24) Notice the information near the bottom of the ‘Custom AutoFilter’ window which informs you how to use the wild card characters question mark (?) and asterisk (*).

Figure 24

Notice the result of three (3) people from our sample data have a first name starting with the letter ‘B’. (Figure 25)

Figure 25

Multiple Columns

We will now use the sum option of the SUBTOTAL function and filter by more than one column.

1)Select a cell in which to display the results for the number of children.

2)Type=SUBTOTAL(9,D2:D10). You can type the subtotal function in the cell or in the formula bar. Function number 9is the option to sum columns. (Figure 26)

Figure 26

Notice for the nine (9) people there are 20 children. (Figure 27) We will now filter by gender the number of children that belong to the men in the dataset.

Figure 27

3)Select the dropdown menu for the Sex heading and select ‘M’ for the male gender. Thirteen (13) of the twenty (20) children belong to the male gender. (Figure 28)

Figure 28

4)Select the ‘First Name’ column heading to select names that start with the letter ‘B’ as we did earlier. Five (5) children belong to those whose first name starts with the letter ‘B’. (Figure 29)

Figure 29

5)Filter by the female gender. Zero (0) children belong to females whose first name starts with the letter ‘B’. Also, notice that two columns’ (First Name and Sex) dropdown list is blue. This indicates that those two columns filter the spreadsheet. (Figure 30)

Figure 30

6)Return the spreadsheet unfiltered by selecting the blue dropdown lists and select the ‘(All)’ option. (Figure 31)

Figure 31

Conclusion

Filtering spreadsheets can be very advantageous when you have large amounts of data. Using the subtotal function helps to summarize data automatically instead of having to do manual calculations. Microsoft® Excel contains many other formulas to help you work more efficiently. The two examples used in this document will come in handy for most situations and provide a good jumping off point for learning and using other formulas.

If you require more assistance with functions or other formulas in Microsoft® Excel, please contact the Help Desk representative Matthew King at .

[cp1]If you choose to place the screen names in quotation marks (as you did in the previous step), then to maintain consistency, you should do so throughout the prompt.

[cp2]Either “we chose” or “we will choose” then keep the verb in that tense throughout the prompt.