Spreadsheets

• A spreadsheet is a document that stores data in a

grid of horizontal rows and vertical columns.
 Spreadsheet a computer program that calculates
numbers and organizes information in columns and
rows.
 A spreadsheet is a grid of rows and columns in
which you enter text, numbers, and the results of
calculations.
• GRID - a network of lines that cross each other to
form a series of squares or rectangles.
• MS Excel is a computer program used to create
electronic spreadsheets.
• Within Excel users can organize data, create charts
and perform calculations.
• In Excel, a computerized spreadsheet is called a
worksheet.
• The file used to store worksheets is called a
workbook.
Microsoft Excel
Window
Microsoft Excel is an electronic
spreadsheet.
You can use it to organize your data
into rows and columns.
You can also use it to perform
mathematical calculations quickly.
To begin this lesson, start Microsoft
Excel 2007/2010.
Microsoft Excel window appears
and your screen looks similar to the
one shown below.
MS Excel Window
Worksheet
 When you start Excel, you’re faced with a big
empty grid made up of columns, rows and
cells.
 Microsoft Excel consists of worksheets.
 Each worksheet contains columns and rows.
 Columns are lettered A to Z and then
continuing with AA, AB, AC and so on; the
rows are numbered 1 to1,048,576.
 The combination of a column coordinate and
a row coordinate make up a cell address.
 E.g. the cell located in the upper-left corner
of the worksheet is cell A1, meaning column
A, row 1. Cell E10 is located under column E
on row 10.
 You enter your data into the cells on the
worksheet.
Worksheet
 When you start Excel, you open a file that is
called a workbook.

 Eachworkbook comes with three

worksheets into which you enter data.

 Thefirst workbook you’ve opened is called

Book1.

 Title
bar indicates the name of the current
workbook and the program name.

 Sheettabs appear at the bottom of the

window.

 It’sa good idea to rename sheet tabs to make

information on each sheet easier to identify.
Worksheets are divided into columns,
rows, and cells.
Columns go from top to bottom on the
worksheet, vertically.
Each column has an alphabetical
heading at the top.
Rows go across the worksheet,
horizontally.
Each row also has a heading. Row
headings are numbers, from 1 through
1,048,576.
Formula Bar

Formula Bar displays the value of the

formula contained in the active cell.
Formula Bar also permits entry or editing
of values or formulas.
The cell address of the cell you are in,
displays in the Name Box which is located
on the left side of the Formula bar.
Cell entries display on the Formula bar.
If you do not see the Formula bar in your
window, perform the following steps:
◦ Choose the View tab.
◦ Click Formula Bar in the Show/Hide group. The
Formula bar appears.
Name Box
Displays the name of the selected
cell, table, chart or object
You can also use Name Box to go to
a specific cell.
Just type the cell address you want
to go to in the Name Box and then
press Enter.
Type B10 in the Name box.
Press Enter. Excel moves to cell B10
Status bar
 Status bar appears at the very bottom
of the Excel window .
 It provides information such as the sum,
average, zoom, minimum, and
maximum value of selected numbers.
 You can change what displays on the
Status bar by right-clicking on the
Status bar and selecting the options
you want from the Customize Status
Bar menu.
 You click a menu item to select it.
 You click it again to deselect it.
 A check mark next to an item means
the item is selected.
Move Around a
Worksheet
 By using the Arrow keys, you can move around
your worksheet.
 You can use the Down Arrow key to move
downward one cell at a time.
 You can use the Up Arrow key to move upward
one cell at a time.
 You can use the Tab key to move across the page
to the right, one cell at a time.
 You can hold down the Shift key and then
press the Tab key to move to the left, one cell
at a time.
 You can use the right and left arrow keys to
move right or left one cell at a time.
 The Page Up and Page Down keys move up
and down one page at a time.
 If you hold down the Ctrl key and then press the
Home key, you move to the beginning of the
worksheet.
Go To Cells Quickly
 Two keyboard shortcuts for moving quickly from
one cell in a worksheet to a cell in a different part of
the worksheet.
1. Go to -- F5
 The F5 function key is the "Go To" key. If you press
F5 key, you are prompted for the cell to which you
wish to go.
 Enter the cell address, and the cursor jumps to that
cell.
 Press F5. The Go To dialog box opens.
 Type J3 in the Reference field.
 Press Enter. Excel moves to cell J3.
2. Go to -- Ctrl+G
 You can also use Ctrl+G to go to a specific cell.
 Hold down the Ctrl key while you press "g" (Ctrl+g).
 The Go To dialog box opens.
 Type C4 in the Reference field.
 Press Enter. Excel moves to cell C4.
Selecting cells
If you want to perform a function on a
group of cells, you must first select those
cells by highlighting them.
The exercises that follow teach you how
to select.
To select cells A1 to E7:
Go to cell A1.
Press the F8 key. This anchors the cursor.
Note that "Extend Selection" appears on
the Status bar. You are in Extend mode.
Click in cell E7. Excel highlights cells A1
to E7.
Press Esc and click anywhere on the
worksheet to clear the highlighting.
Alternative Method: Select Cells by Dragging
You can also select an area by holding down the left mouse button
and dragging the mouse over the area.
You can select noncontiguous areas of the worksheet by doing the
following:
Go to cell A1.
Hold down the Ctrl key. You won't release it until step 9. Holding
down the Ctrl key enables you to select noncontiguous areas of the
worksheet.
Press the left mouse button.
While holding down the left mouse button, use the mouse to move
from cell A1 to C5.
Continue to hold down the Ctrl key, but release the left mouse
button.
Using the mouse, place the cursor in cell D7.
Press the left mouse button.
While holding down the left mouse button, move to cell F10.
Release the left mouse button.
Release the Ctrl key. Cells A1 to C5 and cells D7 to F10 are
selected.
Press Esc and click anywhere on the worksheet to remove the
highlighting.
You can use Excel to enter all
sorts of data, professional or
personal.

• You can enter two basic kinds of

data into worksheet cells:
numbers and text.

• You can use Excel to create

budgets, record student grades
or attendance, or list the
products you sell.
Enter Data
First, place the cursor in the cell in which
you want to start entering data.
Type some data, and then press Enter.
If you need to delete, press the Backspace
key to delete one character at a time.
Enter Data
1. Place the cursor in cell A1.
2. Type John Jordan. Do not press Enter at
this time.

kk
Delete Data
Backspace key erases one
character at a time.
1. Press the Backspace key until
Jordan is erased.
2. Press Enter. The name “John"
appears in cell A1.

nn
Edit a Cell
After you enter data into a cell, you can edit
data by pressing F2 while you are in the cell you
wish to edit.
Change "John" to "Jones."
◦ 1. Move to cell A1.
◦ 2. Press F2.
◦ 3. Use the Backspace key to delete the "n" and the
"h."
◦ 4. Type nes.
◦ 5. Press Enter.

◦ ll
Alternate Method: Editing a Cell
by Using the Formula Bar
You can also edit the cell by using
the Formula bar. You change "Jones"
to "Joker" in the following exercise.
◦ 1. Move the cursor to cell A1.
◦ 2. Click in the formula area of the
Formula bar.

◦ nn
Cont…
3. Use the backspace key to
erase the "s," "e," and "n."
4. Type ker.
5. Press Enter.
EXERCISE: Delete a cell entry
To delete an entry in a cell or a
group of cells, you place the cursor
in the cell or select the group of
cells and press Delete.
Select cells A1 to A2.
Press the Delete key.
Alternate Method: Edit a Cell by
Double-Clicking in the Cell
You can change "Joker" to
"Johnson" as follows:
1. Move to cell A1.
2. Double-click in cell A1.
3. Press the End key. Your cursor is
now at the end of your text.
4. Use the Backspace key to erase
"r," "e," and "k."
5. Type hnson.
Press Enter.
Change cell entry
 Typing in a cell replaces the old cell entry
with the new information you type.
 Move the cursor to cell A1.
 Type Cathy.
 Press Enter. The name “Cathy" replaces
“Johnson."
TEXT WRAPING
 When you type text that is too long to fit
in the cell, the text overlaps the next cell.
 If you do not want it to overlap the next
cell, you can wrap the text.
 Move to cell A2.
 Type Text too long to fit.
 Press Enter.
Cont…
Return to cell A2.
Choose the Home tab.
Click Wrap Text button on Home Tab,
Alignment group . Excel wraps the text
in the cell.
Saving Excel document
To save your file:
Click on File Tab. A menu appears.
Click Save. The Save As dialog box
appears.
Go to the directory in which you
want to save your file.
Type Lesson1 in the File Name field.
Click Save. Excel saves your file.
Close Excel
Close Microsoft Excel.
Click File Tab. A menu appears.
Click Close. Excel closes
Entering Excel Formulas and
Formatting Data
A major strength of Excel is that
you can perform mathematical
calculations and format your data.
In this lesson, you learn how to
perform basic mathematical
calculations and how to format
text and numerical data.
Perform Mathematical
Calculations
 In Microsoft Excel, you can enter numbers
and mathematical formulas into cells.
 Use cell references when performing
mathematical calculations such as
addition, subtraction, multiplication, or
division.
 When entering a mathematical formula,
start the formula with an equal sign.
 A formulae is a user defined mathematical
expression which returns a value in a cell.
 Use the following to indicate the type of
calculation you wish to perform:
◦ + Addition
◦ - Subtraction
◦ * Multiplication
◦ / Division
Using formulae
Addition
◦ 1. Type Add in cell A1.
◦ 2. Press Enter. Excel moves down one cell.
◦ 3. Type 1 in cell A2.
◦ 4. Press Enter. Excel moves down one cell.
◦ 5. Type 1 in cell A3.
◦ 6. Press Enter. Excel moves down one cell.
◦ 7. Type =A2+A3 in cell A4.
◦ 8. Click the check mark on the Formula bar.
Excel adds cell
◦ A1 to cell A2 and displays the result in cell A4.
The formula displays on the Formula bar.
Note: Clicking the check mark on the
Formula bar is similar to pressing Enter.
Excel records your entry but does not move
to the next cell.
Subtraction
◦ 1. Press F5. The Go To dialog box appears.
◦ 2. Type B1 in the Reference field.
◦ 3. Press Enter. Excel moves to cell B1.
◦ 4. Type Subtract.
◦ 5. Press Enter. Excel moves down one cell.
◦ 6. Type 6 in cell B2.
◦ 7. Press Enter. Excel moves down one cell.
◦ 8. Type 3 in cell B3.
◦ 9. Press Enter. Excel moves down one cell.
◦ 10. Type =B2-B3 in cell B4.
◦ 11. Click the check mark on the Formula
bar. Excel subtracts cell B3 from cell B2
and the result displays in cell B4. The
formula displays on the Formula bar.
Multiplication
◦ 1. Hold down the Ctrl key while you press
"g" (Ctrl+g). The Go To dialog box appears.
◦ 2. Type C1 in the Reference field.
◦ 3. Press Enter. Excel moves to cell C1
◦ 4. Type Multiply.
◦ 5. Press Enter. Excel moves down one cell.
◦ 6. Type 20 in cell C2.
◦ 7. Press Enter. Excel moves down one cell.
◦ 8. Type 5 in cell C3.
◦ 9. Press Enter. Excel moves down one cell.
◦ 10. Type =C2*C3 in cell C4.
◦ 11. Click the check mark on the Formula
bar. Excel multiplies C1 by cell C2 and
displays the result in cell C3. The formula
displays on the Formula bar.
Division
◦ 1. Press F5.
◦ 2. Type D1 in the Reference field.
◦ 3. Press Enter. Excel moves to cell D1.
◦ 4. Type Divide.
◦ 5. Press Enter. Excel moves down one cell.
◦ 6. Type 250 in cell D2.
◦ 7. Press Enter. Excel moves down one cell.
◦ 8. Type 50 in cell D3.
◦ 9. Press Enter. Excel moves down one cell.
◦ 10. Type =D2/D3 in cell D4.
◦ 11. Click the check mark on the Formula
bar. Excel divides cell D2 by cell D3 and
displays the result in cell D4. The formula
displays on the Formula bar.
Cont…
When creating formulas, you can
reference cells and include
numbers.
All of the following formulas are
valid:
◦ =A2/B2
◦ =A2+12-B3
◦ =A2*B2+12
◦ =24+53
Perform Advanced Mathematical
Calculations
When you perform mathematical
calculations in Excel, be careful of
precedence (order of importance).
Calculations are performed from left to
right, with multiplication and division
performed before addition and subtraction.
Exercise
◦ 1. Move to cell A7.
◦ 2. Type =3+3+12/2*4.
◦ 3. Press Enter.
◦ Note: Microsoft Excel divides 12 by 2,
multiplies the answer by 4, adds 3, and then
adds another 3. The answer, 30, displays in
cell A7
To change the order of calculation,
use parentheses (brackets).
Microsoft Excel calculates
information in parentheses first.
1. Double-click in cell A7.
2. Edit the cell to read
=(3+3+12)/2*4.
3. Press Enter.
Note: Microsoft Excel adds 3 plus
3 plus 12, divides the answer by 2,
and then multiplies the result by 4.
The answer, 36, displays in cell A7.
Perform Automatic Calculations
By default, Microsoft Excel
recalculates the worksheet as you
change cell entries.
Using cell references makes it
easier for excel to perform
automatic calculations.
This makes it easy to correct
mistakes and analyze a variety of
scenarios.
Cell Reference Operators
 Worksheets are divided into columns, rows, and
cells.
 On the worksheet, the rectangular area where a
row and a column intersect is known as a cell.
 Cell references identify individual cells or cell
ranges in columns and rows.

 Cell references tell Excel where to look for values

to use in a formula.

 Use cell references in formulas, so that Excel

can automatically update results when
values change or when you copy formulas.
Using Reference Operators
Reference operators refer to a cell or a group of
cells.
There are two types of reference operators:
1. Range
A range reference refers to all the cells between
and including the reference.
A range reference consists of two cell addresses
separated by a colon.
The reference A1:A3 includes cells A1, A2, and A3.
The reference A1:C3 includes cells A1, A2, A3, B1,
B2, B3, C1, C2, and C3.
2. Union
A union reference includes two or more references.
A union reference consists of two or more range
references, numbers, or cell addresses separated
by a comma.
The reference A7,B8:B10,C9,10 refers to cells A7,
B8 to B10, C9 and the number 10.
Examples of cell references.
A10 – the cell in column A and
row 10
A10,A20 - cell A10 and cell A20
A10:A20 - the range of cells in
column A and rows 10 through 20
B15:E15 = the range of cells in
row 15 and columns B through E
A10:E20 - the range of cells in
columns A through E and rows 10
through 20.
Other numbers and how to enter
them
•To enter fractions, leave a space
between the whole number and the
fraction. For example, 1 1/8.
•To enter a fraction only, enter a zero
first, for example, 0 1/4. If you enter
1/4 without the zero, Excel will interpret
the number as a date, January 4.
•Ifyou type (100) to indicate a
negative number by parentheses, Excel
will display the number as -100.
Functions
A function is a prewritten formula that performs
calculations using specific values in a particular
order.
◦ A formula is an equation that performs operations on worksheet
data.
A predefined formula – a formula that Excel has
already built for you – that performs calculations by
using specific values in a particular order or
structure.

 Use functions to add up values, calculate averages,

find the smallest or largest value in a range of
values.

 Excel has many functions that can be useful for

quickly finding the sum, average, count,
maximum value, and minimum value for a range
of cells.
Parts of a function
 In order to work correctly, a function must be written
in a specific way, which is called the syntax.
 The basic syntax for a function is the equals sign
(=), the function name (SUM, for example), and one
or more arguments.
 Arguments contain the information you want to
calculate.
 The function in the example below would add the
values of the cell range A1:A20.
 A1:A20 is the information, called the argument, that
tells the SUM function what to add.
Working with arguments
 Arguments can refer to both individual cells
and cell ranges and must be enclosed within
parentheses (brackets).
 You can include one argument or multiple
arguments, depending on the syntax required for
the function.
 For example, the function =AVERAGE(B1:B9)
would calculate the average of the values in the
cell range B1:B9.
 This function contains only one argument.
 Multiple arguments must be separated by a
comma.
 For example, the function =SUM(A1:A3, C1:C2,
E1) will add the values of all the cells in the
three arguments.
Creating a function
 Examples of commonly used functions in Excel.
◦ SUM: This function adds all of the values of the
cells in the argument.
◦ AVERAGE: This function determines the
average of the values included in the
argument. It calculates the sum of the cells and
then divides that value by the number of cells in
the argument.
◦ COUNT: This function counts the number of
cells with numerical data in the argument. This
function is useful for quickly counting items in a
cell range.
◦ MAX: This function determines the highest cell
value included in the argument.
◦ MIN: This function determines the lowest cell
value included in the argument.
You can find these Functions on a Home
Tab under Editing Group.
As prewritten formulas, functions
simplify the process of entering
calculations.

Using functions, you can easily and

quickly create formulas that might be
difficult to build for yourself.

Functions differ from regular formulas

in that you supply the value but not the
operators, such as +, -, *, or /.
For example, you can use the SUM
function to add.
When using a function, remember the
following:
◦ 1. Use an equal sign to begin a
formula.
◦ 2. Specify the function name.
◦ 3. Enclose arguments within
parentheses.
 Arguments are values on which you
want to perform the
 calculation. For example, arguments
specify the numbers or cells you want
to add.
◦ 4. Use a comma to separate
arguments.
Example of a function
=SUM(2,13,A1,B2:C7)
In this function:
◦ 1. The equal sign begins the function.
◦ 2. SUM is the name of the function.
◦ 3. 2, 13, A1, and B2:C7 are the arguments.
◦ 4. Parentheses enclose the arguments.
◦ 5. Commas separate the arguments.
NOTE: After you type the first letter of
a function name, the AutoComplete list
appears. You can double-click on an item
in the AutoComplete list to complete your
entry quickly. Excel will complete the
function name and enter the first
parenthesis.
Exercise
◦ 1. Open Microsoft Excel.
◦ 2. Type 12 in cell B1.
◦ 3. Press Enter.
◦ 4. Type 27 in cell B2.
◦ 5. Press Enter.
◦ 6. Type 24 in cell B3.
◦ 7. Press Enter.
◦ 8. Type =SUM(B1:B3) in cell A4.
◦ 9. Press Enter. The sum of cells B1 to
B3, which is 63, appears.
AutoSum
You can use the AutoSum button Σ
on the Home Tab to automatically
add a column or row of numbers.
When you press the AutoSum
button Σ, Excel selects the numbers
it thinks you want to add.
If you click the check mark on the
Formula bar or press the Enter key,
Excel adds the numbers.
If Excel's guess as to which
numbers you want to add is wrong,
you can select the cells you want.
EXERCISE: AutoSum
The following steps illustrates AutoSum:
◦ 1.Go to cell F1.
◦ 2. Type 3.
◦ 3. Press Enter. Excel moves down one cell.
◦ 4. Type 3.
◦ 5. Press Enter. Excel moves down one cell.
◦ 6. Type 3.
◦ 7. Press Enter. Excel moves down one cell
to cell F4.
◦ 8. Choose the Home tab.
◦ 9. Click the AutoSum button Σ in the Editing
group. Excel selects cells F1 through F3 and
enters a formula in cell F4.
◦ 10. Press Enter. Excel adds cells F1 through
F3 and displays the result in cell F4.
Calculate an Average
You can use the AVERAGE function to
calculate the average of a series of numbers.
Do the following exercise:
1. Move to cell A6.
2. Type Average. Press the right arrow key
to move to cell B6.
3. Type =AVERAGE(B1:B3).
4. Press Enter. The average of cells B1 to
B3, which is 21, appears.

ll
Calculate an Average with the
AutoSum Button
In Microsoft Excel, you can use the
AutoSum button to calculate an
average.
Do the following exercise:
1. Move to cell C6.
2. Choose the Home tab.
3. Click the down arrow next to the
AutoSum button .
4. Click Average.
5. Select cells C1 to C3.
6. Press Enter. The average of cells
C1 to C3, which is 100, appears.
Find the Lowest Number
You can use the MIN function to find the
lowest number in a series of numbers.
Do the following:
1. Move to cell A7.
2. Type Min.
3. Press the right arrow key to move to
cell B7.
4. Type = MIN(B1:B3).
5. Press Enter. The lowest number in the
series, which is 12, appears.
Note: You can also use the drop-down
button next to the AutoSum button to
calculate minimums, maximums, and
counts.
Find the Highest Number
You can use the MAX function to
find the highest number in a series of
numbers.
Do the following:
1. Move to cell A8.
2. Type Max.
3. Press the right arrow key to
move to cell B8.
4. Type = MAX(B1:B3).
5. Press Enter. The highest number
in the series, which is 27, appears.
Count the Numbers in a Series of Numbers
You can use the count function to count the
number of numbers in a series.
Do the following:
1. Move to cell A9.
2. Type Count.
3. Press the right arrow key to move to cell B9.
4. Choose the Home tab.
5. Click the down arrow next to the AutoSum
button . .
6. Click Count Numbers. Excel places the count
function in cell C9 and takes a guess at which cells
you want to count.
The guess is incorrect, so you must select the
proper cells.
7. Select B1 to B3.
8. Press Enter. The number of items in the series,
which is 3, appears.
Find more functions
Excel offers many other useful
functions, such as date and time
functions and functions you can use to
manipulate text.
To see all the other functions

1.Click the Sum button in the Editing

group on the Home tab.
2.Click More Functions in the list.
3.Inthe Insert Function dialog box
that opens, you can search for a
function.
Align Cell Entries
When you type text into a cell, by default
your entry aligns with the left side of the cell.
When you type numbers into a cell, by
default your entry aligns with the right side
of the cell.
You can change the cell alignment.
You can center, left-align, or right-align any
cell entry.
Look at cells A1 to D1.
Note that they are aligned with the left side
of the cell.

ll
Align Centre
To center cells A1 to D1:
◦ 1. Select cells A1 to D1.
◦ 2. Choose the Home tab.
◦ 3. Click the Center button in the
Alignment group. Excel centers each
cell's content.
Cut, copy and paste
Contents of a cell can be
copied/moved to another location in
the same or different worksheet by
using the copy, cut and paste features.
Keyboard shortcuts can also be used
to do the copy, cut and paste actions.
◦ Ctrl +C
◦ Ctrl +X
◦ Ctrl +V

◦l
Insert and Delete Columns and
Rows
You can insert and delete columns
and rows.
When you delete a column, you
delete everything in the column
from the top of the worksheet to the
bottom of the worksheet.
When you delete a row, you delete
the entire row from left to right.
Inserting a column or row inserts a
completely new column or row.
Cont.…
Cont.…
To delete columns F and G:
1. Click the column F indicator and
drag to column G.
2. Click the down arrow next to
Delete in the Cells group. A menu
appears.
3. Click Delete Sheet Columns. Excel
deletes the columns you selected.
4. Click anywhere on the worksheet
to remove your selection.
Cont.…
To delete rows 7 through 12:
1. Click the row 7 indicator and
drag to row 12.
2. Click the down arrow next to
Delete in the Cells group. A menu
appears.
3. Click Delete Sheet Rows. Excel
deletes the rows you selected.
4. Click anywhere on the
worksheet to remove your selection.
Cont.…
To insert a column:
1. Click on A to select column A.
2. Click the down arrow next to Insert in the Cells
group. A menu appears.
3. Click Insert Sheet Columns. Excel inserts a new
column. Click anywhere on the worksheet to
remove your selection.
To insert rows:
1. Click on 1 and then drag down to 2 to select
rows 1 and 2.
2. Click the down arrow next to Insert in the Cells
group. A menu appears.
3. Click Insert Sheet Rows. Excel inserts two new
rows.
4. Click anywhere on the worksheet to remove
your selection.
QUICK WAYS TO ENTER DATA
There are two time-savers you can use to enter data in
Excel:
◦ AutoComplete and AutoFill
AutoComplete: Type a few letters in a cell, and Excel
can fill in the remaining characters for you.
◦ AutoComplete detects when a value you are entering is
similar to previously entered values
◦ AutoComplete works for text or for text with numbers.
It does not work for numbers only, for dates, or for
times.
AutoFill: Type one or more entries in an intended series,
and then extend the series.
With AutoFill, you can quickly enter the months of the
year, the days of the week, multiples of 2 or 3, or other
data in a series. You type one or more entries, and then
extend the series.
 Sometimes Excel can’t calculate a formula because
the formula contains an error.

 If that happens, you’ll see an error value in a cell

instead of a result.

 Here are three common error values:

 #### The column isn’t wide enough to display the contents of
the cell. To fix the problem, you can increase column width, shrink
the contents to fit the column, or apply a different number format.

 #REF! A cell reference isn’t valid. Cells may have been deleted or
pasted over.

 #NAME? You may have misspelled a function name or used a

name that Excel doesn’t recognize.

 #DIV/0! Excel displays the #DIV/0! error when a formula tries to

divide a number by 0 or an empty cell.

 #VALUE! Wrong type of argument in a function or wrong type of

operator. This error is most often the result of specifying a
mathematical operation with one or more cells that contain text.
Cell references
You can type cell references
directly into cells, or you can
enter cell references by clicking
cells, which avoids typing errors.
Three types of cell references:
◦ Relative,
◦ Absolute and
◦ Mixed.
Relative references automatically
change as they’re copied down a
column or across a row.
Using this method the formula adjust
itself relatively to the current cell where
it has been copied.
Absolute references are fixed. They
don’t change if you copy a formula from
one cell to another. Absolute references
have dollar signs ($) like this: $A$1.
◦ You use absolute cell references to
refer to cells that you don’t want to
change as the formula is copied.
Mixed reference has either an
absolute column and a relative
row, or an absolute row and a
relative column.
As a mixed reference is copied
from one cell to another,
Absolute reference stays the
same but the Relative reference
changes.
 Say you receive some entertainment coupons offering
a
7 percent discount for video rentals, movies, and CDs.
How much could you save in a month by using the
discounts?
 You could use a formula to multiply those February
expenses by 7 percent.

 So start by typing the discount rate .07 in the empty

cell D9, and then type the formula in cell D4.
 Then in cell D4, type =C4*. Remember that
relative cell reference will change from row to
row.
 Enter a dollar sign ($) and D to make an
absolute reference to column D, and $9 to make
an absolute reference to row 9. Your formula will
multiply the value in cell C4 by the value in cell
D9.
 Cell D9 contains the value for the 7 percent
discount.
 You can copy the formula from cell D4 to D5 by
using the fill handle.
 As the formula is copied, the relative cell
reference changes from C4 to C5, while the
absolute reference to the discount in D9 does
not change; it remains as $D$9 in each row it is
copied to.
Create a chart
A chart, or graph, is a visual
representation of a set of data.
 A chart is a graphic representation of data
in a worksheet.
 A chart is a drawing which shows
information using lines and curves to show
amounts.
 With a chart, you can transform worksheet
data to show comparisons, patterns and
trends.
◦ Trend – a change in a situation
 So instead of having to analyze columns of
worksheet numbers, you can see at a
glance what the data means.
 A chart gets your point across—fast.
Creating a Column chart
Start by creating the worksheet
below exactly as shown.
After you have created the
worksheet, you are read to create
your chart.
Cont.…
Do the following:
1. Select cells A3 to D6. You must
select all the cells containing the
data you want in your chart. You
should also include the data labels.
2. Choose the Insert tab.
3. Click the Column button in the
Charts group. A list of column chart
sub-types types appears.
4. Click the Clustered Column chart
sub-type.
Excel creates a Clustered Column
chart and the Chart Tools context
tabs appear.
Apply a Chart Layout
Context tabs are tabs that appear only
when you need them, Called Chart Tools.
There are three chart context tabs: Design,
Layout, and Format.
The tabs become available when you
create a new chart or when you click on a
chart.
You can use these tabs to customize your
chart.
You can determine what your chart
displays by choosing a layout.
E.g. the layout you choose determines
whether your chart displays a title, where
the title displays, whether the chart has axis
labels and so on.
Excel provides several layouts from which
you can choose.
Cont.…
Do the following:
1. Click your chart. The Chart
Tools become available.
2. Choose the Design tab.
3. Click the Quick Layout button
in the Chart Layout group.
A list of chart layouts appears.
4. Click Layout 5. Excel applies
the layout to your chart.
Add Labels to a chart
When you apply a layout, Excel may create areas
where you can insert labels.
You use labels to give your chart a title or to label
your axes.
When you applied layout 5, Excel created label
areas for a title and for the vertical axis.
Do the following:
1. Select Chart Title. Click on Chart Title and then
place your cursor before the C in Chart and hold
down the Shift key while you use the right arrow
key to highlight the words Chart Title.
2. Type Toy Sales. Excel adds your title.
3. Select Axis Title. Click on Axis Title. Place your
cursor before the A in Axis. Hold down the Shift key
while you use the right arrow key to highlight the
words Axis Title.
4. Type Sales. Excel labels the axis.
5. Click anywhere on the chart to end your entry.
Add labels to a chart

Before After
Switch Data in the chart
If you want to change what
displays in your chart, you can
switch from row data to column data
and vice versa.
Do the following:
1. Click your chart. The Chart Tools
become available.
2. Choose the Design tab.
3. Click the Switch Row/Column
button in the Data group.
Excel changes the data in your
chart.
Switching data in a chart

Before After
Move a Chart to a Chart Sheet
By default, when you create a chart, Excel embeds
the chart in the active worksheet.
However, you can move a chart to another
worksheet or to a chart sheet.
A chart sheet is a sheet dedicated to a particular
chart.
By default Excel names each chart sheet
sequentially, starting with Chart1.
You can change the name.
Do the following:
1. Click your chart. The Chart Tools become
available.
2. Choose the Design tab.
3. Click the Move Chart button in the Location
group. The Move Chart dialog box appears.
4. Click the New Sheet radio button.
5. Type Toy Sales to name the chart sheet. Excel
creates a chart sheet named Toy Sales and places
your chart on it.
Change the Chart Type
You can change the chart type from a
column chart to a bar chart etc.
Do the following:
1. Click your chart. The Chart Tools
become available.
2. Choose the Design tab.
3. Click Change Chart Type in the
Type group. The Chart Type dialog box
appears.
4. Click Bar.
5. Click Clustered Horizontal Cylinder.
6. Click OK. Excel changes your chart
type.
IF Function in MS Excel
It is used when you want to test for
more than one values.
IF function checks to see if a
certain condition is true or false.
If the condition is true, the function will
do one thing, if the condition is false,
the function will do something else.
IFReturns a value or label if a
condition specified is evaluated to
TRUE and another is evaluated to
FALSE.
IF Function looks like this:
IF( logical_test, value_if_true, value_if_false)
The three items between the round brackets
of the word IF are the arguments that IF
function needs.
Here's what they mean:
logical_test
 The first argument is what you want to test for.
 E.g: Is the number in the cell greater than 50?
value_if_true
 This is what you want to do if the answer to the first
argument is YES. (E.g: Award PASS grade).
value_if_false
 This is what you want to do if the answer to the second
argument is NO. (E.g: Award a FAIL grade.)
Cont.…
Here is what we're saying in the IF
function above:
logical_test: Is the value in cell A1
greater than 5?
value_if_true: If the answer is Yes,
display the text "Greater than Five"
value_if_false: If the answer is NO,
display the text "Less than Five“
NOTE: You first tell Excel what you
want to check the cell for, then what
you want to do if the answer is YES,
and finally what you want to do if the
answer is NO. You separate each part
with a comma.
Example
Do the following:
1. Widen the B column a bit, as we'll be putting a
message in cell B1
2. Now click in cell A1 and type the number 6
3. Type the following in the formula bar of B1 (The
right angle bracket after A1 means "Greater Than".)
=IF(A1 > 5, "Greater than Five", "Less than Five")
The right angle bracket ( > ) is a Conditional
Operator.
4. Press Enter key on your keyboard and your
spreadsheet should look as below.

ll
Exercise 2
IF function will asks if the value in column A
is greater than the value in column B.
If it is, IF function will place the statement "A
is larger" in column D. If it is not, the IF
function will place the statement "B is larger"
in column D.
Our IF function will be entered into cell D1
and it looks like this:
=IF(A3 > B3,"A is larger","B is larger")
Note: The two text statements "A is larger"
and "B is larger" are enclosed in quotations.
◦ In order to add text to an Excel IF Function, it must
be enclosed in quotation marks.
Common Logical functions
 Example 3
 E.g. If A3, B3, C3, D3 and E3 contained a set
of marks 35, 50, 80, 60 and 45, grades are
to be awarded as follows:
◦ 80 to 100 A
◦ 60 to 79 B
◦ 40 to 59 C
◦ Below 40 Fail,
 To assign a grade use,
 IF (A3>=80, “A”, IF (A3>=60, “B”, If
(A3>=40, “C”, “Fail”)))