Formulas, Function, and
Creating Charts
Module 5
OUTLINE
Slide 3 Slide 6
Slide 26 Slide 33
Module 5
FORMULAS
Formulas
Formulas and functions are the bread and butter of Excel. They drive
almost everything interesting and useful you will ever do in a
spreadsheet.
What is a formula?
• Formulas are instructions for
calculations;
• they define how one cell relates
to other cells.
• it is an expression that returns a
specific result.
Formulas MICROSOFT EXCEL - ITEC 11
other example:
Note: all formulas in Excel must begin
with an equals sign ( = ).
Formulas MICROSOFT EXCEL - ITEC 11
Module 5
Cell Referencing
Cell references
A cell referencing means using a cell or a range of cells on a worksheet in a formula.
Example: C1 contains the following
formula: A1+A2+A3
values can be changed at any time.
Notice because we are using cell references for A1 , A2 , and A3 , these values
can be changed at any time and C1 will still show an accurate result .
Cell Referencing MICROSOFT EXCEL - ITEC 11
TWO TYPES OF CELL REFERENCING
Relative references change when a formula is copied to another cell.
Absolute references remains constant no matter where they are
copied.
Cell Referencing MICROSOFT EXCEL - ITEC 11
RELATIVE REFERENCING
By default, all cell references are relative references . When copied across
multiple cells, they change based on the relative position of rows and columns.
Relative references are especially convenient whenever you need to
repeat the same calculation across multiple rows or columns.
Relative Referencing MICROSOFT EXCEL - ITEC 11
RELATIVE REFERENCING
automatically update when a formula is copied to a new location.
This means you don't need to enter the same basic formula again
and again.
In the sample, the formula in
E1 has been copied and
pasted it in E2
You can also drag it using the fill handle
Relative Referencing MICROSOFT EXCEL - ITEC 11
Another example of relative referencing:
We want to create a formula that will multiply each item's price by the quantity.
Step 1:
Select the cell that
will contain the
formula. In our
example, we'll
select cell D2 .
Relative Referencing MICROSOFT EXCEL - ITEC 11
Another example of relative referencing:
Step 2:
Enter the formula
to calculate the
desired value. In
our example, we'll
type =B2*C2 .
Relative Referencing MICROSOFT EXCEL - ITEC 11
Another example of relative referencing:
Step 3: Press Enter on your keyboard. The formula will be calculated, and the
result will be displayed in the cell.
Step 4:
Locate the fill handle
in the lower-right corner
of the desired cell. In
our example, we'll
locate the fill handle for
cell D2 .
Relative Referencing MICROSOFT EXCEL - ITEC 11
Another example of relative referencing:
Step 5:
Click, hold , and drag
the fill handle over the
cells you wish to fill. In
our example, we'll
select cells D3:D12.
Relative Referencing MICROSOFT EXCEL - ITEC 11
Another example of relative referencing:
Step 6:
Release the mouse.
The formula will be
copied to the selected
cells with relative
references and the
values will be
calculated in each cell.
Relative Referencing MICROSOFT EXCEL - ITEC 11
ABSOLUTE REFERENCING
Unlike relative references, absolute references do not change when copied
or filled. You can use an absolute reference to keep a row and/or column
constant .
An absolute reference is designated in a formula by the addition of a dollar
sign ($) before the column and row.
Absolute Referencing MICROSOFT EXCEL - ITEC 11
ABSOLUTE REFERENCING
Absolute Referencing MICROSOFT EXCEL - ITEC 11
ABSOLUTE REFERENCING
For example, we want to multiply each value in column D by 10 , which is
entered in A1 . By using an absolute reference for A1 , we "lock " that
reference so it won't change when the formula is copied to E2 and E3:
Here are the final formulas in E1, E2, and E3:
Absolute Referencing MICROSOFT EXCEL - ITEC 11
ABSOLUTE REFERENCING
Notice the reference to D1 updates when the formula is copied, but the
reference to A1 never changes. Now we can easily change the value in A1, and all
three formulas recalculate. Below, the value in A1 has changed from 10 to 12:
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
we'll use the 7.5% sales tax rate in cell E1 to calculate the sales tax for all items in
column D.
We'll need to use the absolute cell reference $E$1 in our formula. Because each
formula is using the same tax rate , we want that reference to remain constant
when the formula is copied and filled to other cells in column D .
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
Step 1:
Select the cell that will
contain the formula. In
our example, we'll
select cell D3 .
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
Step 2:
Enter the formula to
calculate the desired
value. In our example,
we'll type
=(B3*C3)*$E$1 .
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
Step 3: Press Enter on your keyboard. The formula will calculate, and the result
will display in the cell.
Step 4:
Locate the fill handle
in the lower-right corner
of the desired cell. In
our example, we'll
locate the fill handle for
cell D3 .
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
Step 5:
Click , hold , and drag
the fill handle over the
cells you wish to fill,
cells D4:D13 in our
example.
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Another example of absolute referencing:
Step 6:
Release the mouse.
The formula will be
copied to the selected
cells with an absolute
reference , and the
values will be
calculated in each cell.
Absolute Referencing MICROSOFT EXCEL - ITEC 11
Module 5
Functions
Functions
Functions are built-in formulas that perform common calculations.
All spreadsheet programs include common functions that can be used
for quickly finding the sum , average , count , maximum value , and
minimum value for a range of cells.
Functions MICROSOFT EXCEL - ITEC 11
Functions
Arguments can refer to both individual cells and cell ranges and must be
enclosed within parentheses .
For example, the function =AVERAGE(B1:B9)
would calculate the average of the values in the
cell range B1:B9 .
Functions MICROSOFT EXCEL - ITEC 11
Functions
Multiple arguments must be separated by a comma .
For example, the function =SUM(A1:A3, C1:C2, E2) will add the values
of all cells in the three arguments.
Functions MICROSOFT EXCEL - ITEC 11
Functions MICROSOFT EXCEL - ITEC 11
WORKSHEET TEMPLATES
Worksheet templates are forms containing formats and formulas custom -
designed for particular kinds of work.
Examples are templates for calculating loan payments, tracking travel
expenses, monitoring personal budgets, and keeping track of time worked on
projects.
MICROSOFT EXCEL - ITEC 11
MULTIDIMENSIONAL
SPREADSHEET
Most spreadsheet applications are multidimensional,
meaning that you can link one spreadsheet to another .
A three -dimensional spreadsheet, for example, is like a stack of
spreadsheets all connected by formulas.
A change made in one spreadsheet automatically
affects the other spreadsheets.
MICROSOFT EXCEL - ITEC 11
Module 5
ANALYTICAL
GRAPHICS: CREATING
CHARTS
ANALYTICAL GRAPHICS
Analytical graphics, or business graphics, are
graphical forms that make numeric data easier
to analyze than it is when organized as rows
and columns of numbers.
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Column Chart
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Column Chart
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Pie Chart
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Pie Chart
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Line Graph
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Line Graph
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
Example Of Analytical Graphics
Scatter Chart
ANALYTICAL GRAPHICS MICROSOFT EXCEL - ITEC 11
REFERENCES:
https ://[Link]/lear n/wor ks ho ps/onli ne -
tutorials/microsoft - office - 2010/micr osoft - e xcel - 2010/
https ://[Link]/en/e xcelfor m ulas /rel ati ve - and -
absolute - cell - references/1/
[Link] - for mulas - a nd - fun ctio ns
[Link] yout ube .co m/ watc h? v=k1 VUZEVu DJ8
MICROSOFT EXCEL - ITEC 11
THANK YO U AND
GOD BLESS !
Prepared by: Abegail Peseral Calixtro