Transcript Exploring Microsoft Excel 2003

Exploring Microsoft
Excel 2003
Chapter 1 – Introduction to
Excel: What is a Spreadsheet?
Robert Grauer and Maryann Barber
Committed to Exploring
Shaping
the Next Generation of IT Experts.
Office 2003 - Grauer and Barber
1
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
Exploring Office 2003 - Grauer and Barber
2
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
Exploring Office 2003 - Grauer and Barber
3
Case Study: The Clark School
The opening case study focuses on how to use
a spreadsheet. It describes how a spreadsheet
is used to track progress towards a fund-raising
goal. Students are asked to create a formula to
calculate total profits for each of several line
items and to calculate total profits for the
project. The finished worksheet will be
presented to the school principal.
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
5
Rows, Columns, and Cells
Cell referenced by
column, then number
Active cell surrounded
by heavy border
Row headings to the left
of each row. Rows
designated with numbers
Column headings above
each column. Columns
designated with letters
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
10
An Excel Workbook
Menu bar gives lists of
commands
Formatting toolbar
Standard toolbar
Title bar shows name
of workbook
Exploring Office 2003 - Grauer and Barber
11
Opening a Workbook
Use the Look In list box
to specify the folder
containing the file you
want to open
Double-click the file
you want to open
Exploring Office 2003 - Grauer and Barber
12
The Save As Command
Use the Save In list box
to specify the folder/disk
the file will be saved in
Type the new file
name
Exploring Office 2003 - Grauer and Barber
13
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
Exploring Office 2003 - Grauer and Barber
14
Using the Help System
Click the Help menu
Type a question
and click Search
Select one of the search
results and it will appear in
the Help pane
Exploring Office 2003 - Grauer and Barber
15
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
Exploring Office 2003 - Grauer and Barber
16
Modifying the Worksheet:
The Insert Command
Can be used to add
rows, columns, or cells
Exploring Office 2003 - Grauer and Barber
17
Modifying the Worksheet:
The Delete Command
If deleting a cell, specify
whether to move other cells
up or to the left
Specify whether you’re
deleting cell, row, or
column
Exploring Office 2003 - Grauer and Barber
18
Page Setup
Margins tab is used to
set top, bottom, left and
right margins
Page tab controls
print orientation and
scaling
Exploring Office 2003 - Grauer and Barber
19
Page Setup (continued)
Sheet tab is used to
control repeating rows or
columns or print gridlines
Header/Footer tab allows
user to create headers and
footers for each printed
sheet
Exploring Office 2003 - Grauer and Barber
20
Display the Cell Formulas
Exploring Office 2003 - Grauer and Barber
21
The Print Preview Command
View and adjust
margins by clicking
the Margins button
Exploring Office 2003 - Grauer and Barber
22
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
Exploring Office 2003 - Grauer and Barber
23
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
Exploring Office 2003 - Grauer and Barber
24
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
Exploring Office 2003 - Grauer and Barber
25
Cell Referencing

Absolute reference: remains constant when
copied


Relative reference: adjusts during a copy
operation


Specified with dollar signs before the column and
row
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. B$4
Exploring Office 2003 - Grauer and Barber
26
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 student’s exam scores.
These scores do change for each student, so relative references are
needed to make sure each student’s average reflects his/her scores
Exploring Office 2003 - Grauer and Barber
27
Compute the Student Semester
Averages
Absolute and
relative references
used in formulas
Create the formula
in cell E4 and copy
to other cells
Exploring Office 2003 - Grauer and Barber
28
Isolating the Assumptions
New student averages
are automatically
recalculated
Enter new exam
weights in row 13
Exploring Office 2003 - Grauer and Barber
29
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
Exploring Office 2003 - Grauer and Barber
30
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
Exploring Office 2003 - Grauer and Barber
31
The Format Cells Command
Number tab allows you to
specify appearance of
numbers
Alignment tab specifies
vertical and horizontal
alignment
Font tab allows you to
specify font type and size
Borders and Patterns
tabs allow you to create
special effects
Exploring Office 2003 - Grauer and Barber
32
The Completed Worksheet
Shading is used to
identify labels and
assumptions, and to
show class averages.
Exploring Office 2003 - Grauer and Barber
33
Printing Headers and Footers
Use Page Setup
dialog to create a
Header
Exploring Office 2003 - Grauer and Barber
34
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
Exploring Office 2003 - Grauer and Barber
35
Summary

Spreadsheet - the computerized equivalent of
an accountant’s ledger






Divided into rows and columns
Worksheet - an Excel spreadsheet
Workbook - contains one or more worksheets
Cells can contain either a formula or a
constant
Use the Insert and Delete commands to add
or remove cells, rows, or columns
The Page Setup command provides
complete control over the printed page
Exploring Office 2003 - Grauer and Barber
36
Summary (continued)


Range - a cell or range of cells
Formulas in a cell may be copied or moved to
other cells



Absolute reference remains the same when it is
copied
Relative reference adjusts when it is copied
Cells can be formatted in a variety of ways

Select cells, then apply formatting
Exploring Office 2003 - Grauer and Barber
37
End-of-chapter Exercises


Multiple Choice
Practice Exercises








Exercise 1 – Isolate Assumptions
Exercise 2 – Practice with Formatting
Exercise 3 – The Calendar
Exercise 4 - The Checkbook
Exercise 5 - Judson Ford Realty
Exercise 6 - The Solar System
Exercise 7 - Student Budget
Exercise 8 - Excel Templates
Exploring Office 2003 - Grauer and Barber
38
End-of-Chapter Exercises
(continued)

Mini Cases




The Movies
The Cost of Smoking
Accuracy Counts
The Housing Office
Exploring Office 2003 - Grauer and Barber
39
Questions?
Exploring Office 2003 - Grauer and Barber
40