First Semester, A.Y.

2022-2023
• Use common productivity tools effectively by
maximizing advanced application
techniques.
• Create original or derivative ICT content to
effectively communicate or present data or
information related to specific professional
tracks.
• Microsoft Excel is a software program produced by
Microsoft that allows users to organize, format, and
calculate data with formulas using a spreadsheet system.
• In the early days of accessible PC business computing,
Microsoft Excel played a central role in bookkeeping and
record-keeping for enterprise operations.
• One of the best examples of a use case for MS Excel is a
table with an auto-sum format.
When you look at the Excel
screen — or any other
spreadsheet screen — you
see a rectangular table or
grid of rows and columns.
The horizontal rows are
identified by numbers (1, 2,
3) and the vertical columns
by letters of the alphabet (A,
B, C). For columns beyond
26, columns are identified by
two or more letters such as
AA, AB, AC or AAA, AAB, etc.
1. Formulas
In Excel, a formula is an expression that operates on values in a
range of cells or a cell. For example, =A1+A2+A3, which finds the
sum of the range of values from cell A1 to cell A3.
2. Functions
Functions are predefined formulas in Excel. They eliminate
laborious manual entry of formulas while giving them human-
friendly names. For example: =SUM(A1:A3). The function sums all
the values from A1 to A3.
• SUM - It performs the mathematical operation of addition,
calculating the sum of the chosen cell range values. The
selected cells can be contiguous or non-contiguous.
• COUNT - determines the number of cells in a selected range that
contains only a numeric value. The selected cells can be
contiguous or non-contiguous.
• COUNTA - calculates the total count of cells containing a value,
which could be numerical, text, or data in any format. The
selected cell range can be contiguous or non-contiguous.
• COUNTBLANK - returns the count of blank or empty cells in
the chosen cell range.
• AVERAGE - evaluates the average of values in the chosen cell
range, where the range can be contiguous or non-
contiguous.
• MIN - determines the least value in the chosen cell range,
with the range being adjacent or non-adjacent.
• MAX - determines the highest value in the chosen cell range,
with the selected cell range being adjacent or non-adjacent.
• LEN - calculates the number of characters in a cell, including
spaces and special characters.
• TRIM - eliminates unwanted empty spaces in a cell. Thus, it
ensures other functions do not result in errors due to
unnecessary spaces.
• IF - Of the list of basic excel formulas, IF is really important. It
checks a logical condition and returns a value if the criterion
holds; otherwise, it returns another value when the logical
test is FALSE.
• number1: It is the
mandatory argument and
is the first number we
need to add.
• number2: It is an optional
argument and is the
second number we want
to add.
Example
• We need to determine the
overall aggregate for the
students in the Students’
Unit
• Choose cell J3 and enter
the formula =SUM(B3:I3)
• value1: It is the mandatory
argument and denotes the first
cell reference or range for
which we want to count the
numeric values.
• value2: It is an optional
argument and indicates the
second cell reference or range
for which we want to count the
numeric values.
Example
• Suppose we want to determine
how many students appeared
for the unit test in each subject
in the Students’ Unit Test Marks
table.
• Choose cell B18 and enter the
formula =COUNT(B3:B17).
• value1: This is a mandatory
argument and denotes the first
cell reference or range for
which we want to count the
values.
• value2: This is an optional
argument and is the second
cell reference or range for
which we want to count the
values.
Example
• We need to determine how many
students appeared for the unit test
in each subject. We mark those
students present who give valid
reasons for their absence. And if
they are absent and do not provide
the reason, we leave the individual
cells empty.
• Choose cell B18 and enter the
formula =COUNTA(B3:B17)
• range: It is the cell range in which we want to
count the empty cells.
Example
• Suppose we want to update
the absentees’ data for
each subject in the
Students’ Unit Test Marks
table.
• Choose cell B18 and enter
the formula
=COUNTBLANK(B3:B17)
• number1: It is a mandatory
argument and is the first value
of a cell reference or range, for
which we have to calculate the
average.
• number2: It is the second value
of a cell reference or range for
which we have to calculate the
average.
Example
• Assume we have to
calculate the average
marks scored by each
student in the Students’ Unit
Test Marks table.
• Choose cell J3 and enter
the formula
=AVERAGE(B3:I3)
• where number1 is the
mandatory argument,
and the subsequent
arguments are
optional.
Example
• Suppose we need to
determine the least score in
each subject in the
Students’ Unit Test Marks
table.
• Choose cell B18 and enter
the formula =MIN(B3:B17)
• where number1 is the
mandatory argument,
and the subsequent
arguments are
optional.
Example
• Suppose we need to find
the top score in each
subject in the Students’ Unit
Test Marks table.
• Choose cell B18 and enter
the formula =MAX(B3:B17)
• The argument can be
a number, text, or a
reference to the cell for
which we want to
calculate the number
of characters.
Example
• Consider column A
(Student Name) in the
Students’ Unit Test Marks
table. Suppose we need to
determine the length of
each student’s name.
• Choose cell B2 and enter
the formula as =LEN(A2)
Eliminates unwanted
empty spaces in a cell.
Thus, it ensures other
functions do not result in
errors due to unnecessary
spaces.
Example
• Choose cell D2 and
enter the TRIM() as
=TRIM(C2)
 • logical_test: The logical condition,
based on which the IF() returns a
value.
• [value_if_true]: It is the value the
IF() returns if the logical_test is
TRUE.
• [value_if_false]: It is the value the
IF() returns if the logical_test is
FALSE.
The logical_test will include logical
operators, such as =, >, <, >=, <=, and <>.
Example
• Suppose we want to find the
Top Achiever among the
students in the Students’ Unit
Test Marks table based on
the Average Aggregate.
• Choose cell K3 and enter the
formula:
=IF(MAX($J$3:$J$17)=J3,A3,””)
VLOOKUP
• VLOOKUP function is best suited for situations when you
are looking for a matching data point in a column, and
when it finds the specified match, it returns a value in the
same row
• Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
• lookup_value – this is the
look-up value you are trying
to find in the left-most column
of a table.
• table_array – this is the table
array in which you are looking
for the value. This could be a
reference to a range of cells or
a named range.
• col_index – this is the column
index number from which you
want to fetch the matching
value
• [range_lookup] – here you
specify whether you want an
exact match (0 or FALSE) or an
approximate match (1 or
TRUE)
Example
• We want to fetch the prices of
products “P1252,” “P1440,” and
“P1966” in cells F2, F3, and F4
respectively. Find an exact
match with the VLOOKUP
function of Excel.
Step 1:
• Enter the VLOOKUP formula in cell F2. For this, type “=VLOOKUP”
followed by the opening parenthesis. Since the values to be looked
up are placed in the range E2:E4, enter the first “lookup_value” as
E2.
• You can also type the
lookup value by
enclosing it with
quotation marks (" ").
For example "P1252"
Step 2:
• Select the range A2:C10 for the “table_array” argument. Notice
that this range has been entered as a relative reference.
Note: A relative reference
changes when the
formula is copied to the
other cells, unlike an
absolute reference that
does not change on
copying the formula.
Step 3:
• Enter the “col_index_num” as 3 because the price needs to be
fetched from the third column (column C) of the “table_array”
(A2:C10).
Step 4:
• Specify the “range_lookup”
argument as “false” or "0". This
is because an exact match
needs to be found. Next, press
the “Enter” key.
• Innovative Training Works, Inc. (2016). Empowerment Technologies. Rex Book Store.
• (2020, August 25). Microsoft Excel. Techopedia.
https://www.techopedia.com/definition/5430/microsoft-excel
• French, T. (2020). What is Microsoft Excel and What Does It Do?. Lifewire.
https://www.lifewire.com/what-is-microsoft-excel-3573533
• ExcelMojo Editorial Team (n.d.). Basic Excel Formulas. ExcelMojo.
https://www.excelmojo.com/basic-excel-formulas/
• Jeevan A.Y. (n.d.). VLOOKUP Excel Function. ExcelMojo. https://www.excelmojo.com/vlookup-
function-in-excel/
• Bansal S. (n.d.). 10 VLOOKUP Examples For Beginner & Advanced Users. TrumpExcel.
https://trumpexcel.com/excel-vlookup-function/