MODULE 4: EXCEL

UNIT 5: MICROSOFT EXCEL (BASIC) – FUNCTIONS

Lecturer: M.S. Phạm Thanh Tùng

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 1

CONTENT OF THE LECTURE
basic functionS sort data

1 BASIC FUNCTIONS 5 SORT DATA

selection/decision functionS Filter data

2 SELECTION/DECISION FUNCTIONS 6 FILTER DATA

data processing functionS CHARTS

3 DATA PROCESSING FUNCTIONS 7 CHARTS

data lookup functionS

4 DATA LOOKUP FUNCTIONS

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 2

BASIC FUNCTIONS

Basic arithmetic functions

❑ Round a number down to the nearest integer: INT(number)
❑ Divide and get the remainder: MOD(number, divisor)
❑ Return the largest number: MAX(series of numbers or numbers array)
❑ Return the smallest number: MIN(series of numbers or numbers array)
❑ Round the number: ROUND(number, num_digits)
o If num_digits > 0, round to decimal places
o If num_digits < 0, round to integer places
o If num_digits = 0, rounding at the units place
❑ Round the number to the desired multiple: MROUND(number, multiple). Both number
and multiple have the same sign, otherwise the function returns the #NUM! error.
❑ Take absolute value: ABS(number)
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 3
BASIC FUNCTIONS

Test functions
❑ ISBLANK(object): Check is a blank cell.
❑ ISERROR(object), ISERR(object), ISNA(object): Check is errors, errors different
#N/A, error #N/A.
❑ ISEVEN(object), ISODD(object): Check is an even number or odd number.
❑ ISTEXT(object), ISNONTEXT(object): Check is Text type or not.
❑ ISNUMBER(object): Check is Number type.
❑ ISLOGICAL(object): Check is Logical type.
❑ …
❑ And the result these functions return is a Logical value.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 4

BASIC FUNCTIONS

Text processing functions

❑ LEN(text): Return the number of characters in a text string.
❑ VALUE(string of digits): Convert string to number.
❑ TEXT(number, format_text): Convert a value to text in a specific number
format.
❑ CONCATENATE(list of strings): Concatenate text strings into a string.
❑ UPPER(text): Print all alphabetic characters in uppercase.
❑ LOWER(text): Print all alphabetic characters in lowercase.
❑ PROPER(text): Capitalize all first letters of each word.
❑ TRIM(text): Remove extra spaces from text.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 5
BASIC FUNCTIONS

Text processing functions

❑ SEARCH(find_text, within_text [,start_num=1]): Returns the position of the
first find_text that appears in within_text starting with start_num, if not
found the function returns the #VALUE! error. SEARCH is not case-sentitive.

❑ FIND(find_text, within_text [,start_num=1]): Similar to SEARCH function.

FIND is case-sensitive.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 6

BASIC FUNCTIONS

Text processing functions

❑ REPLACE(old_text, start_num, num_chars, new_text): Replace new_text at
the position starting from start_num of old_text with num_chars characters.

❑ SUBSTITUTE(text, old_text, new_text, [instance_num]): Replace old_text in

text by new_text with instance_num ordered number. If the instance_num
argument is omitted, the function will replace all positions where old_text
appears in the text.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 7

BASIC FUNCTIONS

Text processing functions

❑ LEFT(text [, num_chars=1]): Extract the first characters of a string.

❑ RIGHT(text [, num_chars=1]): Extract the last characters of a string.

❑ MID(text, start_num, num_chars): Extract characters starting from any

position (a natural number other than 0) of a string.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 8

BASIC FUNCTIONS

Date/Time processing functions

❑ NOW(): Return the current date and time.
For example: 22/12/2022 12:12 PM
❑ TODAY(): Return the current date.
For example: 22/12/2022
❑ DATE(year, month, day): Return a Date value.
❑ TIME(hour, minute, second): Return a Time value.
❑ The functions return a number representing each type:
DAY(serial_number), MONTH(serial_number), YEAR(serial_number),
HOUR(object), MINUTE(serial_number), SECOND(serial_number)

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 9

BASIC FUNCTIONS

Date/Time processing functions

❑ WEEKDAY(serial_number, [return_type=1]): Return a number representing
the day of the week.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 10

BASIC FUNCTIONS

Date/Time processing functions

❑ WORKDAY(start_date, days [, holidays]): Returns the date before or after the
start_date in days, only workdays (Monday to Friday), excluding holidays.
❑ [Link](start_date, days [, weekend, holidays]): Similar to the
WORKDAY function, there is an additional weekend argument to select non-
working days, default is 1 (Saturday and Sunday).
❑ NETWORKDAYS(start_date, end_date [, holidays]): Return the number of days
between start_date and end_date, excluding Saturdays, Sundays and holidays.
❑ [Link](start_date, end_date [, weekend, holidays]): Similar to
the NETWORKDAYS function, there is an additional weekend argument to select
non-working days, default is 1 (Saturday and Sunday).

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 11

BASIC FUNCTIONS

Date/Time processing functions

❑ EDATE(start_date, months): Return the date with the number of months added
or subtracted.

❑ EOMONTH(start_date, months): Returns the last day of the month of the date
added or subtracted by the number of months.

❑ YEARFRAC(start_date, end_date): Return the number of decimal years between

the start_date and end_date.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 12

BASIC FUNCTIONS

Date/Time processing functions

❑ DATEDIF(start_date, end_date, unit): Returns the difference from
start_date to end_date in unit; If start_date > end_date, the #NUM! error
is raised.
Unit Meaning
"Y" Number of whole years
"M" Number of whole months
"D" Number of whole days
"YM" Number of months difference (ignoring years)
"YD" Number of days difference (ignoring years)
"MD" Number of days difference (ignoring years and months)
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 13
BASIC FUNCTIONS

Logical functions
❑ NOT(logical): Change TRUE to FALSE, or FALSE to TRUE.

❑ AND(argument list): Return TRUE when all arguments evaluate to TRUE,

otherwise return FALSE.

❑ OR(argument list): Return FALSE when all arguments evaluate to FALSE,

otherwise return TRUE.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 14

BASIC FUNCTIONS

INDIRECT function*
❑ INDIRECT(ref_text [, a1=TRUE]): Returns a specific reference by a text
string ref_text.
o a1=TRUE (default): A1 style – use column and row names.
o a1=FALSE: R1C1 style – use column and row numbers.
o Example: Cell B1 is equivalent to R1C2.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 15

BASIC FUNCTIONS

LET function*
❑ Available from Microsoft Excel version 2021.

❑ LET(name1, name_value1, calculation_or_name2 [, name_value2, …]):

Assign calculation results to names.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 16

SELECTION/DECISION FUNCTIONS

IF function
❑ IF(logical_test, [value_if_true,] [value_if_false])
o logical_test: Conditional expression – an
expression is evaluated to a Logical value.
o value_if_true: Select this expression as the
function's return result if logical_test is the TRUE
value.
o value_if_false: Select this expression as the
function's return result if logical_test is the
FALSE value.
❑ If the argument is left blank, the return value is 0 or
FALSE value.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 17
SELECTION/DECISION FUNCTIONS

IFNA function
❑ IFNA(value, value_if_na): Check whether the value argument is the #N/A
error or not. If so, the function returns the value of the value_if_na
argument, otherwise the function returns the value of the value argument.
❑ The #N/A error value can be entered as follows: #N/A
For example: =IFNA(#N/A, “Excel”) --> Excel

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 18

SELECTION/DECISION FUNCTIONS

IFERROR function
❑ IFERROR(value, value_if_error): Checks whether argument value is an
error or not. If so, the function returns the value of the value_if_error
argument, otherwise the function returns the value of the value argument.

❑ Error values can be entered as follows: #NULL!, #VALUE!, #DIV/0!, #NUM!,

#NAME?, #N/A, #REF!, ...

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 19

DATA PROCESSING FUNCTIONS

The rank of a given value in an array of values

❑ RANK(number, ref [, order=0]): Rank a value of
cell number in an array ref in order.
o number: Value used to evaluate ratings.
o ref: Array of references to rank. Example
o order: Specify ascending or descending
ratings.
▪ If order = 0, descending (default): The
largest value represents the smallest
ranking number (number 1).
▪ If order = 1, ascending.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 20

DATA PROCESSING FUNCTIONS

Count the number of cells

❑ COUNT(range):Count cells in a range that have a number value.
❑ COUNTA(range): Count cells in a range that are not blank.
❑ COUNTBLANK(range): Count cells in a range that are blank and cells with
an empty string value.

❑ COUNTIF(range, criteria): Count cells in a range that match a criteria.

❑ COUNTIFS(range, criteria1 [, range, criteria2, …]): Count cells in a range
that match all criteria.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 21

DATA PROCESSING FUNCTIONS

Count the number of cells: Criteria

❑ Criteria is a string (text).
❑ Criteria is case-insensitive.
❑ Criteria cannot be longer than 255 characters, so CONCATENATE function
or concatenation operator & must be used to concatenate strings
together.
❑ Criteria can use comparison operators and wildcard characters.
o * Represents zero or more characters
o ? Represents any one character
o ~ Escape sequence character: ~* ~? ~~

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 22

DATA PROCESSING FUNCTIONS

Count the number of cells: Criteria

❑ Criteria is a string (text).
❑ Criteria is case-insensitive.
❑ Criteria cannot be longer than 255 characters, so CONCATENATE function
or concatenation operator & must be used to concatenate strings
together.
❑ Criteria can use comparison operators and wildcard characters.
o * Represents zero or more characters
o ? Represents any one character
o ~ Escape sequence character: ~* ~? ~~

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 23

DATA PROCESSING FUNCTIONS

Count the number of cells: Criteria

Criteria Meaning
(omitted) No count, result is 0
"" Blank cells or cells containing empty strings ~ COUNTBLANK
"=" Blank cells
"<>" Non-blank cells ~ COUNTA
"SpecificValue" Cells containing a specific value, for example: "#N/A", "12.5"
"=*" Cells containing values ​that are text
"<>*" Cells containing values ​that are not text
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 24
DATA PROCESSING FUNCTIONS

Calculate the sum and average

❑ Sum functions: SUM, SUMIF, SUMIFS
❑ Average functions: AVERAGE, AVERAGEIF, AVERAGEIFS
❑ The above functions use the same evaluation array (range) and criteria as the
counting functions.
❑ Sum and average functions only consider cells with values of Number type.

❑ Additionally, PRODUCT(arguments) return the product of a list of arguments.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 25

DATA PROCESSING FUNCTIONS

SUMPRODUCT function*
❑ SUMPRODUCT(array1 [, array2 …]): Returns the sum of the products of the
corresponding ranges or arrays. The default operation is multiplication, but
addition, subtraction, and division are possible.

❑ The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.

❑ Note, each array argument in the function must be an array of numbers.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 26

DATA PROCESSING FUNCTIONS

SUMPRODUCT function*
❑ Use the asterisk operator * equivalent to the AND operation and the plus sign
operator + equivalent to the OR operation.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 27

DATA LOOKUP FUNCTIONS

VLOOKUP function
❑ VLOOKUP(lookup_value, table_array, col_index_num [, range_lookup=0]):
Find a value ​in a vertical range of data.
o lookup_value: Value used for lookup.
o table_array: The array is used for lookup (compared with the first
column), absolute addresses should be applied.
o col_index_num: The index (an integer) of the column containing the
value to get. The first column has an index of 1.
o range_lookup: If FALSE or 0 then absolute lookup (default), if TRUE or 1
then relative lookup (table_array is sorted).

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 28

DATA LOOKUP FUNCTIONS

HLOOKUP function
❑ HLOOKUP(lookup_value, table_array, row_index_num [, range_lookup=0]):
Find a value ​in a horizontal range of data.
o lookup_value: Value used for lookup.
o table_array: The array is used for lookup (compared with the first row),
absolute addresses should be applied.
o row_index_num: The index (an integer) of the row containing the value
to get. The first row has an index of 1.
o range_lookup: If FALSE or 0 then absolute lookup (default), if TRUE or 1
then relative lookup (table_array is sorted).

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 29

DATA LOOKUP FUNCTIONS

INDEX function
❑ INDEX(array, row_num, column_num): Returns a value based on the row and
column index.
o To return whole rows/columns, the remaining column/row index is 0.
o Index can be an array of numbers to get the value of many columns and
many rows, note row_num uses column array and column_num uses
row array if the value is found in the correct position.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 30

DATA LOOKUP FUNCTIONS

INDEX function
❑ MATCH(lookup_value, lookup_array [, match_type]): Return an index of the
lookup_value found in the lookup_array with match_type.
o 1 – Less than: Find the largest value that is less than or equal to the
search value.
o -1 – Greater than: Find the smallest value that is greater than or equal to
the search value.
o 0 – Exact match: Find the first value that is exactly equal to the search
value

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 31

SORT DATA

Sort Tool: Sort dialog box

❑ Select Data > Sort & Filter > Sort
to open the Sort dialog box.
o Sort by: The name of the
column used for sorting.
o Sort on: The type of data
used for sorting.
o Order: Order to sort.
❑ Add Level/Delete Level: If
adding/removing columns used
for sorting

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 32

SORT DATA

SORT function*
❑ Available from Microsoft Excel version 2021.

❑ SORT(array, sort_index, sort_order, by_col): Sort and extract sorted data

by a column.
o array: The array needs to be sorted.
o sort_index: Column index used for sorting.
o sort_order: Sort ascending (1) or descending (-1).
o by_col: Sort by row (FALSE) or column (TRUE).

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 33

SORT DATA

SORTBY function*
❑ Available from Microsoft Excel version 2021.

❑ SORTBY(array, by_array1, sort_order1, …): Sort and extract sorted data by

multiple columns.
o array: The array needs to be sorted.
o by_array1: An entire array is a column used for sorting.
o sort_order1: Sort ascending (1) or descending (-1).

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 34

FILTER DATA

Filter Tool: Advanced Filter dialog box

❑ Step 1: Create a condition/criteria table used to filter data.
o Method 1: The header line of the condition table matches the header
of the elements to filter. Values ​in the same row represent AND
operation, while values ​in different rows represent OR operation.
o Method 2: Use a formula that applies an expression on a data line
that needs to be filtered to return a Logical value.
❑ Step 2: Highlight the table (including header line) that needs to be filtered,
then Select Data > Sort & Filter > Advanced to open the Advanced Filter
dialog box.
o List range: The original data area needs to be filtered.
o Criteria range: Condition/criteria table.
o Copy to: The starting cell position (top left) of the array to be filtered
to.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 35
FILTER DATA

FILTER function*
❑ Available from Microsoft Excel version 2021.

❑ FILTER(array, include [, if_empty]): Filter and extract filtered data.

o array: The array needs to be filtered.
o include: Select an entire column (array) to filter and match against a value.
If there are multiple filter conditions, use the asterisk operator * equivalent
to the AND operation and the plus sign operator + equivalent to the OR
operation.
For example, (A1:A5="male")*(B1:B5>=5)
o if_empty: value used to fill empty cells.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 36

CHARTS

Insert charts
❑ Select Insert > Charts…
o Column
o Line
o Pie
o Area
o Scatter
o Histogram
o Combo …
❑ Use F11 function key to create a column chart on a new worksheet.

11/07/2025 LECTURES OF APPLIED INFORMATICS Page 37