EXCEL 101
Prepared by : Mharz Maglinte
February 03, 2012
EXCEL TIPS AND TIDBITS
SPREADSHEET, WORKSHEET and WORKBOOK.
• Spreadsheet = computer program / computer application
• Workbook = multiple/collection of Worksheet in one File or simply, your
EXCEL FILE
• Worksheet = compilation of multiple cells
Putting it together : a spreadsheet program, such as Excel, is used to
create workbook files that contain one or more worksheets containing data.
You can use + or = sign to start a formula.
If you want to select the contents of an entire row quickly, click on any cell in the row
and then press “shift” and “spacebar”.
Vlookup TRUE = close or approximate value = 1;
Vlookup FALSE = exact value = 0
Dollar Sign ($) is a string/wildcard use to change relative cell references to absolute
cell reference. This will prevent the formula from changing either row or column
reference or both when the formula is copied to another cell.
SUM, COUNT and AVERAGE of selected cell range are shown on the bottom left
TABLE OF CONTENTS
KEYBOARD SHORTCUTS
PASTE SPECIAL
SORTING, FILTERING and SUBTOTALING
KEYBOARD SHORTCUTS
KEYBOARD SHORTCUTS
KEYBOARD SHORTCUTS
FORMULA
IF FORMULA
SYNTAX
=IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)
is any value or expression
that can be evaluated as
TRUE or FALSE.
is the value that is returned if
logical_test is TRUE.
is the value that is returned if
logical_test is FALSE.
IF FORMULA Example
SYNTAX
=IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)
is any value or expression
that can be evaluated as
TRUE or FALSE.
is the value that is returned if
logical_test is TRUE.
is the value that is returned if
logical_test is FALSE.
IF FORMULA Example
Scenario :
Compute for the total valuation of the current inventory of a given month.
However, a negative ending inventory should have zero value.
IF FORMULA Example
IF FORMULA Example
G9 is the cell containing the
inventory being evaluated
whether less than or equal to
zero
IF FORMULA Example
is the action to be taken if the
value in the logical test is TRUE
If SOH or END INVTY is less than
or equal to zero, then C9 (which is
the STD COST) will be multiplied to
the absolute value indicated
which is zero.
IF FORMULA Example
is the action to be taken if the value in
the logical test is FALSE
If SOH or END INV is NOT less than or
equal to zero, then C9 (which is the STD
COST) will be multiplied to G9 which
contains the SOH / END INV
IF FORMULA Example
Why C9 contains $ sign?
Answer : So that when we copy the
formula to other valuation column, the
formula will still refer to column C which
contains the standard cost.
Why G9 does not contains $ sign?
Answer : So that when we copy the formula to
other valuation column, the formula will refer
to the column before it which is always the
END INV of a particular month
IF AND ISERROR COMBINATION
Example
is any value or expression
that can be evaluated as
SYNTAX TRUE or FALSE.
=ISERROR(LOGICAL_TEST)
=IF(ISERROR(LOGICAL_TEST),VALUE_IF_TRUE,VALUE_IF_FALSE)
is the value that is returned if
logical_test is TRUE. is the value that is returned if
logical_test is FALSE.
COUNT FORMULA
SYNTAX
=COUNT(Value Range)
Remarks
Arguments (or Cell Values) that are numbers, dates, or text representation of
numbers are counted.
Logical values and text representations of numbers that you type directly
into the list of arguments are counted (such as dates in text format) but error
values or text that cannot be translated into numbers are ignored.
If an argument is an array or reference, only numbers in that array or
reference are counted. Empty cells, logical values, text, or error values in the
array or reference are ignored.
If you want to count logical values, text, or error values, use the COUNTA
function.
COUNTIF FORMULA
SYNTAX
=COUNTIF(RANGE,CRITERIA)
Range of Cell that you
want to be counted
Criteria that defines which cells in
the range are to be counted
COUNTIFS FORMULA
SYNTAX
=COUNTIFS(CRITERIA_RANGE1,CRITERIA1,C
RITERIA_RANGE2, CRITERIA2...)
Range of Cell that you
want to be counted
Criteria that defines which cells in
the range are to be counted
COUNT FAMILY FORMULA Example
NOTE :
For countif and countifs, empty cells are considered as zero and are counted provided
criteria are met.
COUNTIFS applies criteria to cells across multiple ranges and counts the number of
times ALL criteria are met.
Each additional range must have the same number of rows and columns as the
criteria_range1 argument. The ranges do not have to be adjacent to each other.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in
criteria. A question mark matches any single character, and an asterisk matches any
sequence of characters. If you want to find an actual question mark or asterisk, type a
tilde (~) before the character.
APPLICATION
Counting of SKUs given numerical or alphanumerical values
Counting of SKUs per certain category/criteria without the need to sort the entire
worksheet
SUMIF FORMULA
SYNTAX
=SUMIF(RANGE,CRITERIA,SUM_RANGE)
Range of Cells that you
want to be evaluated
Criteria where the range is
being compared with
The actual cells to be added
Note : Any text criteria or any criteria that includes logical or mathematical symbols
must be enclosed in double quotation marks ("). If the criteria is numeric, double
quotation marks are not required.
SUMIF FORMULA Example
APPLICATION
Use the SUMIF function to sum the values in a range (range: Two or more cells on a
sheet. The cells in a range can be adjacent or nonadjacent.) that meet certain criteria
that you specify.
For multiple criteria (more than one), you can use the SUMIFS function.
SUMIFS FORMULA
The first range in which to
Range of Cells that you evaluate the associated
want to be added criteria
SYNTAX
=SUMIFS(sum_range, criteria_range1,
criteria1, criteria_range2, criteria2, ...)
Criteria where the Criteria where the
criteria_range1 is being criteria_range2 is being
compared with compared with
The second criteria range in
which to evaluate the associated
criteria (criteria2)
SUMIFS FORMULA Example
The order of arguments differ between the SUMIFS and SUMIF functions. In
particular, the sum_range argument is the first argument in SUMIFS, but it is
the third argument in SUMIF. If you are copying and editing these similar
functions, make sure you put the arguments in the correct order.
Each cell in the sum_range argument is summed only if all of the
corresponding criteria specified are true for that cell. For example, suppose
that a formula contains two criteria_range arguments. If the first cell of
criteria_range1 meets criteria1, and the first cell of criteria_range2 meets
critera2, the first cell of sum_range is added to the sum, and so on, for the
remaining cells in the specified ranges.
Cells in the sum_range argument that contain TRUE evaluate to 1; cells in
sum_range that contain FALSE evaluate to 0 (zero).
Unlike the range and criteria arguments in the SUMIF function, in the
SUMIFS function, each criteria_range argument must contain the same
number of rows and columns as the sum_range argument.
Quick Access Toolbar
Ribbon Tab
Ribbon
UNDERSTANDING THE FORMULA
UNDERSTANDING THE FORMULA
SYNTAX (of EXCEL functions) refers to the layout and order of the
FUNCTIONS and its arguments. ARGUMENTS are values used for
calculation. It could be in a form of number, text or logical values . All excel
functions should starts with the EQUAL SIGN.
UNDERSTANDING THE FORMULA
SYNTAX (of EXCEL functions) refers to the layout and order of the
FUNCTIONS and its arguments. ARGUMENTS are values used for
calculation. It could be in a form of number, text or logical values . All excel
functions should starts with the EQUAL SIGN.
UNDERSTANDING THE FORMULA
SYNTAX (of EXCEL functions) refers to the layout and order of the
FUNCTIONS and its arguments. ARGUMENTS are values used for
calculation. It could be in a form of number, text or logical values . All excel
functions should starts with the EQUAL SIGN.
EQUAL
SIGN
UNDERSTANDING THE FORMULA
SYNTAX (of EXCEL functions) refers to the layout and order of the
FUNCTIONS and its arguments. ARGUMENTS are values used for
calculation. It could be in a form of number, text or logical values . All excel
functions should starts with the EQUAL SIGN.
=IF
FUNCTION
UNDERSTANDING THE FORMULA
SYNTAX (of EXCEL functions) refers to the layout and order of the
FUNCTIONS and its arguments. ARGUMENTS are values used for
calculation. It could be in a form of number, text or logical values . All excel
functions should starts with the EQUAL SIGN.
=IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)
ARGUMENTS
LOGICAL TEST : The user-defined condition that is to be tested and evaluated
as either true or false
VALUE IF TRUE : The result that is to be returned if logical test is TRUE
VALUE IF FALSE : The result that is to be returned if logical test is FALSE
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 2 which is lower than 10, then the
2 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
Since A1 = 11 which is higher than 10, then the
11 =IF(A1>10,"LOWER","HIGHER") condition is TRUE. So the formula will give you the
word : LOWER
LOWER
Since A1 = 9 which is lower than 10, then the
9 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 2 which is lower than 10, then the
2 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
Since A1 = 11 which is higher than 10, then the
11 =IF(A1>10,"LOWER","HIGHER") condition is TRUE. So the formula will give you the
word : LOWER
LOWER
Since A1 = 9 which is lower than 10, then the
9 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 2 which is lower than 10, then the
2 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
Since A1 = 11 which is higher than 10, then the
11 =IF(A1>10,"LOWER","HIGHER") condition is TRUE. So the formula will give you the
word : LOWER
LOWER
Since A1 = 9 which is lower than 10, then the
9 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 2 which is lower than 10, then the
2 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
Since A1 = 11 which is higher than 10, then the
11 =IF(A1>10,"LOWER","HIGHER") condition is TRUE. So the formula will give you the
word : LOWER
LOWER
Since A1 = 9 which is lower than 10, then the
9 =IF(A1>10,"LOWER","HIGHER") condition is FALSE. So the formula will give you
the word : HIGHER
HIGHER
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 10 which is not lower than 10, then
10 =IF(A1>10,"LOWER","HIGHER") the condition is FALSE. So the formula will give
you the word : HIGHER
HIGHER
Since A1 = 10 which is neither lower nor higher
=IF(A1>10,"LOWER",IF(A1=10, than 10, then the result would be the word
10 "CORRECT","HIGHER") "CORRECT" to indicate that the given number
CORRECT
matches what we are looking for.
UNDERSTANDING THE FORMULA
=IF(A1>10, “LOWER”, “HIGHER”)
A1 VALUES FORMULA DESCRIPTION RESULT
Since A1 = 10 which is not lower than 10, then
10 =IF(A1>10,"LOWER","HIGHER") the condition is FALSE. So the formula will give
you the word : HIGHER
HIGHER
Since A1 = 10 which is neither lower nor higher
=IF(A1>10,"LOWER",IF(A1=10, than 10, then the result would be the word
10 "CORRECT","HIGHER") "CORRECT" to indicate that the given number
CORRECT
matches what we are looking for.
UNDERSTANDING THE FORMULA
UNDERSTANDING THE FORMULA
=SUM(A1:B5)
UNDERSTANDING THE FORMULA
=SUM(A1:B5)
Equal Sign
UNDERSTANDING THE FORMULA
=SUM(A1:B5)
Function
UNDERSTANDING THE FORMULA
=SUM(A1:B5)
Arguments
UNDERSTANDING THE FORMULA
=SUM(A1:B5)
The layout and order of the formula
Syntax parts which are the equal sign,
functions and arguments
VLOOKUP FORMULA
SYNTAX
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,[RANGE_LOOKUP])
VLOOKUP FORMULA
SYNTAX
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,[RANGE_LOOKUP])
is any value or
expression that you
search.
VLOOKUP FORMULA
SYNTAX
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,[RANGE_LOOKUP])
is a range of cells (table)
that contains the data.
VLOOKUP FORMULA
SYNTAX
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,[RANGE_LOOKUP])
is the column number from
the table array where data
is located .
VLOOKUP FORMULA
SYNTAX
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COLUMN_INDEX_NUMBER,[RANGE_LOOKUP])
Type of match
EXAMPLE
Item Code Description SOH
2000213 TBC, TCLEAN 250ML LEMON 18,340
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 16,456
2000215 TBC, TCLEAN 250ML ORANGE 26,220
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 27,235
Item Code Description Estimate
2000213 TBC, TCLEAN 250ML LEMON 6,080
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 4,760
2000215 TBC, TCLEAN 250ML ORANGE 4,400
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 5,760
Item Code Description Sales Qty
2000213 TBC, TCLEAN 250ML LEMON 5,974
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 4,736
2000215 TBC, TCLEAN 250ML ORANGE 3,329
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 4,825
Item Code Description SOH Estimate Sales
2000213 TBC, TCLEAN 250ML LEMON 18,340 6,080 5,974
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 16,456 4,760 4,736
2000215 TBC, TCLEAN 250ML ORANGE 26,220 4,400 3,329
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 27,235 5,760 4,825
EXAMPLE
Item Code Description SOH
2000213 TBC, TCLEAN 250ML LEMON 18,340
2000214 TBC, TCLEAN 250ML FRESHBOUQUET -
2000215 TBC, TCLEAN 250ML ORANGE 26,220
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 27,235
Item Code Description Estimate
2000213 TBC, TCLEAN 250ML LEMON 6,080
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 5,760
2000215 TBC, TCLEAN 250ML ORANGE -
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 4,760
Item Code Description Sales Qty
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 4,825
2000215 TBC, TCLEAN 250ML ORANGE 3,329
2000214 TBC, TCLEAN 250ML FRESHBOUQUET 4,736
2000213 TBC, TCLEAN 250ML LEMON
Item Code Description SOH Estimate Sales
2000213 TBC, TCLEAN 250ML LEMON 18,340 6,080 -
2000214 TBC, TCLEAN 250ML FRESHBOUQUET - 4,760 4,736
2000215 TBC, TCLEAN 250ML ORANGE 26,220 - 3,329
2000216 TBC, TCLEAN 250ML MINTXTRA STRENGTH 27,235 5,760 4,825
VLOOKUP :
WHY IS IT NOT WORKING?
Exact match vs Approximate match
Relative vs Absolute table references
Incorrect column number reference
Incorrect table array
Looking at the left side
Data has duplicates
Different data format
Extra space at the end of the data (lookup value)
VLOOKUP WITH MULTIPLE CRITERIA
VLOOKUP WITH MULTIPLE CRITERIA
VLOOKUP WITH MULTIPLE CRITERIA
