Tab 1 - BASICS
1. Basics to excel
How to drag the cells (dragging/ 1,2,3,4 sequence, 2,4,6,8 sequence, double click
entire row when there is a row of numbers next to it)
Formatting (format numbers: percentage etc)
Keyboard shortcuts:
Keystroke What it does
Shift + (Down, Left or right) Cell to the immediate row above/row
below/left/right
Ctrl + Shift + Down/Up Last/first occupied cell in the same column
Ctrl + Pg Up/ Pg Down Moving between different worksheets
Alt + Enter To go to the next line within the same cell
2. Formulas
COUNT (Allows you to count the number of cells containing numbers in excel)
COUNTIF (Allows you to count the number of cells which fits a specific criteria specify by
you)
Looking at the excel file which we have sent you and referring to the basic tab,
=COUNTIF(A2:A22,"apple")
This formula means that excel will count the number of cells which contains the word
“Apple”. (Not case-sensitive)
COUNTIFS (Allows you to count the number of cells which meets more than one criteria
specified by you)
Again, referring back to the excel file,
=COUNTIFS(A2:A22,"apple",C2:C22,">4000")
This formula means that excel will count the number of cells which contains the word
Apple (in Column A2 to A22), and its respective count which is more than 4000 (in
column C2 to C22).
SUM
SUMIF (Allows you to sum the value of the cells which fits a specific criteria specify by
you)
Looking at the excel file,
=SUMIF(A2:A22,"apple",C2:C22)
This formula means that excel will sum the values in column C2:C22 only if they are
apples.
SUMIFS (Allows you to sum the value of the cells which fits more than one criteria
specify by you)
SUBTOTAL (After doing a “filter”, the subtotal function will add/average (according to
what you wants it to do) those you want and ignore that is being filtered out)
IF (It is one of the most useful formula in excel. Imagine you are throwing a coin, IF the
coin shows head, you win. IF it shows tail, you lose. Therefore, it displays the “scenario”
based on a true/false situation.
For example (the coin eg again),
If the coin shows head and I win, it will be denoted by a 1. Otherwise (ie: tail), I’ll lose
and it will shows a 0.
Hence, assuming I threw the coin 10 times and I get the results shown below:
head 1
head 1
tail 0
head 1
head 1
tail 0
tail 0
head 1
head 1
head 1
Where, the highlighted cell =IF(B1="head",1,0)
NOTE: B1 = to the immediate left of the highlighted cell
Tab 4 – VLOOKUP
Remember that VLookup only look at the furthest left extreme function
TRUE for approximate (for example a range), and FALSE for exact
HLookup for horizontal
Other important things to note:
Remember to add “” if you are stating a non-numerical term in your formula.
For example, =IF(logical test, value if true, value if false)
=IF(A2=”apple”,”red”,0)
*Note that both apple and red has “ “ in front and behind it.
Remember that if you want to include a formula and link it to another cell, always remember
to include a & sign before the designated cell if there is any other text/symbol before it.
For example at cell I9 (Histogram tab),
=COUNTIF($E$8:$E$1007,"<="&H9)
Note that there is a & sign before H9 so as to let excel know that you want to count the
number of cells ONLY if they are less than or equal to the value in H9. With & removed, this
formula will not work.
Remember to “lock” your cells at a specific position using F4.
Referring to the excel file, Tab “basic”
Selling $ 5
Selling $ $5
Demand Revenue
Demand Revenue
200 =K4*$L$1
200 $1,000
300 =K5*$L$1
300 $1,500
400 =K6*$L$1
400 $2,000
500 =K7*$L$1
500 $2,500
600 =K8*$L$1
600 $3,000
Note: $5 is in cell L1
There are many other materials which we were unable to cover in class. Hopefully all of you
enjoyed the class and is able to take away something useful from the class!
If you are interested to learn more about Pivot Table (or that my explanation that day is
unclear), you can refer to this website (you can download their excel file at the bottom of
the page to practice!):
[Link]
Otherwise, you can always try your luck with youtube videos.
All the best