SILS iSchool

24 Oct 2018

Value Added | daily

Class Schedule

Basics | sessions 01-05

22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab

Web Development | sessions 06-11

12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab

Document Markup | sessions 12-14

03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab

Spreadsheets | sessions 15-19

15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
 18 Oct  | Fall Break 

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

29 Oct | spreadsheets lab

Relational Database | sessions 20-26

31 Oct | relational databases
05 Nov | tables
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
 21 Nov | Thanksgiving 

Presentation | sessions 27-30

28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation

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

back to top

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?

back to top

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

back to top