Last Updated on 2021-09-23
Functions are predefined formulas in Excel. A formula is an expression that calculates values in cells.
To start a function, you have to start with “=”. And once we input some parameters, it will show us the result.
Before we get started, try to familiarize yourself with HOTKEYS.
Basic Hotkey
- Insert columns or rows : Ctrl + “+”
- Delete columns or rows : Ctrl + “-”
- Look for and replace : Ctrl + F
- Inconsecutive select : Ctrl + mouse
- Fill in downward with forluma : Ctrl + D
- Fill in rightward with forluma : Ctrl + R
- Select all : Ctrl + A
- Save file : Ctrl + S
- Close file : Ctrl + W
- Zoom in and out : Ctrl + mouse wheel
- Switch between Relative and Absolute reference : F4
Common Function
SUM
You can write like this : B3+B4+B5+B6+B7. Use the mouse to select the cells in range like
“=SUM( selected cells )”
Another way to do it is “=SUM( B3 : B7 )”
MIN
Purpose : To find out the minimum value within selected cells. Use the mouse to select the cells in range like
“=MIN( selected cells )”
Another way to do it is “=MIN( B3 : B7 )”
MAX
Purpose : To find out the maximum value within selected cells. Use the mouse to select the cells in range like
“=MAX( selected cells )”
Another way to do it is “=MAX( B3 : B7 )”
COUNT
Purpose : To count how many cells are there. Use the mouse to select the cells in range like
“=COUNT( selected cells )”
Another way to do it is “=COUNT( B3 : B7 )”
The result will be 5(B3 to B7) in this case.
COUNTA
Purpose : To count how many cells are there ( Only cells with a value that counts ). Use the mouse to select the cells in range like
“=COUNTA( selected cells )”
Another way to do it is “=COUNTA( B3 : B7 )”
AVERAGE
You can write like this : (B3+B4+B5+B6+B7)/5. Use the mouse to select the cells in range like
“=AVERAGE( selected cells )”
Another way to do it is “=AVERAGE( B3 : B7 )”
RANK
Purpose : To find out what is the ranking of the value in the selected cell. Use the mouse to select the cells in range like
“=RANK( the selected cell, reference range, ascending / descending sort )”
Another way to do it is “=RANK( B3, $B$3 : $B$7, 0 / 1 )”
PS. Ascending sort : 1 / Descending sort : 0
Advanced Function
IF
Purpose : If it meets the condition, output A result, otherwise output B result.
“= IF ( Judgement/Condition , meet the condition output A result , otherwise output B result )”
For example :
=IF( B3>=90 , “Grade A” , “Grade B” )
Then, the student is getting grade A if the number is bigger than or equal to 90, otherwise is grade B. Grade A and Grade B can also be replaced with cells that are filled with formula or even empty(it shows blank then).
However, it seems a bit unreasonable to give Bs to all the students who get grades under 90, doesn’t it? We can try to solve this by using multiple layers of IF. For example :
=IF( B3>=90 , “Grade A” , IF( B3>=80 , “Grade B” , “Grade C” ) )
And it can go on and on and on for infinity.
IFS
Now we’re thinking that there are so many IF. Is there a way to solve this formula and make it easier?
Here comes IFS :
Purpose : If it meets the condition, output A result, otherwise output B result. It’s a clean and easy function for humans to read.
“= IF ( Condition A , Output result A , Condition B , Output result B , Condition C , Output result C , otherwise result D )”
For example, we can rewrite IF to IFS as below :
= IF ( B3>90, “Grade A”, IF ( B3>80, “Grade B”, IF ( B3>70, “Grade C” , “Grade D” )))
= IFS ( B3>90, “Grade A” , B3>80 , “Grade B” , B3> 70 , “Grade C” , “Grade D” )
SUMIF
Purpose : Sum up the values among selected cells with a designated condition such as the brand’s sales number.
“= SUMIF ( Condition Range , Designated Condition , Calculate Range )”
For example :
= SUMIF ( $B$2 : $B$6 , “HP” , $C$2 : $C$6 )
Refer to the below chart, the result of the above formula will be 2600+2400=5000.
| A | B | C | |
| 1 | Date | Brand | Sales |
| 2 | 10 | Dell | 8000 |
| 3 | 11 | HP | 2600 |
| 4 | 15 | HP | 2400 |
| 5 | 16 | Lenovo | 6000 |
| 6 | 22 | ASUS | 9000 |
AVERAGEIF
Purpose : Calculate the average among selected cells with a designated condition such as the brand’s sales number.
“= AVERAGEIF ( Condition Range , Designated Condition , Calculate Range )”
For example :
= AVERAGEIF ( $B$2 : $B$6 , “HP” , $C$2 : $C$6 )
Again, refer to the below chart, the result of the above formula will be (2600+2400)/2=2500.
| A | B | C | |
| 1 | Date | Brand | Sales |
| 2 | 10 | Dell | 8000 |
| 3 | 11 | HP | 2600 |
| 4 | 15 | HP | 2400 |
| 5 | 16 | Lenovo | 6000 |
| 6 | 22 | ASUS | 9000 |
COUNTIF
Purpose : Count the number of cells among selected cells with a designated condition such as the brand’s sales number.
“= COUNTIF ( Condition Range , Designated Condition )”
For example :
= COUNTIF ( $B$2 : $B$6 , “HP” )
Refer to the below chart, the result of the above formula will be 2.
| A | B | C | |
| 1 | Date | Brand | Sales |
| 2 | 10 | Dell | 8000 |
| 3 | 11 | HP | 2600 |
| 4 | 15 | HP | 2400 |
| 5 | 16 | Lenovo | 6000 |
| 6 | 22 | ASUS | 9000 |
IFERROR
Purpose : To beautify the error message due to any reason but mostly caused by something went wrong in the formula.
“= IFERROR ( Under normal condition showing the correct result , When error messages occur showing the way you wanted )”
Referring to the below chart, D6 cell is showing an error message. We could do this for example :
= IFERROR ( B2 * C2 , “Invalid Data” ) D6 will be showing “Invalid Data”
= IFERROR ( B2 * C2 , ” ” ) D6 will be showing ” ” – empty blank
| A | B | C | D | |
| 1 | Item | Qty | Price | Invoice |
| 2 | Dell | 3 | 1200 | 3600 |
| 3 | HP | 5 | 1000 | 5000 |
| 4 | HP | 2 | 1500 | 3000 |
| 5 | Lenovo | 6 | 1300 | 7800 |
| 6 | ASUS | 7 | #DIV/0! |
VLOOKUP
Purpose : To find out the specific values we wanted from a large amount of raw data and place them in our arrangement/will.
NOTE : It is very important to know how to switch between Relative and Absolute references by using “F4”.
Absolute reference : $ was shown in front of it. Relative reference : There is no $ sign.
Since Vlookup function is kind of complicated, I’ve found a video that is very easy to understand with his explanation.
