I’m sure you agree with me that "**Functions can make you great in Excel**".

Well, once you master Excel basics this is the next thing you need to master.

But the question is how can you learn any of these functions in the easiest way?

For this, I have this comprehensive list for you.

In this list, you can find **Top 100 Excel Function with Examples**.

Every single function in this list is explained in a way that you can learn it in no time.

Here’s how this function guide can help you to master some of the most useful functions.

**First of all**, you will learn what's the purpose of a function and it's syntax.**Next**thing is some important information about it.**And in the en****d**, its usage through a simple example so that you can use it in real life.

For example, if you want to learn about IF function, you have a quick intro, syntax description, some additional information and then an example.

In the end, you have links to the relevant sources to learn further. And all the functions are structured in the same way.

## ...without any further ado here's the list of most useful functions you need to learn.

Get Better at Excel in 2018

*A bundle of E-Books you need to thrive in Excel this year.*

### (A) Text Functions

- LEFT Function : It returns specified (by you) characters from the left side of a text string. You need to specify the text and number of characters you want to extract.
- RIGHT Function: It returns specified (by you) characters from the right side of a text string. You need to specify the text and number of characters you want to extract.
- MID Function: It returns specific characters from a specific position in the text string. You need to specify the text string, start point and, number of character you want to extract.
- LOWER Function: It converts a normal text into a lower case text where you will have all the letters in small letters. You can refer to a cell or directly insert the text into the function.
- UPPER Function: It converts a normal text into an upper case text where you will have all the letters in capital letters. You can refer to a cell or directly insert the text into the function.
- PROPER Function: It converts a normal text into a proper case text where you will have the first letter of a word in capital letter and rests all in small.
- REPT Function: It repeats a text which you specify (the number of times). You just need to specify the text and the repetition number.
- LEN Function: It returns the number of total characters a text string has. You can input a text string directly into the function or simply refer to a cell.
- FIND Function: It helps to find the starting position of a text string (Case Sensitive) from another text string. You just need to specify the text to find and a text from which you want to find.
- SEARCH Function: It helps to find the starting position of a text string from another text string. You just need to specify the text to find and a text from which you want to find.

### (B) Date Functions

- DATE Function: It creates a valid date according to Excel's date format. You need to specify the day, month and, the year for the date.
- DATEDIF Function: It returns the difference between the start date and end date. It has six different ways to show the difference.
- DATEVALUE Function: It converts a date (stored as text) into a serial number that Excel recognizes as a date. You can refer to a cell or you can also enter a value directly into the function.
- EDATE Function: It returns a date which is a number of months before or after a specific date. You'll get the same date for the future or past.
- DAY Function: It returns the day of the month ranging from 1-31 from the date you specify (a valid date).
- DAYS Function: It returns the difference between two dates using four different methods to calculate.
- TODAY Function: It returns a serial number that Excel recognizes as the current date. This is a volatile function which changes its values when you recalculate.
- MONTH Function: It returns the month name (Jan-Dec) from the date you specify (a valid date).
- EOMONTH Function: It gives you the last date of a month which is a number of months before or after the mentioned date.
- YEAR Function: It returns the year (Jan-Dec) from the date you specify (a valid date).
- WEEKDAY Function: It returns the day number of the week (ranging from 1-7) for the date you have mentioned.
- WEEKNUM Function: It returns the week number of a specific date. For example, the week containing Jan 1 is in the first week of the year.
- NETWORKDAY Function: It returns the number of working days between the start date and end date which exclude weekends (Incl. Holidays and mentioned dates).
- NETWORKDAY.INTL Function: It returns the number of workdays between the start date and end date which exclude weekends (Incl. Holidays and mentioned dates).

### (C). Time Functions

- TIME Function: If create a valid time according to Excel's time format. You need to provide hour, minutes, and seconds.
- TIMEVALUE Function: It converts a text time value into a valid time value according to Excel's time format. You can refer to a cell or you can also enter that text directly into the function.
- HOUR Function: It returns the hour value (ignores minutes and seconds) from a time value ranging from 0-23 hours.
- MINUTE Function: It returns the minute value (ignores hour and seconds) from a time value ranging from 0-59 minutes.
- SECOND Function: It returns the seconds' value (ignores hours and minutes) from a time value ranging from 0-59 seconds.
- NOW Function: It gives you current date and time as per your system settings. This is a volatile function which changes its values when you recalculate your worksheet.

### (D). Logical Functions

- IF Function: You can provide a condition to check and it returns a specific value if that condition is TRUE and another value if that...
- IFERROR Function: It evaluates a value for an error and returns a specific value if an error occurs...
- IFNA Function: It evaluates a value for #N/A error and returns a specific value if an error occurs...
- OR Function: Test two or more conditions differently and return TRUE if any of those conditions is TRUE and FALSE if all those conditions...
- AND Function: Test two or more conditions jointly and return TRUE if all of those conditions is TRUE and FALSE if any of those conditions...
- NOT Function: It converts a TRUE into FALSE and FALSE into TRUE...
- FALSE Function: It returns the logical value TRUE in the cell where you insert it...
- TRUE Function: It returns the logical value FALSE in the cell where you insert it...

### (E). Mathematical Functions

- SUM Function: It adds values. It can add individual values, cell references or ranges or a mix of all three.
- SUMIF Function: It returns the sum of the numeric values of which meet a condition. You need to specify a condition and the range to check that condition.
- SUMIFS Function: It returns the sum of the numeric values of which meet multiple conditions. You need to specify conditions and range to check those conditions.
- SUMPRODUCT Function: It multiplies values from corresponding cells in the given arrays, and returns the sum of those products. It can take an array without using Ctrl + Shift + Enter.
- ABS Function: It converts a number into an absolute number. The absolute value of a number is the number without its sign.
- EVEN Function: It returns a number by rounding it to the nearest even number.
- INT Function: It returns a number by rounding it to the nearest to the nearest integer.
- MOD Function: It returns remainder after dividing two numbers, not the result of the division of two numbers.
- MROUND Function: It rounds a number to the nearest multiple of a number without considering that number is greater or lower than the original number.
- TRUNC Function: It truncates a number to an integer by removing the fractional part of the number. In simple word, it returns on integer part from a value.
- RAND Function: It returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned...

### (F). Statistical Functions

- COUNT Function: It counts the number of cells which contain numbers, and counts the numbers which are specified in the arguments.
- COUNTA Function: It counts all the cells which are not empty. It doesn't matter which type of value is there in a cell, it counts all the cell with values in it.
- COUNTBLANK Function: It counts all the cells which are empty/blank, but if a cell has a blank space it will not count it.
- COUNTIF Function: It counts the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.
- COUNTIFS Function: It counts the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.
- AVERAGE Function: It returns the average (arithmetic mean) of the arguments. You can refer to a range of cells or insert numbers inside the function.
- AVERAGEA Function: It returns the average (arithmetic mean) of a group of numbers and text. You can refer to a range of cells or insert numbers inside the function.
- AVERAGEIF Function: It averages the number of cells which meet a criterion. You need to specify a criterion and a range of cell to check that criterion.
- AVERAGEIFS Function: It averages the number of cells which meet criteria. You need to specify criteria and ranges to check those criteria.
- MAX Function: It returns the largest value in a set of values. You can refer to a range or insert values directly into the function as well.
- MIN Function: It returns the smallest value in a set of values. You can refer to a range or insert values directly into the function as well.

### (G). Lookup Functions

- ADDRESS Function: It returns the address of a cell in a worksheet, given specified row and column numbers.
- AREAS Function: It returns the number of areas in a given reference. An area can be a range of contiguous cells or a single cell.
- CHOOSE Function: It returns a value from a list based on position given.
- COLUMN Function: It returns the column number of the given reference.
- COLUMNS Function: It returns the number of columns included in the given reference.
- FORMULATEXT Function: It returns a formula as a string from the referred cell.
- HLOOKUP Function: It searches for a value in the top row of a table or an array of values, and returns a value in the same column from a row you specify.
- HYPERLINK Function: It creates a hyperlink that opens a document stored on a network server, an intranet, or the Internet.
- INDEX Function: It returns a value from a column or a row based on the position of that value in the column or row.
- INDIRECT Function: It returns the reference specified by a text string. You need to mention the text and reference style.
- LOOKUP Function: It helps to lookup for a value from a single column or row.
- MATCH Function: It returns a number representing the position of a cell in an array. In simple words, you get the position of a cell in from a column or row.
- OFFSET Function: It creates a reference offset from given starting cell using height and width.
- ROW Function: It returns the row number of the given reference.
- ROWS Function: It returns the number of rows included in the given reference.
- TRANSPOSE Function: It switches row into column and column into rows using Ctrl + Shift + Enter...
- VLOOKUP Function: It looks up for a value in a column and returns the value from the right of the value that you have found.

### (H). Financial Functions

- FV Function: It calculates the future value of an investment which is based on periodic and constant payments and on a constant rate of interest...
- PMT Function: It calculates loan payment based on fixed monthly payments and constant rate of interest...
- PV Function: It helps you to determine that an investment is profitable or not. For using PV function, you need a constant interest rate...

### (I). Information Functions

- CELL Function: It returns some specific information about a cell. You need to refer to a cell and type of information you need.
- ERROR.TYPE Function: It returns a number if the referred cell has an error. For each type of error, there is a different number it returns.
- INFO Function: It returns information about the current operating environment. You can select the type of information you need from the function.
- ISBLANK Function: It verifies a cell and return TRUE if that cell is blank (no value) and FALSE if that cell is not blank.
- ISERR Function: It verifies a cell and return TRUE if that cell has an error other than #N/A and FALSE if that cell has any other value.
- ISERROR Function: It verifies a cell and return TRUE if that cell has an error, otherwise it returns a FALSE.
- ISEVEN Function: It verifies a cell and return TRUE if that cell has an ever, otherwise it returns a FALSE.
- ISFORMULA Function: It verifies a cell and return TRUE if that cell has a formula, otherwise it returns a FALSE.
- ISLOGICAL Function: It verifies a cell and return TRUE if that cell has a logical value, otherwise it returns a FALSE.
- ISNA Function: It verifies a cell and returns TRUE if that cell has and #N/A error, otherwise it returns a FALSE.
- ISNONTEXT Function: It verifies a cell and return TRUE if that cell has a non-text value, otherwise it returns a FALSE.
- ISNUMBER Function: It verifies a cell and return TRUE if that cell has a numeric value, otherwise it returns a FALSE.
- ISODD Function: It verifies a cell and return TRUE if that cell has an odd number, otherwise it returns a FALSE.
- ISREF Function: It verifies a cell and return TRUE if that cell has a reference, otherwise it returns a FALSE.
- ISTEXT Function: It verifies a cell and return TRUE if that cell has a text value, otherwise it returns a FALSE.
- N Function: It converts a logical value into a number. 1 for TRUE and 0 for FALSE.
- ISERR Function: It verifies a cell and return TRUE if that cell has an error other than #N/A and FALSE if that cell has any other value.
- NA Function: It returns an #N/A in the cell where you enter it.
- SHEET Function: It returns the worksheet number of the reference.
- SHEETS Function: It returns the number of sheets in a reference.
- TYPE Function: It returns a number representing a value type. When you refer a cell in this function, it verifies the value and returns a number to present it.

Great Puneet.. You made wonderful work to make everyone expert in Excel.

I want to have an example as well after explaining

after long time i find such amazing summary about excel function …thanxs alot sir

Gogia sir, plz suggest solution, if any

https://uploads.disquscdn.com/images/0702942c4aa7355dffa9637ed2508ef37a5666306d6e3db4491e61dd4f600f9d.jpg there are 25 row in a column in which numbers are stored in-sequential like 0, 32,1,5,15,20,101,0,15,0,2,3,0………….. one figure in every one row, I need to allocate one serial number for all the duplicates that too in ascending order of the stored in-sequential figure, I want to apply sorting smaller to larger

You have already sorted in above snapshot?

Sir it was done manually just to let you explain my actual need, please suggest function, if you can help in this regard.

please give formulas and function in the pdf formet

Thanks Puneet for your support

Very useful tips like camera option, Picture graph thanks a lot

Thanks a lot for providing a helpful resources. You did a excellent job. Thanks again…

Are these available in a PDF or Word File? This is a good reference, but it’s hard to remember to come back here for the information. Having them as something you can download would be really helpful.

How to get data of various sheets on Summarry sheet of the inventory register.

Thank you for the valuable information…I would like to know one specific thing.

Once we use the transpose, only values are copied. If i want to retain the values with formula in transpose function, is it possible. This is very important to keep scientific way to prove that the transpose operation done is correct. In data analytics big excel sheets are handled and a small mistake can lead to big problems .. Please advise me ..

How Can I creat a daly sale report in excel

please help me

Puneet, you are simply amazing.

Thanks It is very useful to know which formula where to use. Thanks Puneet Bro

Hi,

Please provide the link for Inv. Management System

very nice useful puneet how i download this excel function book

How can I create a timezone convertor??

Thanks punnet for providing such a good platform to learn excel

MS has already created a summary lists of all functions with links to brief descriptions.

functions (alphabetical) 2016-2007

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

Functions (by category) – 2016 – 2007

https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

with transpose function can we reverse the data set; like AAA, BBB, CCC transpose to CCC,BBB,AAA. Thanks

Hi Puneet I think it is great if you combine these formulas in one store in PDF it is really awesome

I’d love it.but it will be nice if you combine it in a one PDF file 🙂

HI, is there any way to work out how long there is between 2 times? ie. 09:42 and 10:01 (that is, I want it to show 19 minutes)

Try,

=TEXT(10:01-09:42,”HH:MM:SS”)

SUBTOTAL – which I find is a powerful function, but often overlooked – particularly with the different function numbers that formula uses.

Fully Agree With You. Thanks

GETPIVOTDATA, I haven’t nailed that one yet…dan.kordelski@gmaildotcom

Thanks Dan.

OFFSET and exact use of Array with detailed example.

Thanks Mohammed Mubin Sayyed

Punnet thanks, May I suggest this, an average subscriber fairly uses excel and functions but in isolation. Every one knows match, offset etc what most need is the combination of these functions to generate more potent practical problem solving forces is the next step I will like you to do after these functions in isolation.

Yeah Iyek. I am trying to give two different examples. one is solo & other is combined with some other function.

Great! Puneet. I am looking forward to this.

OFFSET/MATCH AND ROW/COLUMN

Thanks Iyke.

Index or match

Thanks Peter

I would love others to learn these functions “Indirect, Offset, Match, Index, Vlookup, Mid, Find, Replace, Substitute, Sumif, Sumifs, SumProduct mainly and of course array functions as well.

Thanks Asif.

I would really like to have a better understanding of the Indirect with Row and/or Columns function. Could we include these please.

Thanks Angel

Offset plays a vital role

Thanks Srinivasan

Hi Puneet, I saw someone at work using both the INDEX and MATCH functions. I see that someone has already requested MATCH so if possible, I would like to see INDEX added to the list. Thank you!

Alright Diana. Thanks for your reply.

Hello again, Puneet (and congratulations on your 1st 1,000 subscribers)! I would like to see the IFERROR function covered. I am just learning about it, so all lessons are greatly appreciated. Thank you for asking!

Thanks Brenda. I will for sure.

You’ll get your E-book on 30 Apr

i would like to Get a good description of the match function and/or the choose function. Thanks Torstein (sejohnse@yahoo.no)

Hey Torstein,

You will get the both.

Thanks for words.

You’ll get your E-book on 30 Apr

Hi, Can you add the sumproduct function. I want to know how you use it to bring back sums based on 3 or conditions. Thanks eannablack@gmail.com

Hello Eanna,

Already Added.

Thanks for your comment. You’ll get your E-book on 30 Apr

very helpful, i really appreciate. keep it up Bro

Thanks Gautam

Nice to see the whole functions list..would appreciate if you let us know the complete descriptions of all functions.

Hello Rohit,

List will be updated before next week. Please Subscribe to our newsletter to get the alert for that. You can also download 100 Keyboard Shortcuts, 40 Useful Excel Tips & 40 ready to use macros.

http://www.excelchamps.com/newsletter.html

Great to have such a summary available

Thanks Inet