Advanced Techniques
using Microsoft Excel
What is a spreadsheet?
It is a software application that
enables a user to save, sort and manage
data in an arranged form of rows and
columns.
Spreadsheets can be used to
determine statistical analysis, compute
student’s grades, keep track business
accounts and inventories, and manage
databases.
Spreadsheets are made up
of the following:
Columns – identified with alphabetic headings
Rows - identified with numeric headings
Cells – are the intersection of rows and columns
Cell Reference – also known as cell address
that identifies a cell on a worksheet.
Active Cell – the currently selected
cell in a
spreadsheet and is indicated
by a bold
outline that surrounds the
cell
Texts or labels – It identifies the
purpose of a cell, it can be a brief
Number data or constant – a value that doesn’t
change and is directly inserted into a cell.
Formula (mathematical equations) – an expression
that calculates the value of a cell.
Mathematical Operators – MS Excel uses
standard operators for formulas.
Functions- These are pre-defined formulas that
are already available in MS Excel.
Commonly Used Excel
Functions
SUM – Adds all the numbers in a range of cells.
COUNT - Counts the number of cells that
contain numbers.
MIN - Returns the smallest number in a set of
values.
MAX - Returns the largest value in a set of
values.
AVERAGE - Returns the average
(arithmetic mean) of the arguments.
SUMIF – Adds the cells specified by a
given condition or criteria.
SUMIF function has the following syntax:
SUMIF(range, criteria, sum_range)
Range - The range of cells that you want to
evaluate based on a given criteria.
Criteria – It determines which cells will be
added.
Sum_Range – These are the cells
containing numeric values and the cells to
add if the condition is met.
AVERAGEIF - Returns the average
(arithmetic mean) of all the cells in a
range that meet a given criteria.
AVERAGEIF function has the following
syntax:
AVERAGEIF(range, criteria,
average_range)
Range - The range of cells that you want
to apply the criteria.
Criteria – It determines which cells to
average.
Average_Range – These are the cells
containing numeric values and the actual
set of cells to average.
COUNTIF - Counts the number of cells
within a range that meet a single
criterion that you specify.
COUNTIF function has the following
syntax:
COUNTIF(range, criteria)
Range – The range of cells to count.
Criteria – The criteria that determines
which cells to be counted.
IF – This function is one of the most popular
functions in Excel. It can perform a logical
test and returns one value if TRUE, and
another value if FALSE.
The following is the syntax of IF function:
IF(logical_test, value_if_true,
value_if_false)
Logical Test - A value or logical expression
that can be evaluated as True or False.
Value if True – The value to return if the
logical test or logical expression evaluates to
TRUE.
Value if False – The value to return if the
logical test or logical expression evaluates to
FALSE.
