Transcript Practical Literacy 2e Ch. 12.ppt

Chapter 12
Creating a Worksheet
1
Revision
• 1- What is the difference between
application software and system software.
Give examples
• 2- what is the diffrence between the Word
2003 and word 2007>
• 3- what is the file extension?
• 4- What is the program interface?
• 5- where is the task bar?
What’s inside and on the CD?
• In this chapter you will learn the essentials of creating a
Microsoft Excel worksheet
• Excel provides a set of tools for simple or complex
calculations such as:
– Creating a budget
– Estimating expenses
– Creating an income and expense projection
Practical Computer Literacy, 2nd edition
Chapter 12
3
What’s in the Excel window?
• To start Excel:
– Click Start
– Point to All Programs
– Click Microsoft Office
– Click Microsoft Office Excel 2007
• Ribbon is similar to Microsoft Word
• This chapter points out important features in Excel that
differ from features in Word
Practical Computer Literacy, 2nd edition
Chapter 12
4
What’s in the Excel window?
Practical Computer Literacy, 2nd edition
Chapter 12
5
What’s in the Excel window?
• A worksheet is grid of columns and rows
– Columns labeled with letters
– Rows labeled with numbers
• Excel worksheets saved in workbook
• Workbook contains one or more worksheets represented
by tab at bottom of Excel window
Practical Computer Literacy, 2nd edition
Chapter 12
6
What’s in the Excel window?
• When workbooks saved or opened, all work sheets
saved or opened
• Click sheet tab to go to that sheet
• Right-click tab to rename, insert, or delete sheet
Practical Computer Literacy, 2nd edition
Chapter 12
7
What’s in the Excel window?
• A worksheet cell or cell is rectangle formed by
intersection of column and row
• Each cell has unique name consisting of column letter
followed by row number
• For example, cell B3 is located in second column of third
row
Practical Computer Literacy, 2nd edition
Chapter 12
8
What’s in the Excel window?
• The active cell, marked by black outline, is one you can
currently edit or modify
• Click any cell to make it active, or use arrow keys
• A range is series of cells, e.g., D3:D6 is range of all cells
from D3 through D6
– Click and drag to select range of cells
Practical Computer Literacy, 2nd edition
Chapter 12
9
How do I enter labels?
• A label is any text entered into cell:
– worksheet title
– describe numbers in other cells
– text data such as names of people or cities
• Any “number” not used in calculation must be entered
as label
• If label is too long to fit in current cell, it will extend
into cells to right if they are empty
Practical Computer Literacy, 2nd edition
Chapter 12
10
How do I enter labels?
Practical Computer Literacy, 2nd edition
Chapter 12
11
How do I enter labels?
• To make label wrap and display in two or more lines
inside same cell:
– From the Home tab, click the Wrap Text button in the
Alignment group
Practical Computer Literacy, 2nd edition
Chapter 12
12
How do I enter labels?
• Edit label by clicking cell, then click in Formula bar
• Use arrow keys to move insertion point and backspace
and delete keys to edit text
• Press Enter key or  Enter button on formula bar to
accept change
• Press  Cancel button to exit without keeping changes
• Edit label inside cell by double-clicking cell then edit with
arrows, Backspace and Delete keys
Practical Computer Literacy, 2nd edition
Chapter 12
13
How do I enter values?
• A value is a number entered into cell and used in
calculations
• Values can be used in formulas to calculate results
• Type minus sign (-) before number to enter a negative
value
Practical Computer Literacy, 2nd edition
Chapter 12
14
How do I enter values?
Practical Computer Literacy, 2nd edition
Chapter 12
15
How do I enter values?
• A value can be edited just as you would edit a label – in
the cell or in the Formula bar
• Excel makes assumptions about the values as you enter
them
• Type an apostrophe (‘) before a number to specify it as a
label
Practical Computer Literacy, 2nd edition
Chapter 12
16
How do I enter values?
• Use the Fill handle to automatically enter values with
technique called drag-and-fill
• Point to the bottom-right corner of a cell. When the
pointer changes to a black cross shape, drag the pointer
across or down other cells
• Displays the Auto Fill Options button
– Fill with series of numbers
– Fill with value of initial cell with or without formatting
– Fill with cell formatting without a value
Practical Computer Literacy, 2nd edition
Chapter 12
17
How do I enter formulas?
• A formula specifies how to add, subtract, multiply,
divide, or otherwise calculate values in cells
• Formulas always begin with an equal (=) sign and can
use cell references that point to other cells
• A cell reference is the column and row location of a cell
Practical Computer Literacy, 2nd edition
Chapter 12
18
How do I enter formulas?
• For example, the formula =C2-C3 subtracts the contents
of cell C3 from the contents of cell C2
• Common arithmetic operators are:
- (subtraction)
/ (division)
+ (addition)
% (percent)
* (multiplication)
^ (exponent)
Practical Computer Literacy, 2nd edition
Chapter 12
19
How do I enter formulas?
• To enter a formula:
– Click the cell where you want the result of the formula
to appear. Type the equal sign (=).
– Click the first cell referenced in the formula. A
marquee, which is a rectangle of dashes, will
surround the cell just clicked.
– Type an arithmetic operator (+,-,*,/) then click the next
cell referenced in the formula.
– Press Enter to end the formula.
Practical Computer Literacy, 2nd edition
Chapter 12
20
How do I enter formulas?
• A formula can also be typed directly into the cell
• Edit a formula in the cell or in the Formula bar the same
as labels and values
Practical Computer Literacy, 2nd edition
Chapter 12
21
How do I enter formulas?
Practical Computer Literacy, 2nd edition
Chapter 12
22
How do I create complex formulas?
• Complex statistical, financial, and mathematical formulas
can be created in Excel.
• Use parentheses to make sure the operations in a
formula are executed in the correct order.
• Without parentheses, Excel will calculate using the order
– multiplication and division, then addition and
subtraction.
Practical Computer Literacy, 2nd edition
Chapter 12
23
How do I create complex formulas?
• By using parentheses you can specify the order of
calculation.
• For example, in the formula, =B3+C3*1.2, Excel will
multiply cell C3 by 1.2 then add that to the value in cell
B3.
Practical Computer Literacy, 2nd edition
Chapter 12
24
How do I create complex formulas?
• In the formula, =(B3+C3)*1.2, Excel will add cell B3 to
cell C3 then multiply the result by 1.2
• A formula can include values, cell references, or both.
• Copying or moving formulas can lead to unexpected
results
Practical Computer Literacy, 2nd edition
Chapter 12
25
How do I create complex formulas?
Practical Computer Literacy, 2nd edition
Chapter 12
26
How do I use functions?
• Excel includes many predefined formulas, called
functions
• Excel includes these examples:
– Financial functions – payments and net value
– Mathematical and trigonometric function – absolute
value and arctangent
– Statistical functions – average and normal distribution
• You can use the Insert Function button to select a
function from a list. There are more than 250 from which
to choose
• Clicking on a function in the list will display a tip about
the function’s use
Practical Computer Literacy, 2nd edition
Chapter 12
27
How do I use functions?
Practical Computer Literacy, 2nd edition
Chapter 12
28
How do I use functions?
• The Payment or PMT function calculates the payments
for a loan
• The PMT function will calculate loan payments for a car
or a house, among others
• Functions can include multiple functions
Practical Computer Literacy, 2nd edition
Chapter 12
29
How do I use functions?
• Arguments are values or cell references used in the
function for calculations.
• For example, the Average function’s argument is a
series of numbers or cell references used in the
calculation.
• The result is the average of the values.
Practical Computer Literacy, 2nd edition
Chapter 12
30
How do I use functions?
• To select a range of cells for your argument, click and
drag to select the range.
• Some functions need more than one argument and they
may be required or optional.
• The PMT function, for example, requires three
arguments and two optional arguments.
Practical Computer Literacy, 2nd edition
Chapter 12
31
How do I use functions?
• It can be difficult to enter arguments for a function.
• If you need help, click Help on this function link.
• When using a new function check results with calculator.
Practical Computer Literacy, 2nd edition
Chapter 12
32
How do I use functions?
Practical Computer Literacy, 2nd edition
Chapter 12
33
How do I use the AutoSum button?
• The AutoSum button is used to quickly calculate the total
of a column or row of cells.
• Excel examines cells to the left and above current cell to
determine inclusion.
• The AutoSum button can be tricked by a blank cell or a
cell containing a label. It is always wise to double-check
the range.
Practical Computer Literacy, 2nd edition
Chapter 12
34
How do I use the AutoSum button?
• Be careful using the AutoSum button to calculate a
range in a column with a numeric heading
• Watch the marquee to verify the range
• If the AutoSum button does not automatically select the
correct cells, create the Sum function manually
Practical Computer Literacy, 2nd edition
Chapter 12
35
How do I use the AutoSum button?
Practical Computer Literacy, 2nd edition
Chapter 12
36