INTRODUCTION TO

SPREADSHEETS
What is a spreadsheet?
Forms that are used to organize
business data into rows and
columns are called spreadsheets.
An electronic spreadsheet, or
worksheet, is a computerized
version of a manual spreadsheet.
This is an
example a
manual or
paper
spreadshee
t.
 This is
an
example
an
electronic
spreadshee
t.
Manual spreadsheets are made with a pen on
paper. Electronic spreadsheets are made with a
program on a computer.
 Alternatively referred to as a
worksheet, a spreadsheet is a file
made of rows and columns that
help sort data, arrange data easily,
and calculate numerical data.
What makes a spreadsheet
software program unique is its
ability to calculate values using
 OpenOffice
Calc

LibreOffice
Calc
Where and when do we usually use a
spreadsheet software?
Although spreadsheets are typically
used with anything containing
numbers, the uses of a spreadsheet
are almost endless. Following are
some other popular uses of
spreadsheets.
Finance.
Spreadsheets are
ideal for financial
data, such as your
checking account
information,
budgets,
transactions, billing,
invoices, receipts,
Forms. Form
templates can be
created to handle
inventory,
evaluations,
performance
reviews, quizzes,
time sheets,
patient
School and Grades.
Teachers can use
spreadsheets to
track students,
calculate grades,
and identify
relevant data,
such as high and
low scores,
missing tests, and
Lists. Managing alist
in a spreadsheet is a
great example of
data that does not
contain numbers, but
still can be used in a
spreadsheet.
Great examples of
spreadsheet lists
include telephone,
Sports. Spreadsheets
can keep track of your
favorite player stats or
stats on the whole
team. With the
collected data, you can
also find averages, high
scores, and other
statistical data.
Spreadsheets can even
 FEATURES OF
SPREADSHEET
SOFTWARE
 The greatest labor-saving aspect
of the electronic spreadsheet is
automatic recalculation:
“When one value or calculation in a
spreadsheet is changed, all dependent
values on the spreadsheet are
automatically recalculated to reflect the
change.”
“What-if” analysis is the process of
changing one or more spreadsheet
values and observing the resulting
calculated effect.
The intersection
of a row and
column forms a
cell. The letter
and number of
the intersecting
column and row A2
B1 Cells

is the cell C4
 In order to work with a
cell, you have to select it to
make it an active cell.
You select a cell to enter data
into it, edit it, move or copy it,
or perform an action on it.
 Active Cell

The active
cell,
or current
cell, is the
cell in
which you
can type
data.
 If you want Cell Range Reference
Reference B3:B7
to work with
more than one
cell at a time,
you must first
selectthe cells
as a range. A
range is a group Range Active Cell
Each cell can
contain one of
three types of
information:
1.Label provides
descriptive
information about
entries in the
spreadsheet;
2. Value is an actual
3. Formula is an instruction to the
program to perform a calculation.
Formula will be the content

The result will be the displayed value

Functions are built-in
formulas in excel that you
can use as a quick way of
performing a task that
would usually take more
time if a formula were
used.
Cell Content (A
function)

Displayed Value.
The result of a
formula or
function.
 Formulas and functions do not
appear in the cells; instead, the
cell shows the result of the
formula or function. The
result is called
the displayed value of the cell.
The formula or function is
Cell Content

Displayed Value
On the image above can you
identify:
1. the labels? 5. the cell content of the current
2. the values? cell?
3. the 6. the cell address of Store B
functions? monitor?
 QUESTION:
Which of the following is the most
popular example of a spreadsheet
software?
A. OpenOffice Calc
B. Google Sheets
C. LibreOffice Calc
D. MS Excel
Spreadsheets have certainly
aided the computer industry
for business purposes, with
the most popular example
being the Microsoft Excel
application.
The Microsoft Excel (2016)
 Control Bar

Groups Quick Access Tabs Title Bar

Toolbar Column Header
Ribbon
Address Formula Bar
Bar
Active Cell Workbook
Row Header Window

Sheet Tab Scroll Bars

Status Bar
EXCEL is more than just a grid in
which you enter numbers in
columns or rows. Yes, one of the
most powerful features in Excel is
the ability to calculate numerical
information using formulas.
Just like a calculator, Excel can
add, subtract, multiply, and
 Most common arithmetic operators:
Arithmetic operator Meaning Example
+ (plus sign) Addition 3+3
Subtracti 3–1
– (minus sign)
on –1
Negation
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2
For example, the following
formula multiplies 2 by 3 and
then adds 5 to that result to
come up with the answer of
11.
=2*3+5
 All formulas in Excel must
begin with an equals sign
(=). This is because the cell
contains, or is equal to, the
formula and the value it
calculates.
While you can create simple formulas in
Excel manually (for example, =2+2 or
=5*5), most of the time you will use cell
addresses to create a formula. This is
known as making a cell reference or cell
referencing. Using cell references will
ensure that your formulas are always
accurate because you can change the
value of referenced cells without having
Active Cell Cell Content Constant

Displayed Value
By combining a
mathematical
operator with cell
references, you can
create a variety of
simple formulas in
Excel. Formulas can
also include a
combination of cell
references and
 COMPLEX FORMULAS
A simple formula is a mathematical
expression with one operator, such as
7+9. A complex formula has more than
one mathematical operator, such as
=5+2*8. When there is more than one
operation in a formula, the order of
operations tells Excel which
operation to calculate first. In order to
use Excel to calculate complex formulas,
SIMPLE FORMULA

COMPLEX FORMULA
Excel calculates formulas based
on the following order of
operations:
1. Operations enclosed in
parentheses
2. Exponential calculations
(3^2, for example)
3. Multiplication and division,
whichever comes first
P – Parenthesis
E – Exponential
M – Multiplication
D – Division
A – Addition
S – Subtraction
G – Group
E – Exponential
M – Multiplication
D – Division
A – Addition
S – Subtraction
 FUNCTIONS
A function is a predefined formula
that performs calculations using
specific values in a particular order.
Excel includes many common
functions that can be useful for
quickly finding the sum, average,
count, maximum value, and
 FUNCTIONS
In order to use functions correctly,
you'll need to understand the
different parts of a function and how
to create arguments to calculate
values and cell references.
In order to work correctly, a function
must be written a specific way, which
is called the syntax. The basic syntax
for a function is:
1. the equals sign (=)
2. the function name (SUM, for
example)
3. one or more arguments.
The
function in
the
example
beside
would add
the values
of the cell
Arguments can
refer to both
individual cells and
cell ranges and
must be enclosed
within
parentheses.
You can include one
argument or
multiple
 Excelhas a variety of
functions available. Following
are some of the most
common functions you'll use:
1. SUM:
This
function
adds all
of the
values of
the cells
in the
2. AVERAGE: =AVERAGE(C3:C12)
This
function
determines the
average of the
values included in
the argument. It
calculates the
sum of the cells
and then divides
that value by the
3. COUNT:
This
function
=COUNT(C3:C12)
counts the
number of
cells with
numerical data
in the
argument.
This function
is useful for
4. MAX: This
function =MAX(C3:C12)
determines
the highest
cell value
included in
the
argument.
5. MIN:
This
function =MIN(C3:C12)
determine
s the
lowest cell
value
included
The AutoSum
command allows you to
automatically insert the
most common
functions into your
formula, including SUM,
 The Function Library
While there are hundreds of
functions in Excel, the ones you use
most frequently will depend on the
type of data your workbooks
contains. There is no need to learn
every single function, but exploring
some of the different types of
 The Function Library
You can search for functions by category,
such as Financial, Logical, Text, Date &
Time, and more from the Function Library
on the Formulas tab.
 Hover the mouse
over the cell that
contains the function,
then click, hold,
and drag the fill handle
The function
over the cellswill
yoube
want
Fill Handle
copied,
to fill. and values for
those cells will be
calculated relative to
 Click, hold,
and drag the
Fill Handle to
copy the
functions or
formulas to
adjacent
cells.
 The COUNT Function
The COUNT function counts the
number of cells that contain
numbers, and counts numbers
within the list of arguments. Use the
COUNT function to get the number
of entries in a number field that is in
a range or array of numbers.
COUNTA.
Counts =COUNTA(C3:C12)
the
number
of cells in
a range
that are
not
 =COUNTBLANK(C3:C12)

COUNTBLANK.
Counts the
number of
empty cells
in a specified
range of
cells.
COUNTIF
C
. ounts
the =COUNTIF(C3:C12, “INC”)
number
of cells
within a
range
that
 =COUNTIFS(E4:E15,“>500”,E4:E15,“<1000”)
COUNTIFS.
Counts the
number of
cells
specified
by a given
set of
conditions
VLOOKUP

VLOOKU
P
VLOOKUP is one the most
widely used functions in Excel.
In a big data sheet, it lets you
look up a value in a column on
the left, then returns
information in another column
to the right if it finds a match.
The formula for VLOOP is as
follows:
VLOOKUP
Function
1. First, you need data on
your Excel. In this example, you
follow this data to look up for
fruits.
2. In cell B7, enter
=VLOOKUP(A7,A2:B5,2,FALSE).
The correct answer for Apples is
50. VLOOKUP looked for Apples,
found it, then went over one
column to the right, and returned
the amount.
VLOOKUP AND
#N/A
Invariably, you will run into a
situation where VLOOKUP can't find
what you asked it to, and it returns
an error (#N/A). Sometimes, it is
because the lookup value simply
does not exist, or it can be that the
reference cell does not have a value
yet. Look on this data on Excel.
There is nothing wrong in the
VLOOKUP formula in the formula bar
but there is #N/A on cell B8, because
there is the reference cell A8 does
not have anything to look up in cells
A2:B6.
VLOOKUP AND
#N/A
1. If you know your lookup
value exists, but want to hide the
error if the lookup cell is blank, you
can use an IF statement. In this case,
wrap your existing VLOOKUP formula
like this in cell B8:
=IF(B8="","",VLOOKUP(B8,A2:B6,2,F
ALSE))
2. This says, "If cell C43 equals
nothing (""), then return nothing,
otherwise return the VLOOKUP's
results." Note the second closing
parenthesis at the end of the
formula. This closes the IF
VLOOKUP AND
#N/A
3. If you're not sure your
lookup value exists, but you still
want to suppress the #N/A error, you
can use an error handling function
called IFERROR in cell G43:
=IFERROR(VLOOKUP(F43,F37:G41,2,
FALSE),""). IFERROR says, "If the
VLOOKUP returns a valid result, then
display that, otherwise, display
nothing ("")". We displayed nothing
here (""), but you can also use
numbers (0,1, 2, etc.), or text, such
as "Formula isn't correct".