IBM BASICS - Bio-Link

Report
MS Excel Intro
Adult & Continuing Education
HI!
Instructor:
 How to contact me

• by phone at
• by e-mail at
1
Attendance
2
MATC General Info
Grading (Satisfactory or Unsatisfactory)
 Evaluations (You’ll get your chance)
 Restrooms
 No food or drink in the lab
 Emergencies:

• Tornadoes: Interior hallway
• Fire Exits
3
How Did You Get Here?
Suggested Progression of ACE Computer Classes
Intro to Computers
103-428
Windows
103-439
WORDPROCESSING
SPREADSHEETS
PRESENTATIONS
DATA BASE
INTERNET
MS PowerPoint
103-443
MS Access Intro
103-442
Internet Intro
103-446
MS Access Interm
103-442
Web Page Design
103-446
MS Word Intro
103-437
WordPerfect Intro
103-436
MS Excel Intro
103-433
MS Word Interm
103-437
WordPerfect Interm
103-436
MS Excel Interm
103-433
4
Course Info

Disks
• Required 3.5” diskette
• Must be NEW--never used in any computer
(prevent viruses)
• Should be DS /HD (double sided, high
density)
• Formatted or unformatted, doesn’t matter
5
Course Info

Texts
• Handouts packet required
• Available from MATC Downtown Bookstore
– Call 258-2417 for hours and pickup options

Talk; Watch; Do; Practice
6
Course Plan
This is an introductory course and assumes some
knowledge and/or experience with a computer
and Windows 95 operating environment
Welcome and Worksheet Basics
 Building a Worksheet Formatting
Worksheets
 Managing Workbooks
 Using Functions

7
Tell Me About Yourself

Why are you taking this course?

What do you hope to learn?

What do use Excel for?
8
Worksheet Basics
Starting MS Excel
 MS Excel Layout
 Entering Data into Cells

•
•
•
•

Active Cells
Entering Values
Editing Cells
Formulas
Saving Your Work
9
Why Use MS Excel?

Work with values (numbers), formulas,
and text
•
•
•
•
•
Bookkeeping
Business Plans
Financial Analysis
List Management
Charts and Graphs
10
Windows 95/98 -- Setup
Desktop contains shortcuts--icons that
system has set up to make going
somewhere just a click
 Has Start Menu at bottom that offers
menus
 Documents on Start Menu contain only
RECENTLY USED documents/files

(not all documents you have created appear)
11
Starting MS Excel
Double-Click icon on desktop
 Start Menu  Programs  MS Excel
 MS Office Shortcut Bar

• New Office Document
• General tab
• Blank Workbook
12
MS Office Shortcut Bar




Optional part of MS Office Package
Customizable: can add or change shortcut
buttons
Can use to Open an Office Document (folder
icon)
Can use to create a New Office Document
13
Layout of Excel Environment
Title Bar
Menu Bar
Standard Tool Bar
Formatting Tool Bar
Worksheet Area
Formula Bar
Status Bar
14
Title Bar

Contains name of application and name of
workbook
(sometimes includes pathname especially if on floppy)


Does NOT have to be blue (user-specified color)
Will appear colorized at the top of the active
window
15
Minimize, Maximize, & Restore
Minimize button
make window as small as it will go (usually icon size)
Maximize button
make window as big as it will go (fills the screen)
Restore button
make window the previous size (whatever it was)
16
Closing & Exiting
Close normally means leave a
document or file or make the window
back to an icon
 Exit normally means leave the program
(keyboard short cut ALT-F4)
Close button (box)

closes the window, application or file
17
Two Sets of Buttons

Notice two sets of minimize,
maximize/restore, close buttons
Top set is for the
application (MS Excel)
Bottom set is for the
workbook
18
The Menu Bar




All commands available are in pull down menus
Click on menu item to see options
NOTE that the location of the menu bar can be
changed/customized by the user
Submenus are indicated by 
(more choices available)

Keyboard shortcuts also indicated on menus
usually Control key (Ctrl) + some other key
19
Missing Menu Commands


New feature in Word
2000 is to show only
recently used menu
commands
Additional
commands indicated
by double arrow
• Click to see
additional menu
options
20
Changing Menu Display Option





Tools  Customize 
Options tab
Click Off (so check
mark does not appear)
“Menus show recently
used commands first”
Click “Reset my usage
data”
Answer “yes” to next
dialog box
Close and menus now
appear fully
21
The Standard Toolbar


Buttons that are shortcuts to menu commands
To see what the button does,
• point to the button but do not click
• small box will pop up indicating button function

NOTE that the location of the standard toolbar can be
changed/customized by the user
22
The Formatting Toolbar


Buttons that are shortcuts to formatting menu
commands
To see what the button does,
• point to the button but do not click
• small box will pop up indicating button function

NOTE that the location of the formatting toolbar and
the buttons it contains can be changed/customized
by the user
23
More Toolbars


You may not see some toolbars at start up of
Excel because of someone’s previous
settings
To make toolbars (standard, formatting, etc.)
appear
• View menu  Toolbars
OR
• Right-click on double bar at left end of any toolbar
• Checkmark indicates active toolbars
• Click on toolbar you want to appear
• Drag into place (if necessary)
24
PRACTICE
Start MS Excel
 Show the Word Art Toolbar
 Show the Pivot Table Toolbar
 Practice “docking” and “floating” the
toolbars
 Hide both the Word Art and Pivot Table
Toolbars

25
The Formula Bar



First list box gives address of active cell
Second area gives value of active cell (if any)
NOTE when you start to type something in a cell (we
will do this in just a few minutes), three buttons will
appear on the formula bar
X rejects entry just typed
 accepts the entry typed
= activates the Formula Wizard
26
Scroll Bars

Scroll bars appear when the window
contains information that cannot be
displayed because it is too small
(i.e. the window contains more, but it is either not
wide enough or tall enough to display all)
Can have vertical scroll bars and
horizontal scroll bars
 Appear at right (vertical scroll bar) or
bottom (horizontal scroll bar) of window

27
Using Scroll Bars
Click on arrow to move a bit
 Click and hold on arrows to
move through
 Click and drag on scroll box to
move
 Click on gray area to move a
page
Vertical scroll bar

Horizontal scroll bar
28
The Workbook Area

Fundamentally, Excel is a big table
Columns labeled by letter
Where column and row
intersect is called a cell
Rows labeled
by number
29
The Workbook Area
An Excel worksheet is made up of a large
number of cells
 Each cell is named (or addressed) by the
column and row it is in

• cell in column B, row 5 is addressed B5
• cell in column AB, row 357 is addressed AB357

Cell name (or address) appears on the
left list box of formula bar
30
How Big is a Workbook?

Excel workbook has
• 256 columns
• 65,536 rows
• thus 16,777,216 cells

Excel workbooks can have several
worksheets
• so you can have millions of cells per
workbook!!
31
PRACTICE

Suppose the following exists in an Excel
worksheet. Indicate the cell addresses
necessary to safely navigate this minefield.
1
A
B
C




2
3

4

5




32
Active Cell
Can only type in one cell at a time. This
is called the active cell
 Active Cell has heavy border around it

To type something in a cell, you must first
make it the active cell
33
More on the Active Cell

Note how column (letter) and row
(number) indicators appear bold for
active cell
34
Move Around in a Worksheet

Using the mouse
• position pointer (large cross) on cell you
want to become active (use scroll bars if
necessary)
• click mouse button
• cell becomes active (gets heavy border)
35
Move Around in a Worksheet

Using the keyboard
• use the cursor keys (arrow keys) on the
keyboard to move active cell, one cell at a
time, in the direction of the arrow
• Other keys to use
– PgUp to move up one screen
– PgDn to move down one screen
– Home to move to column A of the active row
– Ctrl+Home to move to cell A1
– F5 to go to any cell
36
Entering a Value into a Cell

Everything you type in Excel “lives” in a
single cell
• text
• numbers
• formulas
Because of formatting, items can
appear to “live” in several cells, but this
is not real
Everything in Excel “lives” in a single cell 37

Example of Everything
“Lives” in a Single Cell
Type “Introduction to MS Excel” in B5
 NOTE: Appears to be in cells B5, C5,
and D5


Appears this way because of column
width and formatting settings
38
Knowing Where Something “Lives”

Value (text, number, or formula) the cell
contains is given on the formula bar
when that cell is the active cell
Contents of cell B5
appear in formula bar
39
Entering Values into Cell
Select the cell you want the value to
appear (make it active)
 Enter the value (text or number)
 As you type the value (text or number) it
appears simultaneously in the cell and
on the formula bar
 If you make a mistake you can use
delete keys to correct

40
Two Delete Keys

There are two keys to erase text as you
type
• Backspace key
– above the Enter key on the number row
– erases character to left of insertion point
• Delete key
– located to right of Enter key on small keypad
between main keyboard and 10-key keypad
– erases character to right of insertion point
41
PRACTICE

Set up an Excel worksheet using the
“minefield” where the value of the cell
indicates the number of mines in that cell
1
A
B
C




2
3

4

5




42
The Leading 0

Excel will automatically drop leading
zeros (0) on numbers
• Example type “007”, Excel drops “00” and
just writes “7” in the cell

To get leading zeros (0), must trick
Excel
• do this using apostrophe ( ‘ )
• thus “ ‘007” will appear as 007 in the cell
43
Editing Cell Contents

To correct a cell’s contents
• Make cell active
• Type to replace cell contents
• Hit “Enter” or move to new cell
(Old cell contents are replaced with new)

To prevent replacement, hit “Esc” key to
restore cell contents
• only works before you complete editing by
hitting “Enter” or moving to new cell
44
Editing Cell Contents
To edit a cell, you must make the cell
active
 Edit using the formula bar

• Contents of cell appears on formula bar
• Position I-beam pointer in value appearing
on formula bar
• Click to get insertion point (blinking vertical
line “|”)
• Use arrow keys to move within value and
delete keys to edit
45
Editing Cell Contents

Can also edit cell contents directly in the
cell
• Double-click on cell to edit
• Get insertion point (blinking vertical line “|”)
• Use arrow keys to move within value and
delete keys to edit

Hit “Enter” or move to any other cell to
complete editing
46
Undo and Redo
Undo button (counter-clockwise
pointing) will successively undo your
actions
 Redo button (clockwise pointing) will
redo anything you undid
 Note the downward arrowhead to pull
up a list box to undo/redo multiple
actions at once

47
PRACTICE
In cell E1, type your first name
 In cell F1, type your last name
 In cell E2 type “Introduction to MS
Excel”
 Edit the above value to change “MS” to
“Microsoft”
 Edit the above value to change
“Introduction” to “Intro”

48
Types of Cell Values
Recall how everything you type “lives” in
a single cell
 What can you type in a cell

• numbers (1, 2, 0.01, 1e14)
• text (up to 32,000 characters per cell)
• formulas
49
Formulas
Formulas are expressions that you
enter in Excel to perform some
mathematical operation
 All formulas start with an equal sign “=“
 Formulas use cell addresses as
variables in the calculation
 Example

• = A1+B2 (add the values in cells A1 and
B2 together)
50
More on Formulas

With formulas
• formula is displayed in formula bar
• result is displayed in cell
Excel allows you to create your own
formulas
 Excel also has many formulas
programmed for your use
 Excel’s power comes from these
formulas

51
Mathematical Operators
Formulas rely on mathematical
calculations
 List of mathematical operators
Symbol
Operation
+
Addition
Subtraction
*
Multiplication
/
Division
^
Exponentiation

52
PRACTICE

In the notes beside this slide enter a
formula to calculate:
•
•
•
•
•
the sum of cells A1, A2, and A3
the sum of cells A1,B1, and C1
the product of half of A4 and B2
A3 subtracted from the sum of A1 and A2
B2 raised to the C3 power
53
PRACTICE

Using the Excel worksheet with the
values from the “minefield” exercise,
compute the result of these formulas
•
•
•
•
•
A1+A2+A3 = 3
A1+B1+C1 = 7
A4/2*B2 = 6
A1+A2-A3 = -1
B2^C3 = 27
54
Order of Operations

If use multiple operators in one formula,
order of operations controls what
calculation is done first
1) Exponentiation
2) Multiplication, division
3) Addition, subtraction

Use parentheses “( )” to override the
order of operations
• Whatever is in parentheses is done first
55
Order of Operations Examples

2^2*3
• result 12

1+1+3*4
• result 14

1 + (1 + 3) * 4
• result 17

(1 + 1 + 3) * 4
• result 20
56
Entering a Formula

To enter a formula
• ALWAYS start with an equal sign “=“
• Create the formula using cell addresses
and mathematical operators
• Hit “Enter” or move to a new cell to
complete the formula
57
Entering Cell Addresses in Formulas
1) Type the address (e.g. “B5”)
2) Use the keyboard
a) use arrow keys on the keyboard to move
dancing-border box to desired cell location
b) type operator to get rid of dancing-border
box
58
PRACTICE

Using the Excel worksheet with the
values from the “minefield” exercise,
• Write a formula to compute the total
number of mines in each column.
• Write a formula to compute the total
number of mines in each row.
• Be sure to put the totals in a “reasonable”
location
59
Mistakes in Formulas

If Excel cannot calculate the formula
(we will explore reasons later) it will
enter “#VALUE!” in the cell
• To correct, edit formula or data

If the calculated value is too big to
display (because the column is not wide
enough) Excel will enter “########” in
the cell
• To correct, make column wider
60
Formulas = Excel’s Power
Formulas can range from the really
simple (add 2 cells together) to the very
complex (calculate loan payments
based on a certain rate for a certain
number of years)
 Excel will recalculate all formulas each
time the contents of a cell is altered

61
PRACTICE





In cell A7, write the following statement, “The
total number of mines in the grid equals”
In cell A8, enter a formula using the column
totals to calculate the total number of mines
Correct the statement in cell A7 to read “Total
number of mines is”
Change the formula in A8 to calculate the
total mines using the row totals
The results should be the same even though
the formulas are different. Why?
62
Save a Workbook
When you create a workbook, it exists
in RAM
 To make it “permanent” you have to
save it

• to your hard drive
• to your floppy
63
Do we need to review saving?
64
Save vs Save As...

Save
• used first time a file
is saved
• writes over any
existing version of
the file
• happens when you
click on the disk icon
on the standard
toolbar

Save As...
• Used AFTER firsttime save
• gives you the
opportunity to
rename the file
• must use File menu
command
65
Save (Save As...) Dialog Box
Specify
pathname
Create New Folder
Go to
Favorites
Document name
(blue is replace
what is there)
66
Excel Versions

If you choose “Microsoft Excel Workbook”
(the default) as your document type, you save
as an MS Excel document
• you cannot open and use this document with
earlier versions of Excel

To open with earlier versions, select different
document type
• (e.g. Microsoft Excel 5.0/95 Workbook)
67
PRACTICE


Save your worksheet to disk with the filename
“Excel1”
Exit MS Excel and shut down the computer
68
REVIEW EXERCISE

Open a new Excel workbook; create the
following table

Save this workbook on your floppy as
“mosquito”
69
Building a Worksheet
Annotate a Worksheet with Cell
Comments
 Ranges
 Moving and Copying Cell Contents
 Changing Column Width and Row
Height
 Inserting, Deleting, Clearing Cell,
Columns, and Rows
 Using Help

70
Annotate a Worksheet
with Cell Comments
Cell Comment = an explanatory note or
comment added to the worksheet,
which can be displayed, edited, and
printed
 Cells comments are associated with
assigned cell
 Comments are indicted by red indicator
in upper-right corner of cell

Cell with comment
Cell without comment
71
Creating a Cell Comment
Select cell to have comment and make it
active
 Insert menu (OR Right-click on cell)
 Choose Comment

Comment text box is displayed
Status bar displays info about the cell address
and author of comment
Type comment in Comment text box
 Click anywhere outside Comment text
box to complete

72
Viewing a Cell Comment
Select cell with Cell Comment indicator
(small red triangle in upper-right corner)
 Position (using mouse) cross pointer
over cell and click to make active
 Cell Comment comes up after a beat

73
Edit a Cell Comment
Select cell with comment to be edited
 Insert  Edit Comment
 Make corrections using standard editing
techniques
 Click anywhere outside Comment text
box to complete

74
PRACTICE




Open your Excel1 file from your disk
Enter a comment in the totals for each
column (comment should read “total of
column A”, etc)
Enter a comment for the grand total value of
the table (comment should read “grand total”)
On your mosquito worksheet add a comment
to the wet spring multiplier that says
“coefficient accounting for spring moisture
value”
75
Printing Cell Comments


Worksheets can be printed with or without cell
comments
Default is to NOT print cell comments
 To PRINT cell
comments
•
•
•
•
•
File
Page Setup
Sheet tab
Comments list box
select where
comments to be printed
76
Hiding/Displaying Cell Comments

Default (automatic setting) is to
• show comment indicator
• hide comments until requested by the user

To change default
settings
•
•
•
•
Tools
Options
View tab
Comments options
– None
– Comment indicator only
– Comment & indicator
77
Clearing Comments

To remove a comment
• Activate the cell with which the comment is
associated
• Edit menu  Clear  Comments
OR
• Right-click cell with comment to be removed
• Choose Delete Comment from pop-up menu

Comment and indicator are deleted
78
Comment Toolbar

View  Comments
• Can be used to perform Comment
operations
• Use mouse pointer to see what buttons do
• Click toolbar close box to close
79
PRACTICE






Edit your comments on the column totals to
read “total mines column A”, etc.
Delete the comment from the grand total cell
Hide comment indicators
Hide comments
Unhide comments and indicators
Print your worksheet showing your comments
80
Working with Ranges
Range = one or more blocks of cells
that can be formatted, moved, copied,
or referred to as a unit
 Ranges can be

• adjacent--containing cells that are touching
• non-adjacent--containing non-adjoining
cells
81
More on Ranges
Excel refers to a range by the upper-left
cell address, a colon (:), lower-right cell
address
 Examples of cell ranges

• A2:A5 (cells A2, A3, A4, A5)
• A2:B5 (cells A2, A3, A4, A5, B2, B3, B4,
B5)
• A2:D2 (cells A2, B2, C2, D2)
82
Selecting a Range

To select an adjacent range of cells,
• start in the upper-left (lower-right) cell and
drag to the lower-right (upper-left) cell to be
included in the range
• cells included in range will be highlighted
with active cell (cell where you started to
define the range) appearing normal
83
Example of Cell Ranges


A2:A5

A2:B5
A2:D2
84
Expanding Adjacent Cell Ranges

Can always add more onto a cell range
• With range selected, hold down “Shift” key
• Click to new upper-left (lower-right) cell to
extend range
85
Non-Adjacent Cell Ranges
Non-adjacent cell ranges consist of cells
that are not in direct contact
 These ranges are named by having
each individual range listed as normal
with a comma (,) separating parts of the
non-adjacent range
 Examples

• A2:A5,D2:D5 (cells A2, A3, A4, A5, AND
D2, D3, D4, D5)
86
Creating Non-Adjacent Cell Ranges
Procedure to create non-adjacent cell
ranges is essentially to create several
adjacent cell ranges holding down the
control (CTRL) key in between
 Example

•
•
•
•
select A2:A5
hold down the CTRL key
select D2:D5
now have non-adjacent range
A2:A5,D2:D5
87
Naming a Range
Ranges can be given names for easy
reference
 Range names are part of worksheet and
are saved along with other worksheet
data
 Range names are displayed in the
address box on the left of the formula
bar

88
Range Naming Rules
• Must start with a letter or underscore (_)
character (NOT a number)
• Remaining characters may be letters,
numbers, periods, underscore characters
• NO SPACES
• Cannot be same cell address names
• Up to 255 characters although 15 normally
displayed
• May use upper- or lower-case but names
displayed in upper-case
89
Naming a Range
Select range to be named
 Insert  Name  Define

Type range name here
Names of other ranges
appear here
NOTE range reference
90
Little Box for Changing Range
When you click on this little box, the
dialog box will hide and you can get a
better view of your worksheet to modify
the selected range
91
Using Range Name

To go to a named range
• use list box on left of formula bar to access
named ranges
• select named range from list
• range becomes selected
92
PRACTICE




Select range B2:D10. Name range as
“consec_range_1”
Select range E3:E10;F3:F10;H3:H10. Name
range as “nonconsec_range_2”
Use the list box on the formula bar to go to
the consecutive range. (Click anywhere to
deselect range)
Use the list box on the formula bar to go to
the non-consecutive range. (Deselect range)
93
Moving Cell Contents
May decide to edit worksheet by moving
contents of certain cells to another
location
 Can do this two (2) ways

• drag and drop
• cut and paste

Note when you move cell, you move
contents, I.e. by moving cell contents no
“hole” is left in the worksheet
94
Moving Cell Contents
Using Drag and Drop
Select cell(s) whose contents are to be
moved (click and drag to select cell or
range)--the range becomes highlighted
 Click on border of highlighted range
 Drag range to new location (paste area
indicator indicates where range will be
placed)
 Drop (release mouse button) to put in
new location
95

Drag and Drop Over Something
If the paste area cell(s) contain
something, Excel will prompt whether to
replace the existing contents with the
new
 If you accidentally say “OK” and
replace, you can always undo to
remove placement

96
Moving Cell Contents
Using Cut and Paste
Select cell(s) whose contents are to be
moved (click and drag to select cell or
range)--the range becomes highlighted
 Click the scissors on the standard
toolbar (Ctrl+X is keyboard shortcut)
 Dancing border appears
around text to be moved

97
Moving Cell Contents
Using Cut and Paste
Move active cell to where you want the
range to be pasted
 Click the clipboard on the standard
toolbar (Ctrl+V is keyboard shortcut)
 Range is pasted using active cell as
upper-left of paste range
 NOTE Excel does NOT prompt for
replacement; it automatically replaces
any contents within the paste area
98

Matching Range Sizes

If you select an area to paste into, it
must be the same size and shape as
the original area for paste to be
completed
99
PRACTICE
Select your first name (in cell E1) and
drag to cell E5
 Select your last name (in cell F1) and
drag to cell F5
 Select both your first and last names
and drag to cells H5 and I5
 Now cut and paste to cells A10:C10
(why did it not work?)
 How would you make it work?

100
Changing Column Width
and Row Height
Recall how everything “lives” in a single
cell although it may overlap several
 Can change the dimensions of a cell by
changing the width and height of the
column and row the cell is within
 NOTE column width and row height
affects the WHOLE column and row

101
Use Column and Row Borders
Position pointer on right column border
(or bottom row border)
 Pointer becomes sizing pointer

• | for columns

|
•
for rows
Click and drag in direction arrows
pointing to adjust size
102
Use Dialog Boxes
Format  Row (or Column)  Row
Height (or Column Width)
 Enter new number in CHARACTERS

• value (0-255) in number of characters that
can be displayed in “standard” font

Click OK to apply settings
103
Best Fit
Best fit is an automatic adjustment to
the column or row based on the largest
value in the selected range
 To apply Best Fit

• double click on column or row border (must
do this in heading area)
OR
• Format  Row (or Column)  Auto Fit
104
PRACTICE

Open your “Mosquito” worksheet
• Adjust column widths to be 25 characters
• Adjust column widths to fit largest column
entries
• Adjust row heights to be 25
• Adjust row heights to fit

Save your changes
105
Copying Cell Contents
Copying makes a duplicate of the cell
contents (including formatting) in a new
location
 Copy by

• drag and drop
• Copy and Paste
• AutoFill
106
Copying Cell Contents
Using Drag and Drop
Select cell or range to be copied
 Point to right border of range (NOT box
in lower-right corner)
 Hold control “CTRL” key

• get small plus “+” sign and single sided
arrow
• status bar says “Drag to copy cell contents”

Position paste area box in desired
location and release mouse button
107
Copying Cell Contents
Using Copy and Paste
Select cell or range to be copied
 Click copy button on standard toolbar
(Ctrl+C is keyboard shortcut)
 Select cell or range to be pasted to
 Click clipboard on standard toolbar
(Ctrl+V is keyboard shortcut)
 NOTE must place in copy areas of
corresponding size & shape to original

108
Copying Cell Contents
Using AutoFill
ONLY works when copying contents to
adjacent cells
 Select cell or range to be copied
 Click box in lower-right corner of
selection
 Drag to adjacent cell or range
 Release mouse button to complete copy
109
PRACTICE
Use drag and drop to copy the first
column of Mosquito to column I.
 Change the date at the top of the
column to 1998.
 Use copy and paste to copy the
remaining column headings and
“TOTAL” to the second table.
 Change the wet spring multiplier to
3.95.

110
Using AutoFill to Complete
a Data Range
Can use AutoFill to create a series of
numbers based on data already entered
into a worksheet
 Relies on Excel’s ability to determine
increment between cells and uses that
increment to complete rest of range

111
Using AutoFill to Complete
a Data Range
Enter at the first and second values of
the data range in adjacent cells (either
in column or rows)
 Select range
 Click box in lower-right corner of range
(called the fill box)
 Drag in direction (column or row) to
complete range

112
Example of Using AutoFill
Enter in B5 the number 5
 Enter in B6 the number 10
 Activate AutoFill and drag through cells
B7 (15), B8 (20), B9 (25)
AutoFill determines the difference
between the first 2 cells to be five (5)
and then automatically fills the values of
subsequent cells so they differ by 5

113
More on AutoFill
AutoFill MUST be able to determine the
increment between the first and second
value of the range
 Determined increment will be used to fill
selected range
 Can be used to complete

• numbers
• dates
• times
114
PRACTICE
Click on a different worksheet tab (sheet
2) at the bottom of your Mosquito
worksheet
 Use AutoFill to add

•
•
•
•
column of months (full month name)
column of months (three-letter abbreviation)
row of week days
column of times separated by half-hour
increments (from 7:00 a.m. - 11:30 p.m.)
115
Single-to-Multiple Copying
Remember Excel needs a paste area of
similar configuration to a copy area
 Could take single cell and copy value to
whole range of other cells

• because range just looks like single cell
over and over
116
Controlling What Gets Pasted


So far, we have not
been using menu
options to paste cell
contents
Edit  Paste
Special can be used
to control what gets
pasted
117
Copying Formulas
Moving and copying formulas is
different and using other values (text or
numbers)
 Copying formulas saves time over
entering the same formula over and
over, just changing the cells referenced
 Example

• A1+B2+C3 is the “same formula” as
X1+Y2+Z3
118
PRACTICE
Use the Window menu to return to your
minefield worksheet
 Copy the formulas that calculate the
column totals to the row immediately
following (you may move the totals text
and grand totals to a lower row) (why are
the values different)
 Copy the formulas that calculate the row
totals to the column immediately adjacent

119
Formula References
When you copy a formula, you keep the
same mathematical operations, just
change what cells are used in the
calculation
 You can control what cells are changed
in the calculation by changing how the
cells are referenced in the mathematical
formula

120
Example of Cell References
A
B
C
D
1 base price $
12,500.00
Car 1
Car 2
2
engine
$ 1,000.00 $ 5,000.00
3
stereo
$ 2,000.00 $ 2,500.00
4
dealer discount $
500.00 $ 1,000.00
5
retail cost


Retail cost is the base price + engine + stereo
- dealer discount
What would be the formula to calculate
• retail cost for car 1
• retail cost for car 2
121
Type of Cell References
Relative--adjust to new location when
copied or moved
 Absolute--remains the same when
copied or moved
 Mixed--contains a relative and an
absolute reference

122
Relative References
Adjusts to new location when copied or
moved
 Cells are referenced as “normal” (e.g.
A2, B5)
 Example of use

• formula to add a series of numbers for
computing totals
123
Absolute Reference
Remains the same when copied or
moved
 Cell references can be absolute in
column or row or both
 Cells are referenced with a dollar sign
($) preceding the column and row
reference (e.g. $A$2, $B$5)
 Example of use

• one value does not change (car base
price)
124
Mixed References
Contain a relative and absolute
reference
 Use when retaining reference to a
certain column or row
 Cells are referenced with a dollar sign
($) preceding the part of the reference
to remain fixed (e.g. $A2, B$5)
 Example of use

• always refer to the value in column A, but
125
in different rows down a table
Copying Formulas Revisited
When you copy a formula, be sure your
original formula has the correct type of
references
 Copying formulas updates all relative
references (and relative parts of mixed
references) based on new location

126
PRACTICE

Change to a new sheet and create the following
table
A
B
C
1 base price $
12,500.00
Car 1
2
engine
$ 1,000.00 $
3
stereo
$ 2,000.00 $
4
dealer discount $
500.00 $
5
retail cost


D
Car 2
5,000.00
2,500.00
1,000.00
Enter a formula using the appropriate cell
references to calculate the retail price of car 1
Copy the formula to calculate retail price of car 2
127
PRACTICE


In row 6 enter a formula to calculate the total
“extra package” (engine + stereo - dealer
discount) for car 1
Copy this formula to calculate the extra package
cost for car 2
Do you get the right answers?
 Change the base price to $18,550
 What values change? Why?

128
Selecting a Whole Worksheet

To select a whole worksheet
(EXCLUDING PAGE FORMATTING)
blank box to left of column A and above
row 1
129
Moving or Copying
to Another Worksheet
Select cell or range to be moved or
copied
 Click to desired worksheet using tabs at
bottom


Paste to desired location in active
worksheet
130
References Between Worksheets
All reference types (relative, absolute,
mixed) can exist between worksheets
as well as within any worksheet
 Excel puts the worksheet name followed
by an explanation point (!) in front of the
cell reference
 NOTE if the worksheet name has a
space, Excel will put the worksheet
name within single quote (‘) marks
131

Examples of Worksheet References

Sheet1!A2
• cell A2 on worksheet named Sheet1

‘FY 99’!A2:B7
• range A2:B7 on worksheet named FY 99

‘FY 99’!A$2
• mixed reference that will keep the value of
row 2 as constant and value of column as
relative
132
Clear, Delete, and Insert Cells
Allow you to remove cell value or whole
cell
 Clear affects only active cell or selected
range
 Delete and Insert affect surrounding
cells
 Excel will NOT create “holes” in the
worksheet when cells are deleted or
inserted
133

Clear Cells



Removes value
and/or formatting of
cell
Hit delete key “DEL”
to clear cell contents
To clear more
options
• Edit  Clear 
select item to clear
from list
134
Delete Cells
Removes cell and all contents including
formatting from worksheet
 Select cell or range to delete
 Edit  Delete
 Excel will prompt you how to move
surrounding cells
to fill the gap

135
Delete Column or Row
Select column or row by clicking on
header
 Edit  Delete
 Does NOT prompt for adjustment of
surrounding cells

136
Insert Cells
Insert cell or range in selected area
 Insert  Cells
 Excel will prompt you how to move
surrounding cells to fit around insert

137
Insert Column or Row
Select column or row by clicking on
header
 Insert  Column or Row
 Does NOT prompt for adjustment of
surrounding cells

138
Insert Copied Cells
May come a time when you want to insert a
copied selection into an existing worksheet
 Selecting range to be copied and choosing
copy
 Select upper-left cell of range to be copied
to
 Insert  Copied Cells (NOTE this option
does NOT appear unless there is a copy
item on the clipboard)

139
PRACTICE
Insert 4 rows at the top of your car
calculator table
 In row 1 enter the title “Marty’s Fair and
Square Quality Autos”
 In row 3 enter “Manufacturer-Approve
Retail Price Calculator”
 In row 4 enter today’s date

140
141
Help and the Office Assistant

MS Excel comes with on-line user’s
guide
• Guides you through certain tasks
• Tells you (and in some instances) shows
you how to do things
• Provides quicker reference than large book
142
Accessing the Office Assistant
Office Assistant is the animated paper
clip and the interface to using help
 Standard Tool Bar

• Click balloon with question mark
• Office Assistant appears
• Type question in space
143
Example of Using Help
Activate the Office Assistant
 Type “How do I save a document”
 Note the choices that come up
 To view more information on the
choices, click on that choice

144
Help Menu
Can view help topics in format similar to
table of contents
 Also has tab to click for index listing

• type first few letters of help topic
• note the list goes to topics starting with
those letters
• double-click topic to view
145
PRACTICE
Use the Office Assistant to learn about
absolute and relative references
 Use the Office Assistant to learn how to
move and copy worksheets

146
REVIEW EXERCISE


Open your Mosquito worksheet
Insert four (4) rows at the top of the
worksheet. Type the following information
• in cell A1 “Wisconsin Mosquito Population”
• in cell A2 “population (1,000) = (wet spring
multiplier)*(average absolute temperature)^2”
• in cell A3 “absolute temperature =
5/9*(temperature-32)”
147
REVIEW EXERCISE


Use these expressions to create Excel
formulas to calculate the average
temperature and mosquito population for
March 1997. (NOTE: you will have to use an
absolute reference for the calculation of the
mosquito population.)
Copy the above formula to complete the table
entries for the remaining months in the 1997
table and the 1998 table. Be sure to change
the absolute reference so you are using the
correct wet spring multiplier in the 1998 table.
148
Formatting a Workbook






Applying Formats to Numbers
Change Fonts, Font Sizes, and Font
Styles
Align Cell Contents
Format Cells with Borders and Color
Use Conditional Formats
Check Spelling in the Worksheet
149
Applying Formats to Numbers
Controls the appearance of numerical
values
 Can choose from pre-defined formats or
create your own
 Formatting affects how the number
appears, not the value of the number

150
Using Formatting Bar

Buttons on formatting bar can be used
to access number formatting
Activate
currency
formatting
(puts dollar
sign and 2
decimal
places)
Activate
percent
formatting
(multiplies
by 100 and
puts in
percent
sign)
Activates
comma
separators
Increase
number of
decimal
places (will
add zeros to
match
number of
spaces)
Decrease
number of
decimal
places
151
PRACTICE





Go to sheet 2 in Mosquito
In cell A1 enter the following formula
“=Rand()” (This is a function that generates a
random number between 1 and 0. We will
learn more about functions later.)
Copy this formula through A10.
Select the range A1:A10 and paste the value
over the same range.
Enter a formula in the corresponding B
column that multiplies the value in A column
152
by 1000
PRACTICE
Activate the comma separator format for
the values in column B
 Activate the currency format for the
values in column B
 Activate the percent format for the
values in column A
 Change the number of decimal places
displayed in the values in column A to
two
153

Selecting Number Format




Select range to apply format
Format  Cells  Numbers tab
Select category to
match your data
type
Select from presets
available in selected
category
Note sample
appearance
154
Example: Formatting Numbers

To format regular numbers, choose the
Type number of
Number category
decimal places
to appear or use
arrows to
increment value
Select to use
comma separator
each 1000
Select how
negative numbers
will appear
155
Dates in MS Excel
MS Excel treats dates and times as
numbers
 The way that a time or date is displayed
on a worksheet depends on the number
format applied to the cell
 MS Excel will recognize date or time
entry and change cell's format from the
General number format to a built-in date
or time format.
156

More on Dates in MS Excel

Options selected in Regional Settings of
Control Panel determine the default
format for date and time and characters
recognized as date and time separators
• United States-based systems
– the colon (:) for time
– slash (/) for date on ·

To type a date and time in the same
cell, separate the date and time with a
space
157
Dates and Numbers
MS Excel numbers the days starting
from January 1, 1900 to determine a
“date value”
 Thus can subtract one date from
another to determine the intervening
number of days
 NOTE: Excel does not “like” dates
earlier than 1/1/1900--does NOT
recognize them as dates for use in
calculations

158
Example: Formatting Dates

To format appearance of dates, choose
the Date category
Preset choices for
appearance of
dates
159
PRACTICE







In cell A12 enter today’s date
In cell B12 enter your birth date (be sure to use
the 19xx in your year)
In cell C12 enter the formula =A12-B12
What is the result? What does the date mean?
Change the format of the result to reflect the
number of days between the two dates
Use this value to calculate your age in seconds
Change the birth date to that of a significant
other What happens?
160
Example: Custom Formatting

To customize format appearance of
data, choose Custom category
Create your own
format here
Select from presets
Let’s take a closer look at these custom formats
161
Custom Formats for Numbers
use “#” to represent any number when
building format
 negative numbers appear

• with/without minus sign
• with/without parentheses
• different color

Example: #,##0.00;[Yellow](#,##0.00)
• 1234 appears as 1,234.00
• -1234.5 appears as (1,234.50)
162
Custom Formats for Dates


Each use of variable adds that many
“characters” in that part of the date
• Use “d” for day
• Use “m” for month
• Use “y” for year
Example
• mmmm-dd-yyyy means February-01-1999
• mmm d yy means Feb 1 99 and Feb 23 99
163
Custom Formats for Other Data

Other numerical types can be
customized
•
•
•
•
scientific notation
currency
percentages
etc.
164
PRACTICE
Change the appearance of the dates
from the previous exercise to dd-mmmyy (how does it treat 2000 years vs
1900?)
 In column C calculate the difference
between column B and 500*value in
column A

• format the negative numbers in blue and in
brackets [ ] (you will have to create a
165
custom format)
Changing Character Appearance

Font = one complete collection of letters,
punctuation marks, numbers, and special
characters with a consistent and
identifiable typeface, weight (roman or
bold), posture (upright or italic) and type
size

Two ways to change the font
• using Formatting bar
• using dialog box
166
Changing Font Using Formatting Bar
Select range of cells to change font
 Click arrow head to bring down font list
box
 Choose font from list
 Font is applied to
selected range

Note: preview of font
appearance
167
Changing Font Using Dialog Box
Select range of cells to change font
 Format  Cells  Font tab
 Select font from list
 Click “OK” to apply

NOTE: if you click on a
font in the list, you may
then use the arrow keys to
scroll through the list and
preview a text sample of
the applied font
168
Font Size
Font size indicates how large the type
appears
 Larger font size means larger
appearance of type
 Font size measured in points
 72 points = 1 inch
 Common font sizes for regular text are
10 or 12 point

169
Changing Font Size
Select range to change
font size
 Using Formatting bar

• select size from dropdown list
• OR type size in box
(hit “Enter” to accept value)
170
Changing Font Using Dialog Box





Select range of cells to change font
Format  Cells  Font tab
Select font size from
list
OR type value in box
Click “OK” to apply
171
Format Font Appearance

Can control the appearance of the font
by changing
•
•
•
•
•

Bold
Italics
Color
Underlines
Superscript and Subscript
Again, may use formatting bar for some
options or dialog box for more
172
Formatting Font Appearance
Using Formatting Toolbar

Select range to change font appearance
Make font
appear in
boldface
Make font
appear in
italics
Place a
single line
under each
value in cell
173
Formatting Font Appearance
Using Dialog Box



Select range to change font appearance
Format  Cells  Font tab
Change font
appearances as
desired
• font style
• underline
• effects

Click “OK” to apply
changes
174
Change Font Color


Select range to change font color
On formatting bar
• click font color list box
• choose color from box

In dialog box
• Format  Cells  Font tab
• choose font color from list box

Click to deselect range and see
applied font color
175
PRACTICE






Return to your Mosquito sheet
Make the column headings in both tables
appear in bold
Change font size for the column headings to be
12 point
Change the font size of the formula
explanations to be 14 point
Change the font color of the headings to be dark
blue
Change the wet spring multiplier value to be red
176
Align Cell Contents

Horizontal and vertical alignment of cell
contents can be changed
• Horizontal alignment possibilities
– left (starts at left cell border)
– center (centered between left and right cell borders)
– right (ends at right cell border)
• Vertical alignment possibilities
– top (starts at top cell border)
– center (centered between top & bottom cell borders)
– bottom (starts from bottom cell border)
177
Setting Horizontal Alignment

Different data values have different
default alignments
• text default is left aligned
• number default is right aligned
• date default is right aligned

Can use formatting bar or dialog box to
change horizontal alignment
178
Setting Horizontal Alignment
Using Formatting Bar

Select range to change horizontal
alignment
Align
horizontal
left
Align
horizontal
center
Align
horizontal
right
Merge cell
and align
horizontal
center
179
Merging Cells
Merging cells makes selected cells into
one and keeps the value of the upper
leftmost cell
 Merging cells can mess up your ability
to insert columns and rows so be
careful!
 Can center horizontally across several
cells without merging them

180
Setting Horizontal Alignment
Using Dialog Box




Select range to change alignment
Format  Cells  Alignment tab
Select horizontal
alignment from list
box
Click “OK” to apply
alignment
Let’s take a closer
look at the choices
181
Horizontal Alignment Choices

General (default)
• aligns text to left, numbers to right

Left (indent)
• aligns text or numbers on left

Center
• aligns text or numbers centered between
left and right cell border

Right
• aligns text or numbers on right
182
Horizontal Alignment Choices

Fill
repeats contents of selected cell(s) until cell
is full

Justify
can be used ONLY with two or more lines of
wrapped text
aligns wrapped text with a cell on both the
left and right

Center Across Selection
aligns text in the center of selected range
183
Setting Vertical Alignment
All data values have default (automatic
setting) vertical alignment of bottom
 Use dialog box to change vertical
alignment

184
Setting Vertical Alignment
Using Dialog Box




Select range to change alignment
Format  Cells  Alignment tab
Select vertical
alignment from list
box
Click “OK” to apply
alignment
Let’s take a closer
look at the choices
185
Vertical Alignment Choices

Top
aligns contents at cell top

Center
centers contents in cell

Bottom (default)
aligns contents at cell bottom

Justify
align cell contents at both edges (text wrap
must be activated)
186
Other Options in Alignment Dialog Box

Wrap text
makes text entries
wrap to multiple lines
within a cell

Shrink to Fit
automatically shrinks
the size of the text so
that it fits in one cell

Merge cells
make selected range
of cells into single
cell
187
Rotating Cell Text

Orientation
• click and drag line to
rotate text
OR
• type value or use
arrows to increment
by one degree
188
PRACTICE





Return to your sheet 2
Increase the column width of column B to be
roughly twice the size
Change the horizontal alignment to left;
center; right; fill
Increase the row height of several rows in
column B to be roughly twice the size
Change the vertical alignment to top; center;
bottom, 45-degrees
189
PRACTICE





Return to your car calculator worksheet
Add the currency format where applicable
Reflect the discount values and text in blue
and the base price in bold red
Reflect the total car cost in bold underline
Center the “car 1”, “car 2” etc.
190
Indenting Cell Contents

Can indent cell contents from left or
right margins using indent keys on
formatting toolbar
Decrease
indent
Increase
indent
191
Format Cells with Borders and Color
Can add border around a cell or range
of cells
 Can fill a cell or range of cells with a
color or pattern
 Can use combination of predefined
formats (automatic formatting) to format
range of cells or whole worksheet

192
Applying Borders
Default (automatic setting) is no borders
 The light (gray) lines you see around
the boxes are gridlines, NOT borders
 Can control whether gridlines print

193
Printing Gridlines
File  Page Setup  Sheet tab
 Click on option to print gridlines

194
Example of Printed Gridlines

Note how the gridlines are only printed for
part of the worksheet, NOT the whole page
195
Applying Borders
Instead of relying on the gridlines to
delineate cells on a printed page, can
apply borders to cells to help them
stand out
 Can apply borders using formatting bar
or dialog box

196
Applying Borders Using Formatting Bar




Select range of cells
to apply border
Click border icon on
formatting bar
Select from presets
displayed
NOTE limited
selection available
197
Applying Borders Using Dialog Box


Select range of cells to apply border
Format  Cells  Border tab
Select border style
 Choose from
presets
OR
 Click on buttons in
border area to
customize locations
 Can also specify
border color

198
Adding Colors or Patterns to Cells
Default (automatic setting) of the
worksheet background color is “no fill”
(NOTE white fill is different than no fill)
 Can change the color of cell or range of
cells and/or can fill with pattern to help
set certain cells apart
 Can use formatting bar or dialog box

199
Adding Color Using Formatting Bar





Select range of cells
to be colored
Click fill icon on
formatting bar
Select color from
available
De-select range to
see color applied
Can NOT add
pattern from here
200
Adding Color Using Dialog Box





Select range of cells
to be colored
Format  Cells 
Pattern tab
Select color from
options provided
Click “OK” to apply
De-select range to
see color applied
201
Adding Patterns Using Dialog Box







Select range of cells to
add pattern
Format  Cells 
Pattern tab
Click Pattern list box to
see choices
Select pattern
Select color
Click “OK” to apply
De-select range to see
color applied
202
PRACTICE






Print preview your car calculator worksheet (use
appropriate button on standard toolbar)
Close the print preview using the “Close” button
on the toolbar
Activate the gridlines and repeat (do you see the
difference?)
Apply box borders to all cells in the table except
the base price and value
Change each car column to be filled with a
different color
Fill the cells below the car totals with a diagonal
203
pattern
AutoFormat Feature
If you are not sure what formatting will
work well together, select from 16
predefined options
 AutoFormat applies to

•
•
•
•
•
number formats
cell alignments
column widths and row heights
fonts
borders, colors, and patterns
204
Using AutoFormat




Select range of cells
to be formatted
Format 
AutoFormat
Browse through to
see preview of
formats
Click “OK” to apply
205
AutoFormat Options


Clicking “Options”
button allows you to
choose what parts
of the format to
apply
Click off (turn off
checkmark) of those
you do NOT want
206
The Format Painter
Use to copy formats from one cell or
range to another
 Saves time having to re-do formats
 Does not affect cell values (text,
numbers, or formulas), just cell
appearances
 Can be done using standard toolbar or
menu commands

207
Format Painting from
the Standard Toolbar
Select range of cells to copy format
from
 Activate format painter by clicking
icon on toolbar
(pointer becomes cross with paint brush)
 Drag over range of cells to copy format
to

208
Format Painting from
Menu Commands





Select range of cells
to copy format from
Choose copy
Select range of cells
to copy format to
Edit  Paste
Special  Formats
Click “OK” to
complete
209
PRACTICE





Using your car calculator worksheet, copy the
table down twice including one blank row in
between each version
Apply List 1 Autoformat to the first version of
the table
Use the format painter to copy this format to
the second version
Use Past special to copy the format to the
third version
What’s the difference between these
techniques?
210
Using Conditional Formatting
Formats cell(s) depending on whether
specific conditions in cell(s) are met
 Example

• display cell with red color and border if
value below certain limit
• display cell with blue color and border if
value above certain limit
211
Applying Conditional Formatting
Select range of cells to apply conditional
formatting (NOTE these cells should
contain similar data values)
 Format  Conditional Formatting

212
Conditional Formatting
Now will be setting up a series of
conditions to evaluate cell contents and
series of formats if cell contents match
given conditions
 Options on first list box
are for

• text or data
• formulas
213
Conditional Formatting Continued

Second list box is to choose logical
operator to be used to evaluate criterion
214
Conditional Formatting Continued

Use next box(s) to specify criterion or
cell(s) where criterion is contained

May have different number of boxes
depending on selected logical operator
Remember what the little boxes are used for
215
Conditional Formatting Continued
Next, click the format button and use
the dialog box to set the formats if the
condition is met
 NOTE you can
format

• font
• border
• color and pattern
216
Conditional Formatting Final Steps

Click “Add” button to apply conditional
formatting to selected range
Up to three (3) conditions can be
specified in one conditional format
 Repeat or click “OK” to complete

217
Changing Conditional Formats
Select cell or range that have
conditional formatting to be
changed/deleted
 Format  Conditional Formatting
 Click “Format” on condition to be
changed
 Complete new formatting
 Click “OK” to complete conditional
formatting change

218
Removing Conditional Formatting
Select cell or range to remove
conditional formatting
 Format  Conditional Formatting
 Click “Delete” button
 Choose condition to be removed
 Click “OK” to complete

219
Remove Formatting
Select cell or range to remove
formatting
 Edit  Clear  Formats
 Formats are cleared from selected
range--data is NOT affected

220
PRACTICE

Apply conditional formatting to your car
calculator worksheet as follows:
• if value < $15,000 appear in black
• if value >= $15,000 appear in blue
• if value>=$20,000 appear in red


Change the conditional formatting to have
values < $15,000 appear in green
Clear the conditional formatting for car 3
221
Checking Spelling in a Worksheet

Spell Checker is program that checks
for
• misspelled works
• unusual capitalization
• repeated words
Can check whole worksheet or any
selected range
 To active Spell Check, click button on
standard toolbar

222
Spelling Dialog Box
Shows the error.
Click here to edit error.
Let’s take a
closer look at
what these
buttons do
Suggests alternative spellings.
Select choice then click
“Change” button.
223
Spelling Dialog Box Buttons






Ignore = skip this error
Ignore All = skip all identical errors
Change = change the error as selected in
the dialog box
Change All = change all identical errors to
the change made in the dialog box
Add = add identified error to the dictionary
as a correct spelling
AutoCorrect = add this misspelling to a list
to automatically be corrected as you type
224
AutoCorrect
List of words commonly misspelled
 Excel will automatically correct the
misspelling to the correct spelling
according to this list
 Example

• type “hte” and it autocorrects (after
pressing space bar to go to next word) to
“the”
225
Viewing the AutoCorrect List
• Tools menu
• AutoCorrect
• List appears

To add to the list
• type misspelling in Replace box
• type correct spelling in With box
226
REVIEW EXERCISE


Open your Mosquito worksheet
Adjust the columns widths in each table to the
following:
• Month column 9.29
• Average Temp column 9
• Absolute Temp column 11.14
• Mosquito Population column 16.29
• Use wrap text to automatically adjust text
to fit into new column widths
227
REVIEW EXERCISE





Add borders so that all values except the text
and value for wet spring multiplier are
included
Delete or insert columns such that there is
one column separating the 1997 and 1998
tables. Fill that column with a solid blue color
Apply Classic 2 Autoformat to 1997 table
Apply Classic 3 Autoformat to 1998 table
Title your worksheet “Wisconsin Mosquito
Population” and to appear centered across
the top of the page
228
Managing Workbooks









Move among Worksheets in Workbook
Rename Worksheets
Move and Copy Worksheets
Insert and Delete Worksheets
Use the Zoom Control
Set up the Page for Printing
Add Headers and Footers
Use Print Preview
Insert Manual Page Breaks
229
Move among Worksheets
Default is to open new workbook with
three worksheets
 Each worksheet is identical
 Tabs giving worksheet names appear at
bottom of screen

230
Move among Worksheets
To move to another worksheet, click tab
with worksheet name
 Selected worksheet comes to top
 NOTE when you save a workbook (an
Excel file) you save all worksheets

231
More on Moving among Worksheets

Sometimes, have more worksheets than
show on worksheet tab area

Use tab scrolling buttons to move tab
selection to see other worksheets
232
Tab Scrolling Buttons
Adjust
Adjust display
display to
to start view
view
at first
worksheet in worksheets
to left in
workbook
workbook
Adjust display Adjust display
to end view at
to view
last
worksheets to
worksheet in
right in
workbook
workbook
233
Displaying More Worksheet Tabs

Can also click on horizontal scroll bar
border and drag shorter to show more
tabs
234
Rename Worksheets

Default names for the worksheets
appear on the tabs
“Sheet1”, “Sheet2”, etc.
Subsequent worksheets inserted will
continue this number/naming sequence
 To change name

• double-click worksheet tab
• on highlighted tab, type new name
• hit “Enter” to complete
235
Other Ways to Rename Worksheets

Using menu commands
• Format  Sheet  Rename
• on highlighted tab, type new name
• hit “Enter” to complete

Using shortcut menu
• right-click on sheet name
• select “Rename” from pop up menu (left
click to select)
• on highlighted tab, type new name
• hit “Enter” to complete
236
Worksheet Names
Can be up to 31 characters long
(including spaces)
 Should be meaningful description of
contents
 Active worksheet name is shown in
boldface
 Worksheet names can also be name of
chart/graph or macro

237
PRACTICE



Rename the worksheet with these tables “WI
Skeeters”
Rename the worksheet with your car
calculator as “Car Calc”
Rename the worksheet with your minefield
exercise as “Mines”
238
Selecting Multiple Worksheets

To select multiple worksheets to move
or copy
• Hold down “Shift” key
• Click on individual worksheets
• Click on single worksheet within selection
to perform move or copy
239
Move Worksheets within Workbook

To change the order of worksheets in
workbook
• click tab name
• drag to new location
• NOTE small arrowhead shows where
worksheet will go
240
Other Ways to Move Worksheets
within Workbook
Use menu command
• Edit  Move or
Copy Sheet
• Select to move within
current workbook or
to new workbook
• Select worksheet to
be located before
• Click “OK” to
complete
241
Other Ways to Move Worksheets
within Workbook
Use shortcut menu
• Right-click sheet name
• Select “Move or Copy
Sheet” from pop-up
menu
• Complete dialog box like
previous
– Select to move within
current workbook or to
new workbook
– Select worksheet to be
located before
– Click “OK” to complete
242
Copying a Worksheet
Including Page Formatting

To make an exact duplicate of the active
worksheet (including page formatting)
• hold down CTRL key
• use arrow head to drag worksheet tab to
new location
• release CTRL key to complete copy
• rename copy as desired
243
Other Ways to Copy a Worksheet
Including Page Formatting

Using menu
command
• Edit  Move or
Copy Sheet
• Click where copied
worksheet will be
inserted
• Click to create a
copy
• Click “OK” to
complete
244
Other Ways to Copy a Worksheet
Including Page Formatting
Using menu command
• Right-click sheet name
• Select “Move or Copy
Sheet” from pop-up
menu
• Complete dialog box
like previous
– Click where copied
worksheet will be
inserted
– Click to create a copy
– Click “OK” to complete
245
PRACTICE




Move your “WI Skeeters” worksheet so it is
the last worksheet in the workbook
Copy your “WI Skeeter” worksheet to appear
as the first in your workbook.
Rename the new worksheet “MN Skeeters”
and change the appropriate cells to reflect
that the data now represents the mosquito
population in Minnesota
Change the MN 1997 wet spring multiplier to
2.65 and the 1998 to 4.07
246
Moving Between Workbooks
Frequently want to use all or part of
worksheet rather than create from
scratch
 Can move or copy worksheet with
headings, formats, and formulas to new
workbook
 Must have both workbooks open

• source workbook
• destination workbook
247
Move Between Workbooks
Use menu command
• Edit  Move or
Copy Sheet
• Select to destination
workbook from list
box or new workbook
• Select location within
chosen workbook
• Click “OK” to
complete
248
Move Between Workbooks
Use shortcut menu
• Right-click sheet name
• Select “Move or Copy
Sheet” from pop-up
menu
• Complete dialog box like
previous
– Select to destination
workbook from list box or
new workbook
– Select location within
chosen workbook
– Click “OK” to complete
249
Copy Between Workbooks

Use the two methods just discussed but
be sure to click the “Create a copy” box
250
Copy a Worksheet
Excluding Page Formatting






Select whole worksheet
Select copy command (using menu,
toolbar, or keyboard shortcut)
Move to desired worksheet for paste or
insert new
Select paste command
Cell contents, formula, and cell formatting
are copied
Worksheet page formatting is not copied
251
Moving Between Workbooks
Excel will only display active workbook
even though several may be open
 To move between open workbooks

Select from list
252
PRACTICE




Open all workbooks you have used for this
class
Move all your worksheets to your Mosquito
workbook
Arrange the sheets so that the WI and MN
Skeeters worksheets appear as the first and
second sheets in the workbook
Save you work
253
Inserting Worksheets

Use menu command
• Select worksheet you want the new
worksheet to be inserted before
• Insert  Worksheet
• Worksheet is inserted
• Rename as desired
254
Inserting Worksheets

Use shortcut menu
• Right-click worksheet you want the new worksheet
to be inserted before
• Click “Insert” from pop-up menu
• Double-click “Worksheet” icon from dialog box (or
other Excel item if desired)
• Worksheet
is inserted
255
Delete Worksheets

Deleting worksheet deletes the whole
sheet
•
•
•
•
•
cell contents
formatting
formulas
range names
etc.
256
Select Multiple Worksheets to Delete

Adjacent worksheets
• Hold down “Shift” key
• Click on worksheet tabs

Non-adjacent worksheets
• Hold down “CTRL” key
• Click on worksheet tabs
257
Delete Worksheets

Use menu command
• Select worksheet to be deleted
• Edit  Delete Sheet
• Prompted to approve delete
• Click “OK” to complete
258
Delete a Worksheet

Use shortcut menu
• Right-click on worksheet tab
• Click “Delete” from pop-up menu
• Prompted to approve delete
• Click “OK” to complete
259
PRACTICE



Insert a blank worksheet after every other
non-blank, I.e. will be WI Skeeters, blank
sheet, MN Skeeters, blank sheet, etc.
Move all the blank sheets to the end of the
workbook
Delete all the blank sheets (BE CAREFUL to
verify you are deleting blank sheets)
260
Using Zoom Command


View varied degrees of
magnification or reduction
Menu command
• View  Zoom  select
value from dialog box or
type
custom value

Toolbar
• select value from list box or
type custom value
• selection zooms on
selected cell
261
Set Up Pages for Printing
If workbook contains several
worksheets, must set up each
worksheet individually for printing
 Thus can have different page-level
formatting within same workbook
 To access dialog box for page printing
options: File  Page Setup

262
Page Setup Dialog Box

Dialog box has several different tabs to
control page-level formatting options

Let’s look at each of these tabs
263
Page Setup Dialog Box
Page Tab


Orientation
Scaling
• enlarge/reduce
overall size
• fit to number of
pages in width and
height


Select Paper size
Enter first page
number
264
Page Setup Dialog Box
Margins Tab

Enter value or click
presets to increment
by 0.1 inch to set
margins
• top and bottom
• left and right
• header and footer

Click to choose
horizontal or vertical
page centering
265
PRACTICE

Set the following page formatting for the WI
and MN Skeeters worksheets
•
•
•
•
print landscape orientation
margins (top, bottom, left, right) = 0.75
turn off gridlines
adjust each sheet to fit on a single page
266
Header and Footer
Header = text that appears at the top of
every printed page
 Footer = text that appears at the bottom
of every printed page

267
Page Setup Dialog Box
Header/Footer Tab


Select pre-selected
header or footer
from list box
Click custom button
to create your own
header or footer
268
Custom Header/Footer

Type text in each section or use buttons
to automatically insert text
• use “Enter” key to insert line break in each
section
269
Header/Footer Final Steps
Header/footer text appears now as
selection
 Commas separate items entered in
each section

270
Notes on Headers and Footers
Can NOT insert path name into
header/footer with doing so manually
 Can insert file name into header/footer-recommended to help keep track of
work
 Keep page numbers in right region to
aid visibility when printing

• You have to insert the “of” in “page # of #
of pages”
271
PRACTICE

For the WI and MN Skeeters worksheets,
insert a footer to read
•
•
•
•
“created by” your name (left region, italicized)
date (center region, underline)
page # of # of pages (right region, bold)
Second line of footer should have file name and
tab name in left region.
• Footer text should appear in 10 point Arial font
272
Page Setup Dialog Box
Sheet Tab


Set a specific area
of worksheet to print
Set up
• rows to repeat at top
of each page
• columns to repeat at
left of each page


Print options
Page printing order
273
Setting Print Area
Using Dialog Box
Print area = range of cells less than
whole worksheet that will be paginated
and printed
 From this dialog box

• click to reduce dialog box
• click and drag range to set print area
• click to get dialog box back
274
Setting or Clearing Print Area
Using Menu Command
Select range to be set as print area
 File  Print Area  Set Print Area


To modify or change area to be printed,
repeat this procedure

To clear set print areas
• File  Print Area  Clear Print Area
275
Notes on Print Areas
Can define adjacent or non-adjacent
ranges using techniques for selecting
such ranges
 Can type range in area in dialog box; do
not have to click and drag range

276
Repeating Rows/Columns
Use techniques for “hiding” dialog box
to select rows (columns) to repeat at top
(left)
 Enables you to copy column (row)
headings on subsequent pages without
making these appear within the
worksheet on each page

277
Print Preview
Gives miniature version of what
worksheets will look like when printed
 Helps to visualize page-level and other
formatting
 To activate

• File  Print Preview
OR
• click print preview button on toolbar
278
Print Preview Screen
279
Print Preview Window Buttons

Next/Previous
view next/previous page

Zoom
magnify or reduce view of page

Print
display Print dialog box

Setup
display Page Setup dialog box
280
Print Preview Window Buttons

Margins
display or hide adjusting handles to change
margins or column widths

Page Break Preview
view page break preview to adjust page breaks
on active worksheet

Close
quit print preview and return to edit worksheet

Help
active help on print preview
281
Automatic Page Breaks



Excel will
automatically
paginate worksheet
after initial print
preview
Page breaks will show up as dashed
borders separating pages on right and
bottom
Adjust to changes in column width
and/or margins
282
Insert Manual Page Breaks
Automatic page breaks readjust after
insertion of manual page break
 To insert manual page break

• Select cell in row (column) or whole row
(column) to appear at top (left) of new page
• Insert  Page Break
283
Delete Manual Page Break
Select cell below or to right of page
break intersection
 If correct cell is selected

• Insert  Remove Page Break
284
Page Break View
Can view how worksheet will appear
with page breaks
 Allows you to move manual page
breaks
 View  Page Break Preview

285
Page Break Preview Example




Note page breaks appear as heavy (blue) lines
Note remaining cells in worksheet do not appear
Click and drag lines to move page breaks
Click and drag line off page to remove page break
286
PRACTICE





Go to your Car Calc tab (Car Calculator
worksheet)
Adjust the print area to be just the titles and
first table
Insert a manual page break between each of
the tables
Repeat the title rows at the top of each page
Print preview your work
287
Printing Worksheets
To print the whole selected worksheet,
click the printer button on the toolbar or
use keyboard shortcut CTRL+P
 For more options/control

• File  Print
288
Print Dialog Box

Printer
• select printer from list box

Print range
• select to print all
• or select certain pages to
print (type value or use
presets to specify pages)

Print what
• Active sheet(s) is default
• Selection for range highlighted in workbook
• Whole workbook for all worksheets
289
Print Dialog Box Continued

Number of copies
• type value or use
preset arrows to
increment

Collate
• select to print copies
in sequential page
order
• deselect to print all
single pages in
copies (e.g. all pages
one, all pages two,
etc.)
290
PRACTICE



Adjust your page text to appear 100%
Adjust column widths so that 1997 and 1998
mosquito population tables appear on same
pages
Print your
• WI Skeeters worksheet if your last name begins
with A-M
• MN Skeeters worksheet if your last name begins
with N-Z
291
Using Functions






Understand Functions
Enter Functions into a Worksheet
Use the AutoSum Button
Use the Conditional Sum Wizard
Understand and Use the Paste Function
Use the Paste Function for a Variety of
Functions
292
Understanding Functions
Function = predefined formula that
performs calculations on the data in
worksheet(s)
 Excel provides nearly 200 built-in
functions
 Functions empower you to work more
efficiently and effectively

293
Understanding Functions
Functions are easier than formulas
because you do not have to enter the
mathematical expressions yourself
 Functions allow you to work on larger
cell ranges than are practical using
formulas
 Saves time and typing errors

294
Understanding Function Structure
Functions have names and arguments
 Function name is a way to refer to that
function and also give you an idea of
what it does
 Arguments are the elements in the
function that are used to calculate a
result

295
Example Function
Function name
=SUM(C5:C9)
Function arguments

Function may require one argument or
several
296
Enter Functions into a Worksheet

To enter a function in a cell
•
•
•
•

start with equals sign “=“
type function name
type function arguments in parentheses
hit “Enter” to complete or move to another cell
Excel will inform you if there are errors in
the function as you typed it
297
Common Mistakes in Functions
NO SPACE between function name and
argument set
 Arguments used incorrectly
 Argument list not separated by commas
 Function name typed incorrectly
 If Excel cannot calculate your function it
will enter “#NAME?” in the cell

298
How do I know
what function to
use and what its
arguments are?
299
Common Functions
Sum a series of numbers
 Sum a series of numbers if certain
criterion is met
 Other functions from list; dialog boxes
help indicate what function does and
what arguments to use

300
AutoSum
Add up a series of numbers is such
common function, Excel provides a
toolbar button for doing it
 AutoSum button activates the
SUM function

• Click AutoSum button
• Drag over range to sum
• Hit “Enter” or move to another cell to
complete
301
PRACTICE


On WI Skeeters and MN Skeeters, use
AutoSum function to compute the total
number of mosquitoes in each table
Below this value in each table use the
function “Average” to compute the average
number of mosquitoes
302
Conditional Sum Wizard

Next most used function is probably a
conditional sum
• add this range of cells if the value in
another range of cells matches a certain
condition
• Example
– add sales figures A2:A8 if the corresponding
value in B2:B8 is “Northwest”
303
Using Conditional Sum Wizard

Tools  Wizard  Conditional Sum
• Activates series of four dialog boxes to
lead you through creation of conditional
sum function
304
Conditional Sum Wizard: Step 1
Specify range that
includes data to be
evaluated and data
to be summed
305
Conditional Sum Wizard: Step 2
Specify column to be
summed and criteria
for selecting specific
values included in
the calculation
Click “Add Condition”
after specifying each
criterion
306
Conditional Sum Wizard: Step 3
Choose what kind of
formula to display
 only the sum (first
choice)
 to easily change the
criteria, choose
“Copy the formula
and conditional
values” (second
choice)
307
Conditional Sum Wizard: Step 4+
Indicate where you
want the result
displayed
NOTE if you chose the
second option in
step 3, you will have
to specify a location
for each criterion
value and the
formula result
308
Use Paste Function



To access the other
Excel functions, use
the Paste Function
button on the toolbar
Activates dialog box
with functions listed
by group and by
name
Also give brief
description of function
calculation
309
Use Paste Function


Selecting a function category limits displayed
functions only to those within that category
Select “All” for a complete listing
310
Paste Function Dialog Box
Once you select a function, you will get
a dialog box to assist you in defining the
arguments used to calculate the
function result
 Some arguments required (appear in
bold)
 Other arguments optional (appear in
normal text)

311
Example of Paste Function Dialog Box
Argument type
Required
Optional
Description of
function
Description
of argument
312
Argument Types
Value--number or cell reference
containing value
 Logical--result is TRUE or FALSE
 Num--a number or numerical formula
 Text--non-numeric data; text must be
enclosed in quotation marks (“”)

313
Argument Types
Array--a range of values treated as a
single group
 Serial number--a date and time
 Reference--a cell or range address

314
I still don’t get all
these functions.
How do I know
which one to use
and how to use
it?
315
Knowing Which Function to Use

Depends on what you want to do
• Excel cannot do everything you may think of
Might be good idea to spend time looking
at each function description
 Use Help to see if there is a function to
do what you want and see examples
Let’s take a look at some examples of the
more common of functions and their uses

316
Functions and More Functions

We will take a brief
look at each
function category
• get idea of what
each category is
about
• see some examples
of functions from
each category and
what they do
317
Financial Functions

Calculate
•
•
•
•
•
•
payments on a loan
depreciation
present and future values
internal rate of returns
net present value
annuity calculations--calculations based on
series of even payments over specified
time
318
Examples of Financial Functions

ACCRINT(issue,first_interest,settlement,
coupon,par,frequency,basis)
• accrued interest for a security that pays
periodic interest

ACCRINTM(issue,settlement,rate,par,
basis)
• accrued interest for a security that pays
interest at maturity
319
Examples of Financial Functions

CUMPRINC(rate,nper,pv,start_period,
end_period,type)
• cumulative principal paid on a loan
between start_date and end_date

DISC(settlement,maturity,pr,redemption,
basis)
• discount rate for a security
NOTE: no spaces between commas
and arguments
320
Examples of Financial Functions

EFFECT(nominal_rate,nper)
• effective annual interest rate

FV(rate,nper,pmt,pv,type)
• future value of an investment

FVSCHEDULE(principal,schedule)
• future value of an initial principal after
applying a series of compound interest
rates
321
Examples of Financial Functions

IRR(values,guess)
• internal rate of return for a list of values

NPV(rate, value1,value2,…)
• net present value for a series of values

PV(rate,nper,pmt,fv,type)
• present value of an investment

SLN(cost_salvage,life)
• straight-line method of depreciation
322
Date and Time Functions

To keep track of date and time, Excel
counts the number of days since
beginning of 20th century
• Excel uses date serial number that starts
with Jan 1, 1900 as day 1
• Examples
– day 25 = 1/25/1900
– day 366 = 1/1/1901
– day 36214 = 2/23/99
323
Examples Date and Time Functions

DATE(year,month,day)
• returns specific date (set number format to
“Number” to see serial number value)

DATEVALUE(date_text)
• computes serial number of text (remember,
text must be in quotes”)

DAY(serial_number)
• returns day, an integer between 1 and 31
depending on serial number
capitalization does NOT matter
324
Examples Date and Time Functions

DAYS360(start_date,end_date)
• returns number of days between two dates

HOUR(serial_number)
• returns hour, an integer between 0 and 23,
corresponding to serial number

TIME(hour,minute,second)
• returns serial number to time specified by
hour, minute, second
325
Examples Date and Time Functions

NOW()
• returns serial number of current date and time
(set cell number format to “Number” to see serial
number instead of date value)

NETWORKDAYS
• returns number of (start_date,end_date,
holidays) whole working dates between
start_date and end_date; working days exclude
weekends and days identified as holidays

TODAY()
• returns serial number of current date
326
Examples Date and Time Functions

YEAR(serial_number)
• returns year corresponding to serial
number
Whether you see a date format or
number format depends on the
formatting of the cell!
327
Math and Trig Functions

Math functions
• perform standard arithmetic operations
• round and truncate numbers

Trigonometric functions
• build complex scientific and engineering
formulas
• matrix calculations
• solving complex problems that involve
several unknown variables in array
328
Examples Math Functions

ABS(number)
• returns absolute value of number

INT(number)
• number rounded to nearest integer

RAND()
• random number between 0 and 1

ROUND(number,num_digits)
• rounds number to specified number of
digits
329
Examples of Math Functions

SQRT(number)
• square root of number

SUM(number1,number2,…)
• total of arguments

TRUNC(number,num_digits)
• changes number to integer by truncating
the decimal portion
330
Examples of Trig Functions

COS(number)
• returns cosine of an angle

TANH(number)
• hyperbolic tangent of number

ASIN(number)
• returns arcsine of a number in radians, -/2
to /2
331
Statistical Functions

Used to find
•
•
•
•

average
maximum and minimum
standard deviation
variance of group
Standard statistical tests available
• T-test
• Chi-Squared
• F-test
332
Examples of Statistical Functions

AVERAGE(number1,number2,…)
• returns average of defined range

COUNT(value1,value2,…)
• returns total of nonblank cells in range

MAX(number1,number2,…)
• returns largest number in defined range

MEDIAN(number1,number2,…)
• returns middle value in defined range
333
Examples of Statistical Functions

MODE(number1,number2,…)
• returns the most frequently occurring value
in range of data

STDEV(number1,number2,…)
• returns standard deviation for a sample

VAR(number1,number2,…)
• returns variance of a sample
334
Lookup and Reference Functions

Used to retrieve a value or cell
reference from a table or an array
• Array = rectangular range of value or
formulas treated as one group
335
Examples of
Lookup & Reference Functions

HLOOKUP(lookup_value,table_array,
row_index_num)
• searches across the top row of range until
value is met

LOOKUP(lookup_value,array)
• value in array selected by lookup value

VLOOKUP(lookup_value,table_array,
col_index_num)
• searches down the first column of range
until value is found
336
Text Functions
Build-in functions to help find and edit
text
 LOWER(text)

• changes all characters to lowercase

PROPER(text)
• changes text to lowercase with first
character capitalized

LEN(text)
• returns number of characters in text string
337
Logical Functions

Used for testing conditions and making
decisions
•
•
•
•
test condition
returns TRUE if condition is matched
returns FALSE if condition is NOT matched
can combine functions to test multiple
conditions
338
Example of Logical Functions

AND(logical1,logical2,…)
• returns true if every argument is TRUE

IF(logical_test,value_if_true,value_if_false)
• returns value_if_true if logical test is TRUE,
otherwise returns value_if_false

NOT(logical)
• reverses TRUE and FALSE logicals

OR(logical1,logical2…)
• returns true if any argument is TRUE
339
Informational Functions
Use to analyze cells, columns, rows,
and ranges
 IS function to test type of entry in cell or
range

• returns value of TRUE or FALSE
• Example
– ISBLANK returns TRUE if cell is blank, FALSE
otherwise
– ISTEXT returns TRUE if cell value is text,
FALSE otherwise
340
PRACTICE





Use functions and formulas to compute the
following. Show these values under each
table.
# of Months Average Temp Above 75
# of Months Mosquito Pop Above 1 million
Largest Population of Mosquitoes
% of Mosquito Pop during Summer (JunAug)
341
PRACTICE



The mosquito population reaches swarm
proportions if the average monthly
temperature is 75 degrees or more.
Insert a column in each table the prints the
word “swarm” if appropriate. (Cell should be
blank if criterion does not meet swarm
condition.)
Use a conditional sum to total the mosquito
population in months where there was a
swarm and in those where there was not
342
What Next?
Suggested Progression of ACE Computer Classes
Intro to Computers
103-428
Windows
103-439
WORDPROCESSING
SPREADSHEETS
PRESENTATIONS
DATA BASE
INTERNET
MS PowerPoint
103-443
MS Access Intro
103-442
Internet Intro
103-446
MS Access Interm
103-442
Web Page Design
103-446
MS Word Intro
103-437
WordPerfect Intro
103-436
MS Excel Intro
103-433
MS Word Interm
103-437
WordPerfect Interm
103-436
MS Excel Interm
103-433
343
MS Excel Intermediate
Course Plan
Review of Basics
 Creating Charts
 Managing Data and Macros
 Linking, Summarizing, and
Consolidating Worksheets
 Hyperlinks

344

similar documents