excel google sheet function

Function List For Excel and Google Sheet

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.

ABC
1DateBrandSales
210Dell8000
311HP2600
415HP2400
516Lenovo6000
622ASUS9000

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.

ABC
1DateBrandSales
210Dell8000
311HP2600
415HP2400
516Lenovo6000
622ASUS9000

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.

ABC
1DateBrandSales
210Dell8000
311HP2600
415HP2400
516Lenovo6000
622ASUS9000

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

ABCD
1ItemQtyPriceInvoice
2Dell312003600
3HP510005000
4HP215003000
5Lenovo613007800
6ASUS7#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.


If there is any question, please comment down below and let me know.
Welcome to hit that share button if you find this helpful.
by RexyCafe

Leave a Comment

Your email address will not be published.

Scroll to Top