101 Excel Functions with Examples

Excel Functions are one of the most important features in Excel. And, this is must to learn them to make best out of this most popular spreadsheet tool on the planet.

And, for this, I have compiled a list of 101 Excel Functions which you can use everyday work to speed up your all the calculations.

This is a comprehensive list in which you will find easy to understand examples and for each example, you can download a sample file to try it yourself.

I have split this list into categories so that you can pick them according to your need.

Text Functions

Function

Description

Get text from the left side of a cell.

Get text from the right side of a cell.

Get text from a specific position from a text string.

Convert text into lower case.

Convert text into upper case.

Convert text into the proper case.

Repeat a specific text for a specific number of times.

Count number of character in a cell.

Find case sensitive text from an another text string.

Search non-case sensitive text from an another text string.

Date Functions

Function

Description

Create a valid date.

Calculate interval between two dates.

Create a valid date from text.

Return the same date in future or past.

Return day number from a date.

Return number of days between two dates.

Return current date.

Return month’s number from a date.

Return the last date of month in future or past.

Return year’s number from a date.

Time Functions

Function

Description

Create a valid time.

Create a valid time from text.

Return hours from a time value.

Return minute from a time value.

Return seconds from a time value.

Return current date & time.

Return weekday of a given date.

Return week number of a given date.

Return the number of working days between two date.

Return the work days between two dates.

Logical Functions

Function

Description

Perform a specific task if a condition is true or false.

Evaluate a value for error & return a specific value if error occurs.

Evalueate value for #N/A error & return a specific value if error occurs.

Test two or more conditions differently.

Test two or more conditions jointly.

Convert TRUE into FALSE & FALSE into TRUE.

Return logical FALSE.

Return logical TRUE.

Maths Functions

Function

Descrription

Sum numeric values.

Sum numeric values which meet a condition.

Sum numeric values which meet multiple conditions.

Multiply and sum arrays.

Convert a number into an absolute number.

Round a number to the nearest even number.

Round down a number to the nearest integer.

Return remainder after dividing two numbers.

Round to the nearest multiple of a number.

Remove fractional part of a number.

Generate random numbers.

Statistical Functions

Function

Description

Return the count cells which have numbers.

Return the count of cells which are non-blank.

Return the count of cells which are blank.

Return the count of cells which meet criteria.

Return the count of cells which meet multiple criteria.

Return average of numbers.

Return the average of a group of numbers and text.

Return the average of numbers which meet criteria.

Return the average of numbers which meet multiple criteria.

Return largest values from a given set of values.

Return smallest values from a given set of values.

Lookup Functions

Functions

Header 1 / 2

Return a number of areas in a reference.

Return a number of areas in a reference.

Return a value from a list based on position.

Return the column number of the reference.

Return the number of columns in a range.

Return formula from a cell as a text.

Lookup for values from a row.

Create an Hyperlink in a cell.

Return a value from a list or table based on location.

Create a cell reference from a text.

Lookup for a values in a column range.

Return a number representing the position of a cell in an array.

Create a reference offset from given starting cell.

Return the row number of the reference.

Return the number of rows in a range.

Return the average of numbers.

Lookup for values from a column.

Financial Functions

Function

Description

Calculate the future value of an investment.

Calculate the periodic payment for a loan.

Calculate the present value of an investment.

Information Function

Function

Description

Get cell information

Return a number for every error.

Return information about current operating environment.

Check if a cell is blank.

Check if there is any error in the cell other than #N/A.

Check if a cell has an error.

Check if a cell has a even number.

Check if a cell has a has a formula.

Check if a cell has a has a logical value.

Check if a cell has a has a #N/A value.

Check if a cell has a has a non text value.

Check if a cell has a has a number.

Check if a cell has a has a odd number.

Check if a cell has a has a reference.

Check if a cell has a has a text value.

Convert a logical value into a number.

Generate a #N/A error value.

Get the sheet number of a worksheet.

Get number of sheets in a reference.

Get check the type of value from a cell.

  • Inet Kemp

    Great to have such a summary available

    • Puneet Gogia

      Thanks Inet

  • Rohit Sharma

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

    • Puneet Gogia

      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

  • Gautam Bhardwaj

    very helpful, i really appreciate. keep it up Bro

    • Puneet Gogia

      Thanks Gautam

  • Eanna

    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

    • Puneet Gogia

      Hello Eanna,

      Already Added.

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

  • Torstein S Johnsen

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

    • Puneet Gogia

      Hey Torstein,

      You will get the both.

      Thanks for words.

      You’ll get your E-book on 30 Apr

  • Brenda G

    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!

    • Puneet Gogia

      Thanks Brenda. I will for sure.

      You’ll get your E-book on 30 Apr

  • Diana

    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!

    • Puneet Gogia

      Alright Diana. Thanks for your reply.

  • Srinivasan Venkatraman

    Offset plays a vital role

    • Puneet Gogia

      Thanks Srinivasan

  • Angel Gonzalez

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

    • Puneet Gogia

      Thanks Angel

  • Asif Hafeez

    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.

    • Puneet Gogia

      Thanks Asif.

  • Peter Wilson

    Index or match

    • Puneet Gogia

      Thanks Peter

  • iyke

    OFFSET/MATCH AND ROW/COLUMN

    • Puneet Gogia

      Thanks Iyke.

  • iyke

    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.

    • Puneet Gogia

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

      • iyke

        Great! Puneet. I am looking forward to this.

  • Mohammed Mubin Sayyed

    OFFSET and exact use of Array with detailed example.

    • Puneet Gogia

      Thanks Mohammed Mubin Sayyed

  • Dan Kordelski

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

    • Puneet Gogia

      Thanks Dan.

  • Heather

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

    • Puneet Gogia

      Fully Agree With You. Thanks

  • Bj Sutton

    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)

    • Puneet Gogia

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

  • rathanak

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

  • ratanak

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

  • Binaya Karmacharya

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