Transcript Lesson 4: Analyze Data

2
Excel 2007® Business and
Personal Finances
How can Excel 2007 help you plan a
project and chart its progress?
2
Lesson 4: Analyze Data
The formulas, charts,
and graphs in Excel
2007 will help you
analyze, develop,
and display the data
needed for your
project without
having to make
manual calculations.
2
Lesson 4: Analyze Data
Lesson 4 Objectives:
 Filter and sort data
 Write, edit, and use formulas and functions
 Use absolute, relative, and mixed references
 Create, modify, and position diagrams
 Create, modify, and position charts
2
Lesson 4: Analyze Data
To sort and filter data, use the Sort A to Z buttons,
located on the Ribbon.
The Sort A
to Z button
arranges
numbers in
increasing
order or
alphabetizes
words.
To sort in decreasing numerical order or in
reverse alphabetical order, use Sort Z to A.
2
Lesson 4: Analyze Data
To see only the
data you want to
see, use the
AutoFilter
command to filter
the worksheet.
To use AutoFilter,
you must first click
on a cell in the
range, or group of
cells, that you
want to filter.
2
Lesson 4: Analyze Data
You can tell Excel which numbers to use and what
mathematical operation to perform by creating a formula.
Every formula begins
with an equal sign (=)
and includes values or
cell references.
2
Lesson 4: Analyze Data
To save time,
create a formula
by choosing a
function, or
preset formula,
from a list.
After you have chosen the function you want to
use, fill in the correct numbers or cell references.
2
Lesson 4: Analyze Data
To copy a
formula from cell
to cell, use the
Copy and Paste
commands
Excel will automatically modify the cell references in
the new formula so it calculates correctly.
2
Lesson 4: Analyze Data
A relative reference is a cell reference that changes
when a formula is copied into a new location.
A relative reference
shows how a formula is
relative to the data in a
particular cell.
If the formula moves,
the reference moves
with it.
A relative reference is
written with the
column letter and row
number, such as B2.
2
Lesson 4: Analyze Data
An absolute reference is a locked cell that maintains a constant
reference to the original cell when copied and pasted.
An absolute
reference does not
change when you
copy the formula
to a new location.
2
Lesson 4: Analyze Data
Use an absolute
reference when
more than one
formula should
refer to the same
cell.
To write an absolute
reference, place a dollar sign
($) in front of both the column
letter and the row number.
The dollar signs lock the
formula to the cell $E$12.
2
Lesson 4: Analyze Data
The Charts group allows you to create
charts based on the data that you select.
Common types of charts include
bar charts, column charts, line
charts, and pie charts.
2
Lesson 4: Analyze Data
Use Chart
Tools to make
the data easier
for others to
read or to
make your
point.
You can change the look of the chart to highlight
sales trends, salary increases, or the decrease of
a loan balance as payments are made.
2
Lesson 4: Analyze Data
Another way
to modify a
chart is to
change the
chart type.
Give a clustered column chart a completely different look
Use
a bar
chart to
compare
items tosales
eachover
other.
Use
a line
help
compare
time.
by changing
itschart
charttotype
toyou
a line,
bar, or area
chart.
2
Lesson 4: Analyze Data
To avoid cluttering
a worksheet with
numbers and
charts, put the data
in one sheet and
move the
accompanying
charts to another
sheet in the same
workbook.
2
Lesson 4: Analyze Data
The COUNT function
is used to find the
number of cells in a
range that contain
numbers.
The COUNTA function
is used to find the
number of cells in a
range that contain
ANY kind o fdata.
2
Lesson 4: Analyze Data
The PMT function
calculates the
monthly payment
for a loan using the
amount of the loan,
the interest rate,
and the number of
payments.
The PMT function is very useful if
you want to create a budget.
2
Lesson 4: Analyze Data
The Function Arguments dialog box will help you
calculate your monthly loan payments with PMT.
Rate is the interest rate.
Nper is total number
of payments.
Pv is present value.
2
Lesson 4: Analyze Data
The IF function allows
a worksheet to
compare data.
Condition
IF function compares
data to see if a
Condition, or rule,
that you create is
true. One value is
returned if the
condition is true and
another is returned if
it is false.
Value if True
Value if False