Top 100 EXCEL FUNCTIONS with Examples + Sample Files (2019 List)

Home ➜ Top 100 Excel Functions with Examples – A Complete List Including Samples Files

What is an Excel Function

In Excel, a function is a predefined formula that performs a specific calculation by using values a user input as arguments. Every Excel function has a specific purpose, in simple words, it calculates a specific value. Each function has its arguments (the value one needs to input) to get the result value in the cell.

TYPES of Excel Functions

Below is the major types of the Excel Functions which we have covered in the this guide:

TEXT FUNCTIONS: If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.
DATE FUNCTIONS: Dates are one of the major ingredients of data that you use every day, and if you want to analyze your data in a better way you need to know some of these functions.
TIME FUNCTIONS: Just like dates, time is could also be there in data and you can use time functions to deal with data where you have time values.
LOGICAL FUNCTIONS: Logical functions can help you create some of the most helpful formulae in your spreadsheet.
MATHS FUNCTIONS: Excel is all about calculations and analysis, and mathematical functions are one of those functions which you can use to get better in calculations and analysis.
STATISTICAL FUNCTIONS: One of the best things about Excel is there are a bunch of statistical functions there that you can use to analyze data easily.
LOOKUP FUNCTIONS: In Excel, there some specific functions which can help you to look up a value or specific information about a cell or a range of cells.
INFORMATION FUNCTIONS: These some specific functions which you can use to get information about values you supplied.
FINANCIAL FUNCTIONS: These functions can help you calculate some of the common but important financial calculations in an easy way.

COMPONENTS of an Excel Functions

Each function has two major components. In short, each function (except a few) is made up of two following things:

Function Name
Arguments

Let me show you an example. Let’s take a look at the below function which we have inserted in the cell A1.

Now if you look at the formula bar you can understand the structure of the function by splitting it into two parts i.e. name and arguments.

What is an ARGUMENT

As I have already mentioned that in a function you need to specify input values to get the desired result. An argument is that value which you need to specify. If you look at the syntax of a function you can see there in each function there is set arguments to specify.

Types of Arguments

Below are the types of the arguments:

Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
Optional: If you skip specifying these arguments it will not stop a function to calculate its result value.
No Arguments: There are few functions (like NOW) where you don’t need to specify any argument.

How to INSERT a Function in Excel

The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with a equals to sign. Let's say you want to insert the SUM function:

First of all, you need to type = and the then type SUM.
After that, enter the opening parentheses.
Specify the arguments (refer to a cell or you can directly enter values into the function).
In the end, type closing parentheses and hit enter.

here’s how this function guide can help you

First, you will learn what's the purpose of a function and its syntax.
Next thing is arguments and how to declare an argument.
And in the end, its usage through a simple example so that you can use it in real life.

Text Functions

S No:	Function Name	Description
1	Excel FIND Function	Returns the starting position of a text string in another text string (case sensitive).
2	Excel LEFT Function	Returns the string from another string starting from the left.
3	Excel LEN Function	Counts the number of characters from the value supplied.
4	Excel LOWER Function	Convert a text into lower case.
5	Excel MID Function	Returns a sub-string from a string using a specific position and number of characters.
6	Excel PROPER Function	Convert a text to a proper case text.
7	Excel REPT Function	Repeats a value a number of times.
8	Excel RIGHT Function	Returns the string from another string starting from the right.
9	Excel SEARCH Function	Returns the starting position of a text string in another text string (case sensitive).
10	Excel UPPER Function	Convert a text into an upper case text.

Date Functions

S No:	Function Name	Description
1	Excel DATE Function	Returns a valid date using the day, month, and year supplied.
2	Excel DATEDIF Function	Calculates the difference between two dates.
3	Excel DATEVALUE Function	Converts a date that is formatted as text into an actual date.
4	Excel DAY Function	Returns the day from the date supplied.
5	Excel DAYS Function	Returns the count of days between two dates.
6	Excel EDATE Function	Returns a date after adding/subtracting months from the supplied date.
7	Excel EOMONTH Function	Returns the end of the month date from a future month or a past month.
8	Excel MONTH Function	Returns the month from the date supplied.
9	Excel NETWORKDAYS Function	Count of days between the start date and end date, excluding weekends and holidays.
10	Excel NETWORKDAYS.INTL Function	Count of days between the start date and end date, excluding weekends (Custom), and holidays.
11	Excel TODAY Function	Returns the current date.
12	Excel WEEKDAY Function	Returns the day number (ranging from 1 to 7) of the date in the week.
13	Excel WEEKNUM Function	Returns the week number (in the whole year) of the date ranging from 1 to 54.
14	Excel YEAR Function	Returns the year from the date supplied.

Time Functions

S No:	Function Name	Description
1	Excel HOUR Function	Returns the hours from the time supplied.
2	Excel MINUTE Function	Returns the minutes from the time supplied.
3	Excel NOW Function	Returns the current date and time.
4	Excel SECOND Function	Returns the seconds from the time supplied.
5	Excel TIME Function	Returns a valid time using the hours, minutes, and seconds supplied.
6	Excel TIMEVALUE Function	Convert a time value that is stored as text into actual time.

Logical Functions

S No:	Function Name	Description
1	Excel AND Function	Test multiple conditions and return TRUE if all the conditions are TRUE.
2	Excel FALSE Function	Returns the boolean value FALSE.
3	Excel IF Function	Tests a condition and returns a value if that condition is TRUE else some other value.
4	Excel IFERROR Function	Tests a value for an error and returns the specified value if an error occurred, else returns the original value.
5	Excel IFNA Function	Tests a value for #N/A error and returns the specified value if the error occurred, else returns the original value.
6	Excel NOT Function	Reverse a logical result/value.
7	Excel OR Function	Test multiple conditions and return TRUE if any of the conditions are TRUE.
8	Excel TRUE Function	Returns the boolean value TRUE.

Math Functions

S No:	Function Name	Description
1	Excel ABS Function	Converts a number into an absolute number.
2	Excel EVEN Function	Rounds a number to the nearest even number.
3	Excel INT Function	Returns the integer part from the value supplied.
4	Excel MOD Function	Returns the remainder value after dividing a number with a divisor.
5	Excel MROUND Function	Rounds a number to a given multiple.
6	Excel RAND Function	Returns a random number ranging from 0 to 1.
7	Excel SUM Function	Sum the value supplied.
8	Excel SUMIF Function	Sum the value supplied using the condition specified.
9	Excel SUMIFS Function	Sum the value supplied using the multiple conditions specified.
10	Excel SUMPRODUCT Function	Multiply and sum the array values.
11	Excel TRUNC Function	Returns a number after truncating the original number.

Statistical Functions

S No:	Function Name	Description
1	Excel AVERAGE Function	Calculates the average of the supplied numeric values.
2	Excel AVERAGEA Function	Calculates the average of the supplied numeric values, boolean, and numbers formated as text.
3	Excel AVERAGEIF Function	Calculates the average of the numbers based on the conditions you specify.
4	Excel AVERAGEIFS Function	Calculates the average of the numbers based on the multiple conditions you specify.
5	Excel COUNT Function	Counts the supplied numeric values.
6	Excel COUNTA Function	Counts the supplied values except for blanks.
7	Excel COUNTBLANK Function	Counts the blank values.
8	Excel COUNTIF Function	Counts the values supplied using the condition specified.
9	Excel COUNTIFS Function	Counts the values supplied using the multiple conditions specified.
10	Excel MAX Function	Returns the maximum value from a list of value.
11	Excel MIN Function	Returns the minimum value from a list of value.

Lookup Functions

S No:	Function Name	Description
1	Excel ADDRESS Function	Creates a valid cell reference as per the supplied row and column.
2	Excel AREAS Function	Counts the number of ranges in the supplied reference.
3	Excel CHOOSE Function	Returns a value from a list of values using the index number.
4	Excel COLUMN Function	Returns the column number of the referred cell.
5	Excel COLUMNS Function	Count columns referred to in the supplied range.
6	Excel FORMULATEXT Function	Returns the formulas from the cell referred to.
7	Excel HLOOKUP Function	Lookups for a value in the top row of a table and returns the value from the same column using an index number.
8	Excel HYPERLINK Function	Create a text with the hyperlink using the link specified.
9	Excel INDEX Function	Returns a value from a list using the index number of that value in the list.
10	Excel INDIRECT Function	Create a valid cell reference from the text supplied.
11	Excel LOOKUP Function	Lookup for a valuer from a row, column, or an array.
12	Excel MATCH Function	Returns the index number of the value from the list of values.
13	Excel OFFSET Function	Returns a reference to a range which is a specific number of rows and columns away from a cell or range of cells.
14	Excel ROW Function	Returns the row number of the referred cell.
15	Excel ROWS Function	Count rows referred to in the supplied range.
16	Excel TRANSPOSE Function	Changes the orientation of a range.
17	Excel VLOOKUP Function	Lookups for a value in the first column of a table and returns the value from the same row of the matched value using the index number.

Information Functions

S No:	Function Name	Description
1	Excel CELL Function	Returns specific information about the cell referred to.
2	Excel ERROR.TYPE Function	Returns a number that represents the error.
3	Excel INFO Function	Returns information about the current operating environment.
4	Excel ISBLANK Function	Checks if the supplied value is blank and returns TRUE.
5	Excel ISERR Function	Checks if the supplied value is an error other than #N/A and returns TRUE.
6	Excel ISERROR Function	Checks if the supplied value is an error and returns TRUE.
7	Excel ISEVEN Function	Checks if the supplied value is an even number and returns TRUE.
8	Excel ISFORMULA Function	Checks if the supplied value is a formula and returns TRUE.
9	Excel ISLOGICAL Function	Checks if the supplied value is logical value and returns TRUE.
10	Excel ISNA Function	Checks if the supplied value is an #N/A and returns TRUE.
11	Excel ISNONTEXT Function	Checks if the supplied value is a non-text value and returns TRUE.
12	Excel ISNUMBER Function	Checks if the supplied value is number and returns TRUE.
13	Excel ISODD Function	Checks if the supplied value is an odd number and returns TRUE.
14	Excel ISREF Function	Checks if the supplied value is a reference and returns TRUE.
15	Excel ISTEXT Function	Checks if the supplied value is a text and returns TRUE.
16	Excel N Function	Converts a boolean to a number.
17	Excel NA Function	Returns the #N/A.
18	Excel SHEET Function	Returns the sheet number of the referred cell.
19	Excel SHEETS Function	Counts the number of sheets in the reference.
20	Excel TYPE Function	Checks a value for an error and returns a number to represent it.

Financial Functions

S No:	Function Name	Description
1	Excel FV Function	Calculates the future value of an investment.
2	Excel PMT Function	Calculate a periodic payment of loan which you need to pay.
3	Excel PV Function	Calculates the present value of financial investment or a loan.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn

YouTube