Spreadsheets

This outline covers Microsoft Excel X for Macintosh and Microsoft Excel XP for Windows.

Topical Outline

Getting Started

• What is a spreadsheet?
  • consider a spreadsheet to be the electronic version of a ledger pad
  • a spreadsheet can be used anytime you have information that is best organized in a row and column format and at least some of the data is to be manipulated mathematically (although mathematical manipulation is not a requirement!)
  • how does a spreadsheet differ from
    • a database?
    • a table in a word processing document?
• Macintosh Users: Click here to learn about the Project Gallery in Office X for Macintosh. Use the <BACK> button in your browser to return to this page, do not use <Go Back>.
• Overview of the Excel screen
  • the “strips” (from top to bottom)
    • the Menu Bar
      • contains the menus that you “pull down” to access commands
        • File, Edit, View, Insert, Format, Tools, Data, Window, Help
    • the Standard Toolbar
      • contains icons or “buttons” to execute commands, an alternative to menus
      • first one is a blank sheet of paper, second is an open folder, third is a diskette, etc.
      • the “button” to the left ofis actually a pull down menu to select viewing size
      • visibility is controlled by ViewToolbars…

        When you position the mouse pointer over a button, a mini-prompt “pops up” to tell you what that button does. Further descriptive information appears at the bottom left of the screen in the status bar

    • the Formatting Toolbar
      • the first item is a pull down menu for fonts (e.g. Arial)
      • second is a pull down menu for font size (e.g. 10)
      • the rest of this toolbar contains additional “buttons” for formatting
      • visibility is controlled by ViewToolbars…
    • the Formula Bar is where the action is for spreadsheet entry (more below)
    • the Title Bar of the window
      • on the Macintosh
        • the title bar is the next item you will see; it will read Workbook1 (for untitled worksheets) or filename for saved worksheets
      • in Windows
        • the title bar will be the next item you will see if the spreadsheet window is not maximized but still visible and will read Book1 (for untitled worksheets) or filename for saved worksheets
        • if the spreadsheet window is maximized, the title bar appears at the very top of the screen above the menu bar and it will read the same
    • the Status Bar is at the bottom of the screen and displays information to tell you where you are, what is happening; although this can usually be ignored, you might want to glance here every once in awhile

     

• what about those “tabs” above the Status Bar?
• the spreadsheet work area
• this is the area between the Title Bar and the Status Bar
• columns
  • designated A, B, etc.
  • the 27th column is labeled AA and continues to AZ, then BA, etc.
  • each worksheet contains 256 columns, the last one is IV
• rows
  • designated with a number
  • each worksheet has 65,536 rows
• cell
  • the intersection of a row and a column
  • designated by the column and row coordinates, e.g. B11

    Name Box Excel

  • the active cell will be outlined and its coordinates will be displayed in the formula bar
  • the Name Box (left edge of the formula bar) will indicate the current active cell
  • since there are 256 columns and 65,536 rows, a Microsoft Excel worksheet contains 16,777,216 cells!

  A spreadsheet operates on a coordinate system much like a map. You navigate a spreadsheet, create formulas and functions, and so forth, by referring to the coordinates of cells. If reading a map or any other similar coordinate system is a challenge for you, you may have difficulty with a spreadsheet!

• revisiting the formula bar

  The Formula Bar
  Excel XP	Excel X

  • to enter data into a spreadsheet, click in the desired cell then type
  • anything you type will appear both in that cell and in the formula bar; additional “buttons” will also appear in the formula bar
  • although the data does appear in the active cell, it is not part of the worksheet until it is “entered”:
    • check the data in the formula bar and edit as necessary
      • click in the formula bar, note the flashing cursor similar to a word processing document
      • you may now edit in the formula as you would in a word processor using the cursor left/right keys
      • or click the in the formula bar or press <ESC> to cancel the entry entirely
    • to actually enter the data
      • click the in the formula bar
      • or press the <RETURN> / <ENTER> key
      • or press the <TAB> key
• Viewing Size (aka “Zoom”)

  Viewing Size Drop-Down List

  • click and hold on the triangle, viewing sizes “pop-down”
  • move mouse pointer to select desired size to zoom “in” or “out”
  • Selection
    • if you choose this “size”, Excel will zoom in directly on the current active cell or the currently selected range
    • the resulting “zoom” size will depend upon the size of the selection
• spreadsheet navigation
• Cells can contain
  • text to be used as labels to identify your data (by default, text is left aligned in the cell)
  • numbers (by default, numbers are right aligned in the cell
  • formulas
  • functions

   

• Selecting cells
  • many spreadsheet actions require that you select one or more cells first then act upon them
  • selecting first, then acting upon the selection is a standard windowing technique, whether you are using a Macintosh or Windows computer
  • once selected, you can apply formats, fill down/right, clear, create a chart, sort, and so forth
  • selection methods

Developing a spreadsheet - the steps

Let’s make a spreadsheet!

• Alignment of text used as labels
• Wrapping text within a cell
• Column width & Row height
• How data is displayed
• Entering formulas and functions
  • the Fill command
  • relative versus absolute referencing
  • the If statement
• The final touches, borders and fills!
• using test data
• completing the spreadsheet with real data
• Sorting a spreadsheet
  • single-key sorts versus multiple-key sorts
  • the Sort button
  • the Sort command

More Spreadsheet Tools

• Charting
• The Freeze Panes command
• Inserting/Deleting Rows/Columns
• the Clear command
• the Fill Series command
• using the Format Painter
• adding text boxes and arrows to a worksheet or chart
• adding comments to a worksheet
• conditional formatting
• Printing and Pagination
  • the Page Setup dialog box and its options
    • the Page tab
    • the Margins tab
    • the Header/Footer tab
    • the Sheet tab
  • using Print Preview
  • using Page Break Preview
  • more print and display options
• Date functions
• Templates
  • the concept of a template
  • the template as a file type in Excel
• Concept of consolidation, linking worksheets
• Statistical analysis in Excel

Go Back

Copyright © 2003
last updated August 12, 2003