SILS iSchool

15 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 | practice exercises | next session

17 Oct | formulas & functions
22 Oct | data display
 18 Oct | Fall Break 
24 Oct | database tools
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

Do these practice exercises to familiarize yourself with the components.


Practice basic spreadsheet tools and formats

Download this Excel worksheet and use it to practice the skills discussed today.

  1. save it in your working folder to a name of your choosing
  2. highlight some columns and try out the Excel formatting functions on the formatting toolbar (that is, try out the formatting functions that are new in Excel and different from those in Word)

back to top

Tidy up the worksheet

  1. rename the sheet tab from Table 1 to Library Profile
  2. then select rows 1 and 2 and delete them
  3. you have now placed the title of the worksheet on the tab and cleaned up the sheet itself. Why might this be preferable to the way the original author created the worksheet?
  4. now go to Page Layout > Page Setup and place the new tab name on each printed page of the worksheet by putting the tab name in the top right header location

back to top

Work with the title

look at the title cells called Service Outlets

where is the phrase "Service Outlets" placed? (check cell D1 - it was originally in cell D3, but we deleted two rows above it)

  • why does it show up where it does? (check the cell formatting for that cell and look at the horizontal alignment. What does this do for the title placement?)

leave this cell alone and move on to the other header cells

back to top

Tidy up the header cells

  1. the current headers range over 1, 2, or 3 cells.  Place the header titles in a single cell atop each column.
  2. For instance  1999  is in cell C2,  Legal Service  is in cell C3, and  Population  is in cell C4
  3. Place  1999  and  Legal Service  in cell C4 along with  Population 
  4. then format the newly created header cells in columns C-J with alignment center, bottom, wrap text
  5. then delete the unnecessary rows by highlighting them and using Right Click > Delete

back to top

Cell formatting

check the cell formatting for the cells in column H

  1. what kind of number formatting do you see?
  2. try out different kinds of formatting
    • format the cells as numbers: check commas, decimal places, and how you want to display negative numbers
    • format the cells as currency: try out different monetary symbols
    • format the cells as accounting
      • what is different between the currency formatting and the accounting formatting? (look at the monetary symbols)
      • why is one format preferable over another?

back to top

Cutting and pasting cells

go to the North Carolina totals at the bottom of column C (it should now be in cell D86)

  1. copy the total in that cell and then paste it into the cell four rows below (which should be now D90)
    • did the same number appear?
    • why or why not?
  2. since the values you copied in the step above are still on the clipboard, go to the cell two rows below and prepare to paste the value in this cell
    • but before you paste, right click and select paste special
  3. at the dialog box, check Value
    • did you get the same result as your first paste effort?
    • why or why not?

back to top

Cell formatting

look at the format of the value you got in the last paste operation

  • why does it look as it does?
  • format the entire column (by formatting cells) to display numbers with no decimal places and with commas as thousands separators

back to top


try two different AutoFill operations

  1. using the GoTo function ( cntl+g ), go to cell J81 (if you have done the delete rows operations above, this cell should have the value 584 in it)
    • using the grab handle on the lower right corner of the cell at the bottom of cell J81, drag the value into the cell to the right
      • what value appears in the new cell?
    • now drag it to the next cell below it?
      • what is the value after the second drag?
    • drag the value to the right as far as you wish and then down as far as you wish
    • what are the values in the cells affected by the diagonal drag?
  2. using the grab handle on the lower right corner of the cell at the bottom of column F, drag the value into the cell below it. 
    • what value appears in the new cell?
    • now drag it to the next cell below it?
      • is the value after the second drag a number that is one more than the value in the original cell?
    • repeat the right drag and down drag operations you did in the previous section, but with the values in these cells
  3. what does this tell you about how the grab handle works?

back to top

Clean up the look of the sheet

  1. remove the bold from all but the header rows
  2. use different fonts and/or font colors for the different classes of libraries

back to top