IBM BASICS - Bio-Link
Download
Report
Transcript IBM BASICS - Bio-Link
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