Transcript Excel 2007® Business and Personal Finances How can Excel 2007 help you plan a project and chart its progress?

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
View This Presentation to Answer
the Following Questions:
• How can I sort and filter data?
• How can I tell Excel what mathematical
operation to perform?
• What are relative and absolute references?
• How can I create, modify, and position charts?
• What is the PMT function?
• What vocabulary words should I review?
2
Lesson 4: Analyze Data
To sort and filter data, use the Sort A to Z button, 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
Some
worksheets
contain so
much data that
is difficult to
understand the
data or chart
the progress of
a project.
It is easier to manage
your time and stick to a
plan for a project if you
chart your progress.
Being able to filter data so that
you can see only the
information you need is helpful
to see that you are getting
closer to you goal.
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
You can edit the
formula in the
formula bar.
You can also edit a formula by doubleclicking the cell that contains the formula.
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 does not change when
you copy the formula to a new location.
An absolute
reference is a
locked cell
that maintains
a constant
reference to
the original
cell when
copied and
pasted.
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
Academic Skills Check
Why is it important to check your
progress toward a goal?
Answers may include:
It is easier to stick manage a project
and stick to a plan if you chart your
progress so that you can see that
you are getting closer to your goal.
2
Lesson 4: Analyze Data
Tech Check
How do you sort numbers in increasing
order in Excel?
Answer:
Use the Sort A to Z button to sort
numbers in increasing order in Excel.
2
Lesson 4: Analyze Data
Tech Check
How can you create formulas in Excel?
Answer:
To create formulas in Excel, key
information into the cell or formula
bar, or choose a preset formula from a
list of functions.
2
Lesson 4: Analyze Data
A chart is a graphic that organizes data visually.
Using a
graphic such
as a chart or
graph allows
you to
organize data
so it can be
evaluated at
a glance.
An upward line on a graph shows that sales
are increasing, while a downward line
shows they are declining.
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
by changing its chart type to a line, bar, or area chart.
2
Lesson 4: Analyze Data
Use a bar
chart to
compare
items to
each other.
2
Lesson 4: Analyze Data
Use a line chart to help you compare sales over time.
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
Formulas in
Excel can help
you to
incorporate
items like
monthly
payments and
interest rates
into a budget.
To create an effective budget, you need to know both your
income, or how much you can spend, and expenses. Be sure
to consider taxes, interest rates, and other expenses.
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
When the
results from
the PMT
function are
red and in
parentheses,
they
represent
money owed
on the loan.
In finance, negative amounts are usually
represented in parentheses, and in red.
2
Lesson 4: Analyze Data
Academic Skills Check
What do you need to know to
create an effective budget?
Answers may include:
You need to know both income and
expenses to create an effective
budget.
2
Lesson 4: Analyze Data
Academic Skills Check
Why is it sometimes better to present
data as a graphic than as numbers or
words?
Answers may include:
Using a graphic such as a chart or
graph allows you to organize data so
that it can be evaluated at a glance,
making it easier to understand.
2
Lesson 4: Analyze Data
Academic Skills Check
How are negative numbers usually
represented in finance?
Answer:
In finance, negative amounts are
usually represented in parentheses,
and in red.
2
Lesson 4: Analyze Data
Tech Check
What are some of the more common
types of charts?
Answer:
Common types of charts include bar
charts, column charts, line charts,
and pie charts.
2
Lesson 4: Analyze Data
Tech Check
What are some of the ways you
can use to modify charts?
Answer:
You can use Chart Tools to change
the way the chart looks or you can
change the chart type, for example,
from a bar chart to a line chart.
2
Lesson 4: Analyze Data
Vocabulary Review
chart
A graphic that organizes data
visually so that you can compare
different kinds of data or evaluate
how data changes over time.
2
Lesson 4: Analyze Data
Vocabulary Review
range
A group of cells.
2
Lesson 4: Analyze Data
Vocabulary Review
sort
To put a list of data in ascending or
descending order.
2
Lesson 4: Analyze Data
Vocabulary Review
filter
A feature that allows you to use
only the data you need.
2
Lesson 4: Analyze Data
Vocabulary Review
relative reference
A cell reference that changes when a
formula is copied to a new location.
2
Lesson 4: Analyze Data
Vocabulary Review
absolute reference
A cell reference that doesn’t change
when a formula is copied to a new
location.
2
Lesson 4: Analyze Data
Vocabulary Review
function
A preset formula that is used to
solve an equation.
2
Lesson 4: Analyze Data
Vocabulary Review
PMT
A function that calculates payments
for a loan based on interest rate,
number of payments, and the
amount of the loan.