Exploring Microsoft Excel 2003

1
Exploring Microsoft Excel 2003
Chapter 1 Introduction to Excel What is a
Spreadsheet?
2
Objectives

• Describe potential spreadsheet applications
• Distinguish between a constant, a formula, and a
  function
• Distinguish between a workbook and a worksheet
• Explain how rows and columns are labeled
• Download the practice files

3
Objectives (continued)

• Insert or delete rows and columns
• Print a worksheet to show displayed values or
  cell contents
• Distinguish between relative, absolute, and mixed
  references
• Copy and/or move cell formulas
• Format a worksheet

4
Introduction to Spreadsheets

• Spreadsheet a computerized ledger
• Divided into rows and columns
• Columns identified with alphabetic headings
• Rows identified with numeric headings
• Cell the intersection of a row and a column
• Cell reference uniquely identifies a cell
• Consists of column letter and row number

5
Rows, Columns, and Cells
Cell referenced by column, then number
Active cell surrounded by heavy border
Column headings above each column. Columns
designated with letters
Row headings to the left of each row. Rows
designated with numbers
6
Types of Cell Entries

• Constant an entry that does not change
• Can be a numeric value or descriptive text
• Function a predefined computational task
• Formula a combination of numeric constants,
  cell references, arithmetic operators, and
  functions
• Always begins with an equal sign

7
Introduction to Microsoft Excel

• Common user interface with other Office
  applications
• Menus and toolbars are similar to Word and Power
  Point
• Workbook contains one or more worksheets
• Worksheet an Excel spreadsheet

8
Toolbars

• Appear beneath the menu bar
• Contain buttons that perform commonly-used
  commands
• Standard toolbar buttons correspond to most
  basic commands in Excel
• Examples include opening, closing, and saving a
  workbook
• Formatting toolbar buttons correspond to common
  formatting operations
• Examples include boldface and cell alignment

9
The File Menu

• Contains most common commands related to Excel
  files
• Examples
• New command creates a new workbook
• Open command opens an existing workbook
• Save command saves a workbook
• Save As command saves a copy of an existing
  workbook under a different name or file type
• Print command prints all or part of a worksheet

10
An Excel Workbook
Menu bar gives lists of commands
Formatting toolbar
Title bar shows name of workbook
Standard toolbar
11
The Active Cell, Formula Bar, and Worksheet Tabs
Formula bar displays contents of active cell
Active cell is highlighted
Click tabs to move to a different worksheet
12
Hands-on Exercise 1

• Title of Exercise Introduction to Microsoft
  Excel
• Objective to start Microsoft Excel to open,
  modify, and print an existing workbook
• Input file Grade Book
• Output file Grade Book Solution

13
Other

• Page Setup
• Insert and delete rows columns

14
Display the Cell FormulasTurn this in also for
assignments
15
Hands-on Exercise 2

• Title of Exercise Modifying a Worksheet
• Objective to open an existing workbook to
  insert and delete rows and columns to print cell
  formulas and displayed values to use the Page
  Setup command
• Input File Grade Book Solution
• Output File Grade Book Solution

16
Using Cell Ranges

• Range a rectangular group of cells
• May be a single cell or the entire worksheet
• May consist of a row (or part of a row), a column
  (or part of a column) or multiple rows and/or
  columns
• To select a range
• Click left mouse button at the beginning of the
  range
• Hold left mouse button as you drag the mouse
• Release left mouse button at the end of the range

17
Copying and Moving Cells

• Copy command duplicates the contents of a cell
  or range of cells
• Source range the cell(s) you are copying from
• Destination range the cell(s) you are copying
  to
• You can copy to more than one destination ranges
• Move operation transfers the contents of a cell
  or range to another cell or range
• You must use both the Copy (or Cut) command and
  the Paste command

18
Cell Referencing

• Absolute reference remains constant when copied
• Specified with dollar signs before the column and
  row
• Relative reference adjusts during a copy
  operation
• Specified without dollar signs, i.e. B4
• Mixed reference either the row or the column is
  absolute the other is relative
• Specified with a dollar sign before the absolute
  part of the reference, i.e. B4

19
Absolute and Relative References
Absolute references are used to refer to the
weight of each exam. These weights do not change
for each student, so absolute references are
needed to keep those references constant as the
formula is copied
Relative references are used to refer to each
students exam scores. These scores do change
for each student, so relative references are
needed to make sure each students average
reflects his/her scores
20
Compute the Student Semester Averages
Absolute and relative references used in formulas
Create the formula in cell E4 and copy to other
cells
21
Isolating the Assumptions
New student averages are automatically
recalculated
Enter new exam weights in row 13
22
Hands-on Exercise 3

• Title of Exercise Creating a Workbook
• Objective to create a new workbook to copy
  formulas containing relative and absolute
  references
• Input file N/A
• Output file Better Grade Book

23
Formatting Cells

• Format Cells command controls the formatting
  for numbers, alignment, fonts, borders, and
  patterns (color)
• Select-then-do
• Select the cells to which the formatting will
  apply
• Execute the Format Cells command
• Show Example

24
Printing Headers and Footers
Use Page Setup dialog to create a Header
25
Hands-on Exercise 4

• Title of Exercise Formatting a Worksheet
• Objective to format a worksheet using boldface,
  italics, shading, and borders to change the font
  and/or alignment of a selected entry
• Input file Better Grade Book
• Output file Better Grade Book