home & schedule | class blog | syllabus | contact | grades

Practice with the sorting, filtering, & subtotals tools.

Practice with the database tools

use this example to practice inserting subtotals

You will probably have to reformat the data to make your subtotal tool work for you.

Use the Subtotal tool and, at each change in date, have the automatic subtotal sum the numbers in columns B, C, D, F, & G

Was that useful?

I would guess that it probably wasn't because it didn't subtotal at useful intervals, so let's set up a way to make the interval useful

select column B and insert a new column named Year and formatted for numbers with no decimal places

you now need to add the four digit year numbers in the relevant cells in column B

rename column A as Date and format it so that the date reads only as the month and day

• the year should disappear.

this has broken up the data in the original column A into two columns, each of which can be subtotaled

now do the same subtotal task you did above

• be sure it is summing the numbers you want because the column numbers have changed

did this work better?

you can use your cut and paste tools to move the subtotal labels to better places on the row where they are displayed

you can format the cells in the subtotal rows to make them stand out more clearly

you can also hide unneeded columns if you want to control the display

try a few of the other subtotal types on this data

• are some of them irrelevant for some of the data in this worksheet?
• note also that the tool gives you a grand total, a sum of the subtotal sums

use this example to practice sorting

• sort by author
• sort by location
• sort by title, then by author
• if you sort by a single column, as opposed to sorting by multiple columns, what happens to the integrity of your data?
• if you include column A in your sort, is the value of the sequential numbering series affected?

use this example to practice auto filtering

go to column DB and filter for top "Religion - none"

• How many records does that retrieve?
• don't count the rows; look at the bottom of the worksheet for the answer

now add a filter for "Region - mountain"

• now how many records are in your new group?
• Why has this happened?

now add a filter for "Res16 - farm"

• you have cut down this data set from 2905 to how many?

create a few questions of your own and see how you can use the Custom Filter tool to create new data sets

try a custom sort

• go to AY and AZ and show the rows where Health equals Excellent or Good, then where Happy equals Not Too
• what does this give you?

try another custom sort

• go to Age (column A) and use the Top 10 tool to show the top 18 items (all the respondents who were 89)
• do it again, but first show all the records, then use the Top 10 tool to show the top 10 percent of the records
  • how many does this return?

one more time

• in column A, use the custom filter to show you all the records where the age is more than 88 or less than 18

database tools | database practice exercises | live database practice | next session