LEARNING ACTIVITY SHEET

SPECIAL PROGRAM IN ICT 7

OFFICE PRODUCTIVITY 7
Third Quarter, Week 7

Name of Learner: _____________________________ Date: __________________________

Grade Level /Section: __________________________

Formulas in Excel
BACKGROUND INFORMATION FOR LEARNERS
One eminent feature of Excel is the ability to compute or create mathematical formulas and functions.
It is easier than making computations manually. Excel’s environment became receptive and lively once you
use formulas, it automatically updates when you change a data.
Examples of calculations that can be done in excel are:
 Totals
 Subtotals
 Average
 Standard Deviation
In Excel, calculation can be specified by formulas and function.
Formulas are self-defined instructions in doing calculations.
Functions are pre-defined formulas that is already in Excel
Both formulas and functions should be entered in a cell. They should always start with an equal sign “=”.

ENTERING FORMULA
After the equal sign, a formula includes the addresses of the cells whose values will be manipulated
with appropriate operands placed in between. The operands are the standard arithmetic operators:
Operator Meaning Example
Fig. 1. Arithmetic Operations
+ Addition =C13+B14
- Subtraction =C13-C14
* Multiplication =C13*C14
Practice:
/ Division =C13/C14
Open a new worksheet.
^ Exponents =C13^C14
Enter the following data:
 Fig. 2. Sample Table

You can resize columns if text overlaps in another cell. Position your mouse pointer in between
column headers. Double click. It will auto fit the columns to the widest width in the entire column. Or, you
can point your mouse in between two column headers (ex. A and B) then drag your mouse to your desired
width.
Using arithmetic operations in Fig. 1, enter a simple formula for the Grand total of the Price per
pieces of the product.
 Using simple formula, in cell B12, you can simply type ‘=B4+B5+B6+B7+B8+B9+B10’. This is the
most logical solution.
 Press Enter in your keyboard when done. Notice that the result of the calculation is displayed in the
cell B12 that you selected.
 The formula is displayed in formula bar.
 If equal sign ‘=’ is not entered at the beginning, the formula you entered will display a regular text.

Cell Reference

Why use cell reference or cell range instead of the actual data (ex. 13.5 or 15.25) in the cell to do the
computation? Cell reference makes calculation automatic. We say automatic simply because if you change a
digit or data in a cell included in the formula, the answer automatically change.
Practice:
We are going to change some data in cells included in the formula and notice how the Excel
recalculates the formulas we entered.
 Change the amount in cell B4 from ’13.5’ to ‘18.25’.
 See how the total of calculations referencing cell ‘B12’ automatically changed.
Automatic Calculations automatically recalculates the result of any formulas as cell entries changes.

Create Formula that contains Function

Function is different from formula because after entering the equal sign ‘=’ you will then enter the cell
address but not the operators. The function performs calculations by using specific values, which is called
arguments in a particular order called syntax. Syntax must be followed strictly for the function to work
correctly.
 How to use function:
Start with equal sign ‘=’
After = sign state the function name (example: Sum)
Enclosed the argument inside a parenthesis. Argument is the cell range or cell reference.
Use a comma to separate the function’s individual arguments.

Formula will display

on formula bar by
cell range or
clicking the cell.
cell reference

Function name Cell reference

Practice:
Using the same spreadsheet above, Fig. 2 Sample Table, calculate the Grand Total of Quantity using
the SUM function.

Editing Formula

Sometimes we entered an incorrect cell address in our formula so we will need to correct it. To edit a
formula, do the following:
a. Select the cell that contains the formula you would like to edit. For example, select B12, then double
click it.

b. You can also edit the formula in the formula bar.

c. A colored border will appear to the referenced cells.
d. In the above table you can see that I have changed the referenced cell from B4 to B6. Instead of adding
them all, I just get the sum of all the pens. Notice a blue border to the amount of cells included in the
formula.
e. When finished, click on Enter in your keyboard.

COPYING FORMULA using CLIPBOARD

You can always copy or move a formula in a cell, but it is important to note what happens to cell
references when you do so.
 When you move a formula, the cell references within the formula do not change whatever type of
cell references (absolute or relative) that you use.
 When you copy a formula, the cell references may change based on the type of cell reference that
you use.

Move a formula

a. Select the cell that contains the formula that you want to move.
b. In the Home tab, in the clipboard group, click Cut.
You can also move formulas by dragging the border of the selected cell to the upper-left cell of the paste
area. Any existing data is replaced.
c. Click on the cell where you would like put the formula.
d. Do one of the following:
 To paste the formula and any formatting, on the Home tab, in the Clipboard group, click
Paste.
 To paste the formula only, on the Home tab, in the Clipboard group, click the small arrow
below the Paste, click Paste Special, and then click Formulas.

Copy a formula

a. Select the cell that contains the formula that you want to copy.
b. On the Home tab, in the Clipboard group, click Copy.
c. Do one of the following:
 To paste the formula and any formatting, on the Home tab, in the Clipboard group, click
Paste.
 To paste the formula only, on the Home tab, in the Clipboard group, click the small arrow
below Paste, click Paste Special, and then click Formulas.
 You can paste only the formula results. On the Home tab, in the clipboard group, click
Paste, click Paste Special, and then click Values.

Using Fill Handle

You can also copy formula using Fill Handle.

a. Using the same sample table in Fig. 2, enter the formula at the top cell.
 Go to D4, enter the formula ‘=B4*C4’. Hit Enter.
 Select the cell with the formula, and hover the mouse cursor over a small square at the lower
right-hand corner of the cell, which is called the Fill handle. As you do this, the cursor will
change to a thick black cross.

Hold and drag the plus

sign down to copy the
formula

You can also drag it to

the adjacent cell to the
 left or right or
upwards.
ABSOLUTE CELL REFERENCE
Absolute Cell reference is used when you want a formula to consistently refer to a particular cell even
if you copy or move the formula elsewhere in the worksheet. An absolute cell reference is a cell address that
contains a dollar sign ($) in the row or column coordinate or both.
When you enter a cell reference in a formula, Excel assumes it is a relative reference unless you change
it to an absolute reference. If you want to make part of a formula relative, remove the dollar sign that
appears before the column or row number.
Create an Absolute Reference
1. Click the cell where you want to enter the formula.
2. Type = (an equal sign) to begin the formula.
3. Select a cell reference, and then type an arithmetic operator (+, -, *, or /).
4. Select another cell reference and click F4 to make the cell reference absolute.
5. If necessary, continue entering the formula.
6. Click the (Enter) in the formula bar, or press Enter in your keyboard.
 When a formula or function contains absolute cell, reference is copied to a new location, the cell
reference is not adjusted.
Example: When the Formula ‘=B4*$C$4’ is copied from D4 to D5, the relative cell reference
changes from ‘B4’ to ‘B5’, but the ‘$C$4’ absolute cell reference remains unchanged.
An absolute cell reference is most often used when you want to use a constant in a formula or
function.

LEARNING COMPETENCY
To be able to create, edit, copy and move formula in excel.
To be able to identify and use absolute and relative cell reference in a formula.
ACTIVITIES
ACTIVITY 1
Directions:
1. Open a blank worksheet.
2. Copy the data on the table below

3. Create a simple addition formula using cell references. Create the formula in cell B4 to calculate
the total budget.
4. Try to modify the value of a cell referenced in a formula. Change the value of cell B2 to $2,000.
Notice the formula in cell B4 recalculates the total.
5. Go to cell G5. Create a formula that multiplies the cost of napkins by the quantity needed to
calculate the total cost.
6. Edit a formula using the formula bar. Go to cell B9, the formula should be ‘=B7/B8’. Edit this
formula in cell B9 from division (/) to minus sign (-).
7. Save your work. Filename: Act1_week7_Surname, Given name.
8. Send your work to designated Facebook group or google classroom.

ACTIVITY 2
Directions:
1. Open the file sent to you by your teacher or copy the table below in your worksheet.
 2. Create a formula that uses a relative reference. Use the fill handle to fill in the formula in
cells E4 through E14. Double-click a cell to see the copied formula and the relative cell
references.
3. Create a formula that uses an absolute reference. Correct the formula in cell D4 to refer only
to the tax rate in cell E2 as an absolute reference, then use the fill handle to fill the formula
from cells D4 to D14.
4. Save your work with filename: Act2_Week7_surname, given name.
5. Send it to your teacher.
REFLECTION

REFERENCES
https://edu.gcfglobal.org/en/excel2013/simple-formulas/1/
https://kb.nmsu.edu/page.php?id=75231
https://www.informit.com/articles/article.aspx?p=2031329&seqNum=7#:~:text=Create%20an%20Absolute
%20Reference&text=Select%20a%20cell%2C%20and%20then,necessary%2C%20continue%20entering
%20the%20formula.

https://www.mc.vanderbilt.edu/crc/workshop_files/2008-02-08.pdf

Prepared by: CATHERINE B. TALENS

Name of Writer

Noted by: LABERNE A. LADIGNON, JR

Division ICT Coordinator/ OIC EPS