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

HomeTop 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

Returns the starting position of a text string in another text string (case sensitive).

2

Returns the string from another string starting from the left.

3

Counts the number of characters from the value supplied.

4

Convert a text into lower case.

5

Returns a sub-string from a string using a specific position and number of characters.

6

Convert a text to a proper case text.

7

Repeats a value a number of times.

8

Returns the string from another string starting from the right.

9

Returns the starting position of a text string in another text string (case sensitive).

10

Convert a text into an upper case text.

Date Functions

S No:

Function Name

Description

1

Returns a valid date using the day, month, and year supplied.

2

Calculates the difference between two dates.

3

Converts a date that is formatted as text into an actual date.

4

Returns the day from the date supplied.

5

Returns the count of days between two dates.

6

Returns a date after adding/subtracting months from the supplied date.

7

Returns the end of the month date from a future month or a past month.

8

Returns the month from the date supplied.

9

Count of days between the start date and end date, excluding weekends and holidays.

10

Count of days between the start date and end date, excluding weekends (Custom), and holidays.

11

Returns the current date.

12

Returns the day number (ranging from 1 to 7) of the date in the week. 

13

Returns the week number (in the whole year) of the date ranging from 1 to 54.

14

Returns the year from the date supplied.

Time Functions

S No:

Function Name

Description

1

Returns the hours from the time supplied.

2

Returns the minutes from the time supplied.

3

Returns the current date and time.

4

Returns the seconds from the time supplied.

5

Returns a valid time using the hours, minutes, and seconds supplied.

6

Convert a time value that is stored as text into actual time.

Logical Functions

S No:

Function Name

Description

1

Test multiple conditions and return TRUE if all the conditions are TRUE.

2

Returns the boolean value FALSE.

3

Tests a condition and returns a value if that condition is TRUE else some other value.

4

Tests a value for an error and returns the specified value if an error occurred, else returns the original value.

5

Tests a value for #N/A error and returns the specified value if the error occurred, else returns the original value.

6

Reverse a logical result/value.

7

Test multiple conditions and return TRUE if any of the conditions are TRUE.

8

Returns the boolean value TRUE.

Math Functions

S No:

Function Name

Description

1

Converts a number into an absolute number.

2

Rounds a number to the nearest even number.

3

Returns the integer part from the value supplied.

4

Returns the remainder value after dividing a number with a divisor.

5

Rounds a number to a given multiple.

6

Returns a random number ranging from 0 to 1.

7

Sum the value supplied.

8

Sum the value supplied using the condition specified.

9

Sum the value supplied using the multiple conditions specified.

10

Multiply and sum the array values.

11

Returns a number after truncating the original number.

Statistical Functions

S No:

Function Name

Description

1

Calculates the average of the supplied numeric values.

2

Calculates the average of the supplied numeric values, boolean, and numbers formated as text.

3

Calculates the average of the numbers based on the conditions you specify.

4

Calculates the average of the numbers based on the multiple conditions you specify.

5

Counts the supplied numeric values.

6

Counts the supplied values except for blanks.

7

Counts the blank values.

8

Counts the values supplied using the condition specified.

9

Counts the values supplied using the multiple conditions specified.

10

Returns the maximum value from a list of value.

11

Returns the minimum value from a list of value.

Lookup Functions

S No:

Function Name

Description

1

Creates a valid cell reference as per the supplied row and column.

2

Counts the number of ranges in the supplied reference.

3

Returns a value from a list of values using the index number.

4

Returns the column number of the referred cell.

5

Count columns referred to in the supplied range.

6

Returns the formulas from the cell referred to.

7

Lookups for a value in the top row of a table and returns the value from the same column using an index number.

8

Create a text with the hyperlink using the link specified.

9

Returns a value from a list using the index number of that value in the list.

10

Create a valid cell reference from the text supplied.

11

Lookup for a valuer from a row, column, or an array. 

12

Returns the index number of the value from the list of values.

13

Returns a reference to a range which is a specific number of rows and columns away from a cell or range of cells.

14

Returns the row number of the referred cell.

15

Count rows referred to in the supplied range.

16

Changes the orientation of a range.

17

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

Returns specific information about the cell referred to.

2

Returns a number that represents the error.

3

Returns information about the current operating environment.

4

Checks if the supplied value is blank and returns TRUE.

5

Checks if the supplied value is an error other than #N/A and returns TRUE.

6

Checks if the supplied value is an error and returns TRUE.

7

Checks if the supplied value is an even number and returns TRUE.

8

Checks if the supplied value is a formula and returns TRUE.

9

Checks if the supplied value is logical value and returns TRUE.

10

Checks if the supplied value is an #N/A and returns TRUE.

11

Checks if the supplied value is a non-text value and returns TRUE.

12

Checks if the supplied value is number and returns TRUE.

13

Checks if the supplied value is an odd number and returns TRUE.

14

Checks if the supplied value is a reference and returns TRUE.

15

Checks if the supplied value is a text and returns TRUE.

16

Converts a boolean to a number.

17

Returns the #N/A.

18

Returns the sheet number of the referred cell.

19

Counts the number of sheets in the reference.

20

Checks a value for an error and returns a number to represent it.

Financial Functions

S No:

Function Name

Description

1

Calculates the future value of an investment.

2

Calculate a periodic payment of loan which you need to pay.

3

Calculates the present value of financial investment or a loan.

About the Author

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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

103 thoughts

Leave a Comment

Your email address will not be published.

  1. Sir, 2nd Sept.2019.
    Really you have shown very clearly and deeply all points.
    I must thank you.
    Hoping to receive more articles in future too.

    Kanhaiyalal Newaskar.

    Reply
  2. This is very useful, but I often don’t have access to the internet when I am working in Excel. Could you make this information in a PDF file, so that it would be accessible offline?

    Reply
  3. Keep on sharing. This help us a lot. Its a time save for us and no need to search each functions from net cause its already here.

    Reply
  4. Hi, I find your Excel tips very useful. It helps me a lot. Tips likes this give me a lesser time to search for the uses and functions of each formulas. Thanks a lot.

    Reply
  5. Sir, very useful information,notes and formula you have given.User like me would appreciate the work.
    Once again thanks.

    Reply
  6. Thank you very much Mr. Punit Sir. I am computer faculty and recently I have asked to teach Excel functions and I find the complete list here. Could you possibly provide us the PDF format of all the functions with examples to use. Thank you.

    Reply
  7. Thanks a lot for sharing your knowledge with us with clear explanation. Your blog is my guru. All your excel tips are being very useful for my work. Thanks again.

    Reply
  8. Hello Punit,

    I am very thankful to you as you have start this learning portal for all. And thanks for giving ur stuff from this web portal i will be able to use excel smartly and efficiently. I learn so far with this really Excellent work ur doing
    Thanks a lot again.

    Reply
  9. Thank you so much Puneet Gogia and nice to see the whole functions list in online,it is helpful to me.I’m really appreciate to you… i want to learn VBA pls need you guideline (confusion in coding)

    Reply
  10. Hi, Dear Punit,

    I am very thankful to you as you have start this learning portal for all. In my office people are jealous they don’t give me chance to learn excel and no one guide me how to use excel smartly even though many of them are working smartly in excel. But from your this web portal i will be able to use excel smartly and efficiently. Thanks a lot again.

    Reply
  11. (Maraming salamat po sayo Puneet..) Thank you so much Puneet. It’s a great help for me to learn more from you

    Reply
  12. It will be better if you put more information about Excel macros. Thank you for your contributions in order to make people’s working lives easier. Keep up the good work.

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

      Reply
  13. 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.

    Reply
  14. 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 ..

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

    Reply
  16. 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)

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

    Reply