Staff Development Daze

1
Staff Development Daze

• June 27 28
• Tony Gauvin

2
Schedule

• Monday June 27               
• 900 1200              Basic Excel
• 1200 100              Lunch for all workshop
  participants
• 100 400                Advanced Excel
• Tuesday June 28
• 900 1200              Basic Access
• 1200 100              Lunch for all workshop
  participants
• 100 400                Advanced Access
• Thursday June 30 
• Time TBA (2hrs.)       Outlook Highlights
• All materials available at
• http//perleybrook.umfk.maine.edu

3
Working with computers

• Some basic rules
• Computers are stupid!
• Computers do exactly what you tell them to do
  because of rule 1
• If you get a wrong answer or result it is because
  you gave the computer bad data or bad
  instructions (GIGO)
• Most applications have self-help features, use
  them
• Hit F1
• Look for ?
• Topright corner of application or toolbar

4
Difference between Spreadsheets and Databases

• Spreadsheets (Excel) are electronic ledgers
• Store, manipulate and present numbers
• Databases (Access) are electronic file cabinets
• Receive, store, organize and present data
• Use the right application
• Save time and effort
• Decrease frustration

5
Quick history of spreadsheets

• 1978
• Robert Frankston Dan Bricklin invented
  VisiCalc, the first spreadsheet.  It came out
  with the Apple II computer.  VisiCalc did very
  well in its first year because it could run.  On
  personal computers, could perform simple math
  formulas, and gave immediate results.
• 1983
• Lotus 123 was introduced. It allowed people to
  chart information and identify cells.  For
  example cell A1.
• 1985
• Lotus 123 number 2.
• 1987
• New spreadsheet programs such as Excel and Corel
  Quattro Pro were introduced.  This allowed people
  to add graphics.  They are different because they
  include graphic capabilities.
• 2001
• Spreadsheet programs in use today are Excel,
  Appleworks, Filemaker, and Corel Quattro Pro.

Source http//library.thinkquest.org/J0110054/His
tory.html
6
The Spreadsheet abstraction

• An (near) infinite series of rows and columns
  called Cells that
• Store numbers (and other stuff)
• Store formulas that use other information in
  other cells and produce a results to be displayed
• A bunch of other neat stuff
• Formatting
• Charting
• What-if scenarios

7
Basic Excel

• To learn Excel we will build a simple worksheet
  (Microsofts name for spread sheet)

8
Advanced Excel (afternoon)

• Topics
• Formulas and Functions
• Formatting
• Importing and exporting data
• Working with Large Spread Sheets
• Anything else anyone wants to cover

9
Excel Project 1

• Creating a Worksheet and an Embedded Chart

10
Objectives

• Start and Quit Excel
• Describe the Excel worksheet
• Enter text and numbers
• Use the AutoSum button to sum a range of cells

11
Objectives

• Copy a cell to a range of cells using the fill
  handle
• Format a worksheet
• Create a 3-D Clustered column chart
• Save a workbook and print a worksheet

12
Objectives

• Open a workbook
• Use the AutoCalculate area to determine
  statistics
• Correct errors on a worksheet
• Use the Excel Help system to answer questions

13
Starting Excel

• Click the Start button on the Windows taskbar,
  point to All Programs on the Start menu, point to
  Microsoft Office on the All Programs submenu, and
  then point to Microsoft Office Excel 2003 on the
  Microsoft Office submenu
• Click Microsoft Office Excel 2003
• If the Excel window is not maximized,
  double-click its title bar to maximize it

14
Starting Excel
15
Customizing the Excel Window

• Right-click the Language bar
• Click Close the Language bar
• Click the Getting Started task pane Close button
  in the upper-right corner of the task pane
• If the toolbars are positioned on the same row,
  click the Toolbar Options button
• Click Show Buttons on Two Rows

16
Customizing the Excel Window
17
Entering the Worksheet Titles

• Click cell A1
• Type Extreme Blading in cell A1 and then point to
  the Enter box in the formula bar
• Click the Enter button to complete the entry
• Click cell A2 to select it. Type Second Quarter
  Sales as the cell entry. Click the Enter box to
  complete the entry

18
Entering the Worksheet Titles
19
Entering Column Titles

• Click cell B3
• Type Direct Mail in cell B3
• Press the RIGHT ARROW key
• Repeat the last two steps for the remaining
  column titles in row 3, as shown on the following
  slide

20
Entering Column Titles
21
Entering Row Titles

• Click cell A4. Type Inline Skates and then press
  the DOWN ARROW key
• Repeat the previous step for the remaining row
  titles in column A, as shown on the following
  slide

22
Entering Row Titles
23
Entering Numbers

• Click cell B4
• Type 58835.35 and then press the RIGHT ARROW key
• Enter 97762.50 in cell C4, 71913.73 in cell D4,
  and 85367.37 in cell E4
• Click cell B5
• Enter the remaining fourth quarter sales provided
  on the next slide for each of the three remaining
  product groups in rows 5, 6, and 7

24
Entering Numbers
25
Summing a Column of Numbers

• Click cell B8
• Click the AutoSum button on the Standard toolbar
• Click the AutoSum button a second time

26
Copying a Cell to Adjacent Cells in a Row

• With cell B8 active, point to the fill handle
• Drag the fill handle to select the destination
  area, range C8E8
• Release the mouse button

27
Determining Multiple Totals at the Same Time

• Click cell F4
• With the mouse pointer in cell F4 and in the
  shape of a block plus sign, drag the mouse
  pointer down to cell F8
• Click the AutoSum button on the Standard toolbar
• Select cell A9 to deselect the range F4F8

28
Determining Multiple Totals at the Same Time
29
Changing the Font Type

• Click cell A1 and then point to the Font box
  arrow on the Formatting toolbar
• Click the Font box arrow and then point to Arial
  Rounded MT Bold
• Click Arial Rounded MT Bold

30
Changing the Font Type
31
Bolding a Cell

• With cell A1 active, click the Bold button on the
  Formatting toolbar

32
Increasing the Font Size of a Cell Entry

• With cell A1 selected, click the Font Size box
  arrow on the Formatting toolbar
• Click 24 in the Font Size list

33
Changing the Font Color of a Cell Entry

• With cell A1 selected, click the Font Color
  button arrow on the Formatting toolbar
• Click Violet (column 7, row 3) on the Font Color
  palette

34
Centering a Cell Entry across Columns by Merging
Cells

• With cell A1 selected, drag to cell F1
• Click the Merge and Center button on the
  Formatting toolbar

35
Formatting the Worksheet Subtitle

• Select cell A2
• Click the Font box arrow on the Formatting
  toolbar and then click Arial Rounded MT Bold
• Click the Bold button on the Formatting toolbar
• Click the Font Size box arrow on the Formatting
  toolbar and then click 16
• Click the Font Color button on the Formatting
  toolbar

36
Formatting the Worksheet Subtitle

• Select the range A2F2 and then click the Merge
  and Center button on the Formatting toolbar

37
Using AutoFormat to Format the Body of a
Worksheet

• Select cell A3, the upper-left corner cell of the
  rectangular range to format
• Drag the mouse pointer to cell F8, the
  lower-right corner cell of the range to format
• Click Format on the menu bar
• Click AutoFormat on the Format menu
• When Excel displays the AutoFormat dialog box,
  click the Accounting 2 format

38
Using AutoFormat to Format the Body of a
Worksheet

• Click the OK button
• Select cell A10 to deselect the range A3F8

39
Using the Name Box to Select a Cell

• Click the Name box in the formula bar and then
  type a3 as the cell to select
• Press the ENTER key

40
(No Transcript)
41
Adding a 3-D Clustered Column Chart to the
Worksheet

• With cell A3 selected, position the block plus
  sign mouse pointer within the cells border and
  drag the mouse pointer to the lower-right corner
  cell (cell E7) of the range to chart (A3E7
• Click the Chart Wizard button on the Standard
  toolbar
• When Excel displays the Chart Wizard Step 1 of
  4 Chart Type dialog box and with Column
  selected in the Chart style list, click Clustered
  column with a 3-D visual effect (column 1, row 2)
  in the Chart sub-type area
• Click the Finish button
• If the Chart toolbar appears, click its Close
  button

42
Adding a 3-D Clustered Column Chart to the
Worksheet

• When Excel displays the chart, point to an open
  area in the lower-right section of the chart area
  so the ScreenTip, Chart Area, appears next to the
  mouse pointer
• Drag the chart down and to the left to position
  the upper-left corner of the dotted line
  rectangle over the upper-left corner of cell A10
• Release the mouse button
• Point to the middle sizing handle on the right
  edge of the selection rectangle

43
Adding a 3-D Clustered Column Chart to the
Worksheet

• While holding down the ALT key, drag the sizing
  handle to the right edge of column F
• If necessary, hold down the ALT key and drag the
  lower-middle sizing handle down to the bottom
  border of row 20
• Click cell H20 to deselect the chart

44
Adding a 3-D Clustered Column Chart to the
Worksheet
45
Saving a Workbook

• With a floppy disk in drive A, click the Save
  button on the Standard toolbar
• Type Extreme Blading 2nd Quarter Sales in the
  File name box
• Click the Save in box arrow
• Click 3½ Floppy (A) in the Save in list
• Click the Save button in the Save As dialog box

46
Saving a Workbook
47
Printing a Worksheet

• Ready the printer according to the printer
  instructions and then click the Print button on
  the Standard toolbar
• When the printer stops printing the worksheet and
  the chart, retrieve the printout

48
Quitting Excel

• Point to the Close button on the right side of
  the title bar
• Click the Close button
• Click the No button

49
Starting Excel and Opening a Workbook

• With your floppy disk in drive A, click the Start
  button on the Windows taskbar, point to All
  Programs on the Start menu, point to Microsoft
  Office on the All Programs submenu, and then
  click Microsoft Office Excel 2003 on the
  Microsoft Office submenu
• Click Extreme Blading 2nd Quarter Sales in the
  Open area in the Getting Started task pane

50
Starting Excel and Opening a Workbook
51
Using the AutoCalculate Area to Determine an
Average

• Select the range B6E6 and then right-click the
  AutoCalculate area on the status bar
• Click Average on the shortcut menu
• Right-click the AutoCalculate area and then click
  Sum on the shortcut menu

52
Using the AutoCalculate Area to Determine an
Average
53
Clearing Cell Contents

• Fill Handle
• Select the cell or range of cells and point to
  the fill handle so the mouse pointer changes to a
  cross hair
• Drag the fill handle back into the selected cell
  or range until a shadow covers the cell or cells
  you want to erase. Release the mouse button
• Shortcut Menu
• Select the cell or range of cells to be cleared
• Right-click the selection
• Click Clear Contents on the shortcut menu

54
Clearing Cell Contents

• Delete Key
• Select the cell or range of cells to be cleared
• Press the DELETE key
• Clear Command
• Select the cell or range of cells to be cleared
• Click Edit on the menu bar and then point to
  Clear
• Click All on the Clear submenu

55
Clearing the Entire Worksheet

• Click the Select All button on the worksheet
• Press the DELETE key or click Edit on the menu
  bar, point to Clear and then click All on the
  Clear submenu

56
Deleting an Embedded Chart

• Click the chart to select it
• Press the DELETE key

57
Obtaining Help Using the Type a Question for
Help Box

• Type save a workbook in the Type a Question for
  help box on the right side of the menu bar
• Press the ENTER key
• When Excel displays the Search Results task pane,
  scroll down and then click the link Save a file
• If necessary, click the AutoTile button to tile
  the windows
• Click the Show All link on the right side of the
  Microsoft Excel Help window to expand the links
  in the window

58
Obtaining Help Using the Type a Question for
Help Box

• Double-click the Microsoft Excel Help title bar
  to maximize it
• Click the Close button on the Microsoft Excel
  Help window title bar

59
Obtaining Help Using the Type a Question for
Help Box
60
Quitting Excel

• Click the Close button on the right side of the
  title bar, and if necessary, click the No button
  in the Microsoft Excel dialog box

61
Summary

• Start and Quit Excel
• Describe the Excel worksheet
• Enter text and numbers
• Use the AutoSum button to sum a range of cells
• Copy a cell to a range of cells using the fill
  handle

62
Summary

• Copy a cell to a range of cells using the fill
  handle
• Format a worksheet
• Create a 3-D Clustered column chart
• Save a workbook and print a worksheet

63
Summary

• Open a workbook
• Use the AutoCalculate area to determine
  statistics
• Correct errors on a worksheet
• Use the Excel Help system to answer questions

64
Questions??