[PDF]101 Excel Functions

101 EXCEL
FUNCTIONS


: You Should Know |
Ili


PÀ PDF GUIDE


TABLE of Contents


101 Excel Functions. <4 4 5.csncn ates Pe ehe heb Rb ees ob bee Seen de 2
Date and Time FunctionS. «cosas cturac dead oun ased suena wee 3
PNGINEGING>.¢c4 5 aoe ee wee eset sedate tee ee ee Eee ee 9
Information Functions ...........0 0c cee ee eee 10
Logical Functions. ... s.an aaaea sn ee teices a. ee hws aes ares Mente se ee 11
Lookup and Reference Functions ........ 0.0.0.0: cece eee eens 13
STATISTICAL Functions. ... dc otek a bk bk eh ae eo ed cee kk ee a 19
MATH Functions ina wkd eae ee Shoe ee ee heed oe bee eee ke hed 24
TEXT Functions isk anaana anaa aes 8. bia eeabe wed Soe Rea adws 28
Dynamic Array Functions. »tec22%6saveleagnussew eaaebdeaneba ws 33
More Resources. 544 oats Fas din eee teak ea asa kee cae weds 34
Copyright


No part of this publication shall be reproduced, or sold in whole or in part
in any form, without the prior written consent of the author. All trade-
marks and registered trademarks appearing in this guide are the property
of their respective owners.


© 2021 Exceljet. All rights reserved.


https://exceljet.net/


Published: 2021-04-14 09:59:45


101 EXCEL FUNCTIONS


Quick Navigation
ABS AGGREGATE AND


AVERAGE AVERAGEIF
AVERAGEIFS CEILING
CHAR CHOOSE CLEAN
CODE COLUMN COLUMNS
CONCAT CONCATENATE
CONVERT COUNT COUNTA
COUNTBLANK COUNTIF
COUNTIFS DATE DATEDIF
DAY EDATE EQMONTH
EXACT FILTER FIND
FLOOR GETPIVOTDATA
HLOOKUP HOUR
HYPERLINK IF |IFERROR
IFNA IFS INDEX INDIRECT
INT ISBLANK ISERROR
ISEVEN ISFORMULA
ISLOGICAL ISNUMBER
ISODD ISTEXT LARGE
LEFT LEN LOOKUP LOWER
MATCH MAX MAXIFS
MID MIN MINIFS MINUTE
MOD MODE MONTH
MROUND NETWORKDAYS
NOT NOW OFFSET OR
PROPER RAND RANDARRAY
RANDBETWEEN RANK
REPLACE RIGHT ROUND
ROUNDDOWN ROUNDUP
ROW ROWS SEARCH
SECOND SEQUENCE
SMALL SORT SORTBY
SUBSTITUTE SUBTOTAL
SUM SUMIF SUMIFS
SUMPRODUCT TEXT
TEXTJOIN TIME TODAY
TRANSPOSE TRIM

UNIQUE UPPER VLOOKUP
WEEKDAY WEEKNUM
WORKDAY XLOOKUP
XMATCH YEAR YEARFRAC


Table of Contents


101 Excel Functions


Excel has over 480 built-in functions, and more are still being added. That
is a huge number of functions to think about, even for advanced users.
Thankfully, you don’t need to learn all of these functions to be productive
in Excel. If you have a basic understanding of about a hundred key func-
tions, you’ll be far ahead of the average user.


This document contains a brief overview of about 100 important Excel
functions you should know, with links to detailed examples. It is based on a
more complete list of Excel functions here.


Excel Function List


Excel functions by category. Optional arguments in white. Click any function for a detailed description with formula
examples. Also see 500 Formulas and 101 Functions.


Search for functions here ( Fior ]
Logical

AND Test multiple conditions with AND (lesicatz)(—)
FALSE Generate the logical value FALSE

IF Test for a specific condition

IFERROR Trap and handle errors

IFNA Trap and handle #N/A errors | value J valve it.ne |

IFS Test multiple conditions, return first true [tests f values | o
NOT Reverse arguments or results ga

OR Test multiple conditions with OR Fea) (=)
SWITCH Match multiple values, return first match expre alt /resultt
TRUE Generate the logical value TRUE

XOR Perform exclusive OR


Excel Function List


We also have a large list of example formulas and video training. If you are
new to Excel formulas and how functions are used, see this introduction.


101 EXCEL FUNCTIONS


Quick Navigation
ABS AGGREGATE AND
AVERAGE AVERAGEIF
AVERAGEIFS CEILING
CHAR CHOOSE CLEAN
CODE COLUMN COLUMNS
CONCAT CONCATENATE
CONVERT COUNT COUNTA
COUNTBLANK COUNTIF
COUNTIFS DATE DATEDIF
DAY EDATE EOMONTH
EXACT FILTER FIND
FLOOR GETPIVOTDATA
HLOOKUP HOUR
HYPERLINK IF IFERROR
IFNA IFS INDEX INDIRECT
INT ISBLANK ISERROR
ISEVEN ISFORMULA
ISLOGICAL ISNUMBER
ISODD ISTEXT LARGE
LEFT LEN LOOKUP LOWER
MATCH MAX MAXIFS
MID MIN MINIFS MINUTE
MOD MODE MONTH
MROUND NETWORKDAYS
NOT NOW OFFSET OR
PROPER RAND RANDARRAY
RANDBETWEEN RANK
REPLACE RIGHT ROUND
ROUNDDOWN ROUNDUP
ROW ROWS SEARCH
SECOND SEQUENCE
SMALL SORT SORTBY
SUBSTITUTE SUBTOTAL
SUM SUMIF SUMIEFS
SUMPRODUCT TEXT
TEXTJOIN TIME TODAY
TRANSPOSE TRIM

UNIQUE UPPER VLOOKUP
WEEKDAY WEEKNUM
WORKDAY XLOOKUP
XMATCH YEAR YEARFRAC


Table of Contents


one 1
. a * a
+ ad P xx Z
NB teers
s; =y aS wre

+. a H ote

i) . a

age


)

39s
T

$

ek


Date and Time Functions


Excel provides many functions to work with dates and times.


NOW and TODAY

You can get the current date with the TODAY function and the current date
and time with the NOW Function. Technically, the NOW function returns
the current date and time, but you can format as time only, as seen below:


c4 X fe | =TODAY()
(| oA B c D E F G i
m
2
3
4 topay | 15-Nov-2018]
5
6 NOW 1:37 PM
7


() // returns current date Easily add Date
() // returns current time and Time to your
Excel files using


these functions


Note: these are volatile functions and will recalculate with every work-
sheet change. If you want a static value, use date and time shortcuts.


DATE SHORTCUT: This shortcut will insert the current date as a fixed
value; it will not change.


Windows shortcut Mac shortcut
een Ee


TIME SHORCUT: This shortcut will insert the current time as a fixed value;
it will not change. [Note: In Mac 2016, Control Shift : stopped working to
insert a time. Command ; now seems to work.]


Windows shortcut Mac shortcut


oma on


More excel shortcuts.


P xx = La 2


Aer


a


DAY, MONTH, YEAR, and DATE Did you know?
You can use the DAY, MONTH, and YEAR functions to disassemble any
date into its raw components, ‘and the DATE function to put things back Excel dates are serial
together again. numbers that start in
the year 1900.
H5 ~ fe | =DATE(D5,E5,F5)
oo p E = i k eL H | i Excel times are fractions
Fi of the number 1.
3]
4 | Date YEAR MONTH DAY DATE EEES eae
x PEET Oii P 14 > [irnos] Both dates and times
6 | 23-Apr-12 2012 4 23 23-Apr-12 are numbers that can be
7 | 20-Feb-00 2000 2 20 20-Feb-00 used in math operations.
8| 4-Oct-95 1995 10 4 4-Oct-95
g |
10
TE


("14-Nov-2018") // returns 14
("14-Nov-2018") // returns 11


("14-Nov-2018") // returns 2018
(2018,11,14) // returns 14-Nov-2018


HOUR, MINUTE, SECOND, and TIME


Excel provides a set of parallel functions for times. You can use the HOUR,
MINUTE, and SECOND functions to extract pieces of a time, and you can


assemble a TIME from individual components with the TIME function.
H5 7 fe =TIME(D5,E5,F5)
| A B c D E F G H I
1 |
>
3 |
4 | Date HOUR MINUTE SECOND TIME
5 | 10:00 AM 10 0 0 [10:00 aml
6 | 11:30 AM 11 30 0 11:30 AM
Z| 3:05:02 3 5 2 3:05:02
8 | 5:15 PM 17 15 0 5:15 PM
3
10 |
11|


12


("10:30") // returns 10
("10:30") // returns 30


("10:30") // returns @
(10,30,0) // returns 10:30


DATEDIF and YEARFRAC


You can use the DATEDIF function to get time between dates in years,
months, or days. DATEDIF can also be configured to get total time in “nor-


malized” denominations, i.e. “2 years and 5 months and 27 days”.


The DATEDIF function is a good way to calculate age from a birthday. See


this example formula.


E5 X f=- | =DATEDIF(B5,C5,"y")

4,A| B c |o E BG CHS N E E;
1 i

2

3 4

4 Date 1 Date 2 Years _ Months Days Years Months Days
s| 14-Nov-18 10-Jun-21 30 939 2 «6

6 | 23-Apr-12 17-Oct-13 1 17 542 1 5

7 | 20-Feb-00 11-May-08 8 98 3003 8 2
8| 4-Oct-95 1-Mar-12 16 196 5993 16 4

9 _

10 |

11


Use YEARFRAC to get fractional years:


m
uw
4


fe | =YEARFRAC(B5,C5)


27
24
21
26


4d a| B c | o EE e | e |
1)

2 F Af year t t

3 |

4 Date 1 Date 2 YEARFRAC

5 | 14-Nov-18 10-Jun-21 2.6]
6 | 23-Apr-12! 17-Oct-13 15
7 | 20-Feb-00 11-May-08 8.2
8 | 4-Oct-95 1-Mar-12| 16.4
9 |

10

11


("14-Nov-2018" , "10-Jun-2021" )


EXCEL FUNCTIONS


Quick Navigation
ABS AGGREGATE AND


AVERAGE AVERAGEIF
AVERAGEIFS CEILING
CHAR CHOOSE CLEAN
CODE COLUMN COLUMNS
CONCAT CONCATENATE
CONVERT COUNT COUNTA
COUNTBLANK COUNTIF
COUNTIFS DATE DATEDIF
DAY EDATE EOMONTH
EXACT FILTER FIND
FLOOR GETPIVOTDATA
HLOOKUP HOUR
HYPERLINK IF IFERROR
IFNA IFS INDEX INDIRECT
INT ISBLANK ISERROR
ISEVEN ISFORMULA
ISLOGICAL ISNUMBER
ISODD ISTEXT LARGE
LEFT LEN LOOKUP LOWER
MATCH MAX MAXIFS
MID MIN MINIFS MINUTE
MOD MODE MONTH
MROUND NETWORKDAYS
NOT NOW OFFSET OR
PROPER RAND RANDARRAY
RANDBETWEEN RANK
REPLACE RIGHT ROUND
ROUNDDOWN ROUNDUP
ROW ROWS SEARCH
SECOND SEQUENCE
SMALL SORT SORTBY
SUBSTITUTE SUBTOTAL
SUM SUMIF SUMIFS
SUMPRODUCT TEXT
TEXTJOIN TIME TODAY
TRANSPOSE TRIM

UNIQUE UPPER VLOOKUP
WEEKDAY WEEKNUM
WORKDAY XLOOKUP
XMATCH YEAR YEARFRAC


Table of Contents


EDATE and EOMONTH IN Į | TUES
D
A common task with dates is to shift a date forward (or backward) by a 5 — P.
given number of months. You can use the EDATE and EOMONTH functions SUNDay
for this. EDATE moves by month and retains the day. EOMONTH works the SEPTI
SATL


same way, but always returns the last day of the month.


VE
"UESDay Raa
A


E5 ~ fe | =EDATE(B5,C5)
A A | B e] D E F | G H
L A
2 Shift dates forward or backward bu months JU L 2
: VE
=] SUNDAY
2 sarun E
4 Start Months EDATE EOMONTH
5 20-Mar-2010. 6 m l 20-Sep-2010] 30-Sep-2010 ay
11-Aug-2013 -6 SA 11-Feb-2013 28-Feb-2013 MA


|


30-Nov-2015 12; - 30-Nov-2016 30-Nov-2016 FRID
_ 10-Jan-2017, o -= = 10-Jan-2017) 31-Jan-2017 APRIL
7-May-2018 z 7-Feb-2019 28-Feb-2019
19-Jul-2020 24| => 19-Jul-2022! 31-Jul-2022


A
(date,6) i ji su)
(date,6) : ' '


~NE
"UESDay SEPTEMBER


D SATURDAY A
la A At
=i È


8 ejes]
w
=
=
[=]
N9 £
DdD
x
i E R
r
v 4
TA
m
p5)


aren
py


Shift dates Forward
(or Backward)
using EDATE


VIDEO


How to highlight
expiration dates


101 EXCEL FUNCTIONS


WORKDAY and NETWORKDAYS


To figure out a date n working days in the future, you can use the


WORKDAY function. To calculate the number of workdays between two


dates, you can use NETWORKDAYS.


E5 v fe | =WORKDAY(B5,C5,holidays)

A A B | C D E l Ej G H
1 |

2 t kday past

3

4 Start Days WORKDAY Holidays

5 | Mon, 6-May-2019 5 | Mon, 13-May-2019] 27-May-2019

6 | Mon, 6-May-2019 10 Mon, 20-May-2019 4-Jul-2019

7 | Sat, 1-Jun-2019 30 Mon, 15-Jul-2019

8 | Fri, 10-May-2019 415 Mon, 3-Jun-2019 holidays = G5:G6
9 | Fri, 10-May-2019 -5 Fri, 3-May-2019

10|

11|

12


(start,n,holidays) =] i


E5 a o fe =NETWORKDAYS(B5,C5, holidays)

A A | B c B E F | G

1 |

2 set number of Workaaus tween Té

3 =|

4 Start Finish Workda Holidays

5 | Mon, 6-May-2019 Mon, 13-May-2019 — | el 27-May-2019
6 | Mon, 6-May-2019 Mon, 20-May-2019 — 11 4-Jul-2019
a Sat, 1-Jun-2019 Mon, 15-Jul-2019. —- 30

8 | Fri, 10-May-2019 Mon, 3-Jun-2019 16 holidays = E5:E6
9 | Fri, 10-May-2019 Fri, 3-May-2019 -6

10|

11|

12|


(start,end,holiday


Note: Both functions automatically skip weekends (Saturday and Sunday)
and will also skip holidays, if provided. If you need more flexibility on what
days are considered weekends, see the WORKDAY.INTL function and


NETWORKDAYS.INTL function.


s)


101 EXCEL FUNCTIONS


VIDEO


How to calculate
due dates with
WORKDAY


Practice
worksheets
included with


online Video
Training


WEEKDAY and WEEKNUM


function. WEEKDAY returns a number between 1-7 that indicates Sunday,


To figure out the day of week from a date, Excel provides the WEEKDAY ©


Monday, Tuesday, etc. Use the WEEKNUM function to get the week number


in a given year.


X


[=]
On


fe


=WEEKDAY(B5)


Date
Sun, 11-Nov-2018
Mon, 12-Nov-2018
Tue, 1-Jan-2019
Fri, 1-Feb-2019


Held bed el) badd cl al


H
H


i


WEEKDAY _WEEKNUM

1 46
2 46
3 1
6 5


(date)
(date) ‘


See this formula to calculate sales per weekday.


MONDAY


TUESDAY


WEDNESDAY


LI
L
L
L


THURSDAY


i?


ee]


SATURDAY


FORMULA


Get the monday of
the week


101 EXCEL FUNCTIONS 8


Engineering
CONVERT


Most Engineering functions are pretty technical... you’ll find a lot of func-
tions for complex numbers in this section. However, the CONVERT func-
tion is quite useful for everyday unit conversions. You can use CONVERT
to change units for distance, weight, temperature, and much more.


F5 a fe =CONVERT(B5,C5,D5)
A A B C D | E jee G n
i
2 tT om A y t th
3 |
5 | 72F c —> 22.2)
6 | 10 km mi ed ae
7 | 175 Ibm kg 8
8 75 jin m 1.9}
5 Ilgai l 3.8
10
u
12|
12

Biy ar A A r a Aa


See this formula to calculate the BMI of an individual where the CONVERT
function is used to convert between the metric and imperial unit systems.


101 EXCEL FUNCTIONS


Using the
Versatile Convert
Function


1 Gallon = 3.8 Litres


cm


—+~~
(A i A
in


A | UULU
AG ae NG d


1 in = 2.54 cm


Information Functions


ISBLANK, ISERROR, ISNUMBER, and ISFORMULA
Excel provides many functions for checking the value in a cell, including


LA These functions are sometimes called the “IS” functions, and they all
return TRUE or FALSE based on a cell’s contents.


D5 ~| 3 fe | =ISNUMBER(B5)

d A | B IE D | E | F | G | H | l |
1 |

2 t {| tents

3 | : f . : ,

4 Value _ISNUMBER ISTEXT ISLOGICAL ISBLANK ISERROR ISFORMULA
EZ 100, —> TRUE | FALSE | FALSE | FALSE | FALSE | FALSE
6| | 6/28/2018 —> TRUE FALSE FALSE | FALSE FALSE FALSE
7) $20.00 | —-> TRUE | FALSE | FALSE FALSE FALSE | FALSE
8| 50% -- TRUE FALSE | FALSE FALSE | FALSE | _ FALSE
9| | 8:15AM - TRUE — FALSE | FALSE | FALSE FALSE | FALSE
10 | -> FALSE FALSE | FALSE | TRUE FALSE | FALSE
TE) apple --> FALSE TRUE | FALSE | _ FALSE FALSE | FALSE
12| r #DIV/O! --> FALSE FALSE | FALSE FALSE TRUE | TRUE
13 | FALSE -- FALSE FALSE | TRUE FALSE FALSE | FALSE |
14|

15 |


Excel also has ISODD and ISEVEN functions will test a number to see if it’s


even or odd.


By the way, the green fill in the screenshot above is applied automatically


with a conditional formatting formula.


101 EXCEL FUNCTIONS


20


True or False
results using
Information
Functions


10


Logical Functions

Excel’s logical functions are a key building block of many advanced formulas.
Logical functions return the boolean values TRUE or FALSE. If you need a
primer on logical formulas, this video goes through many examples.


AND, OR and NOT

The core of Excel’s logical functions are the AND function, the OR func-
tion, and the NOT function. In the screen below, each of these function is
used to run a simple test on the values in column B:


DS = 5 fæ =AND(B5>3,B5<9)

4| aA B | c | D E F G H
1 | ans u iets ela:

z| Value AND OR NOT
al 2 FALSE | FALSE FALSE
5 | 4 TRUE FALSE TRUE
7 3 FALSE TRUE TRUE
=| 2 FALSE FALSE FALSE
B 7 TRUE FALSE TRUE
= 9 FALSE TRUE TRUE
10 |

14


(B5 > 3,B5 <9)


(B5 = 3,B5 = 9)
(B5 = 2)


IF and IFS functions
The IF function is one of the most used functions in Excel. In the screen
below, IF checks test scores and assigns “pass” or “fail”:


DS a fe || =IF(C5>=70,"Pass","Fail")

Aj A)| B | E E D E F | G H |
1 —

2 IF function to assign Pass or Fail

3|

4 Name Score Result

5 | Anderson 92 Pass

6 | Bautista 85 Pass Passing score: 70
Fal Block 65 Fail

8 | Burrows 79 Pass

9 | Chandler 69 Fail

10 Colby 95 Pass


The logical functions above can be combined with the IF function to create
more complex logical tests. Alternatively, multiple IF functions can be
nested together to return more than two values as a result.


101 EXCEL FUNCTIONS


Is it Green?


Kh TRUE FALSE


VIDEO


How to build
logical formulas


GUIDE


50 examples of
formula criteria


<
#


New in Excel 2019 and Office 365, the IFS function can run
multiple logical tests without nesting IFs.


D5 % te =IFS(C5<60,"F",C5<70,"D",C5<80,"C",C5<90,"B",C5>=90,"A")
LAr B C Ee E [EES ne I J
1 |
2
3
4 Name Score Grade Score Grade
5 Hannah 81.8 B | 0 F
6 | Edward 82.8 B 60 D
7 | Miranda 91.3 A 70 E
8 | William 76 E 80 B
295 Joanna 74:2 € 90 A
10) Collin 80.6 B
11 Mallory 85 B
E


12 Oscar 79.2


IFS(C5 < 60, "F",C5 < 70, "D",C5 < 80, "C" ,C5 <90,"B",C5


90,"A")


IFERROR and IFNA


The IFERROR function and IFNA function can be used as a simple way to
trap and handle errors. In the screen below, VLOOKUP is used to retrieve
cost from a menu item. Column F contains just a VLOOKUP function, with
no error handling. Column G shows how to use IFNA with VLOOKUP to
display a custom message when an unrecognized item is entered.


G5 àf =IFNA(VLOOKUP(E5,menu,2,0), "Not found")
A B c D E F G H
1
2
3
4 Item Cost Item Cost IFNA
>>>