101 Excel Functions With Examples

    useful excel functionsExcel Functions are one of the most important features in Excel.

    This is must learn Excel formulas to make best out of this best spreadsheet tool on the planet.

    This section is purely dedicated to the useful Excel functions.

    You can learn these Excel functions with examples & download sample files for your better understanding.

    This comprehensive list of 101 Excel functions will help you to drive your Excel skills to the next level. You can learn each of them with real life examples.

    I have integrated these formulas in the categories so that you can learn them in the way your want.

    TEXT

    LEFTGet text from the left side of a cell.
    RIGHTGet text from the right side of a cell.
    MIDGet text from a specific position from a text string.
    LOWERConvert text into lower case.
    UPPERConvert text into upper case.
    PROPERConvert text into the proper case.
    REPTRepeat a specific text for a specific number of times.
    LENCount number of character in a cell.
    FINDFind case sensitive text from an another text string.
    SEARCHSearch non-case sensitive text from an another text string.
     

    DATE & TIME

    DATECreate a valid date.
    DATEDIFCalculate interval between two dates.
    DATEVALUECreate a valid date from text.
    EDATEReturn the same date in future or past.
    DAYReturn day number from a date.
    DAYSReturn number of days between two dates.
    TODAYReturn current date.
    MONTHReturn month’s number from a date.
    EOMONTHReturn the last date of month in future or past.
    YEARReturn year’s number from a date.
    TIMECreate a valid time.
    TIMEVALUECreate a valid time from text.
    HOURReturn hours from a time value.
    MINUTEReturn minute from a time value.
    SECONDReturn seconds from a time value.
    NOWReturn current date & time.
    WEEKDAYReturn weekday of a given date.
    WEEKNUMReturn week number of a given date.
    NETWORKDAYSReturn the number of working days between two date.
    NETWORKDAYS.INTLReturn the work days between two dates.
     

    LOGICAL

    IFPerform a specific task if a condition is true or false.
    IFERROREvalue a value for error & return a specific value if error occurs.
    IFNAEvalue a value for #N/A error & return a specific value if error occurs.
    ORTest two or more conditions differently.
    ANDTest two or more conditions jointly.
    NOTConvert TRUE into FALSE & FALSE into TRUE.
    FALSEReturn logical FALSE.
    TRUEReturn logical TRUE.
     

    MATHS

    SUMSum numeric values.
    SUMIFSum numeric values which meet a condition.
    SUMIFSSum numeric values which meet multiple conditions.
    SUMPRODUCTMultiply & Sum Arrays.
    ABSConvert a number into an absolute number.
    EVENRound a number to the nearest even number.
    INTRound down a number to the nearest integer.
    MODReturn remainder after dividing two numbers.
    MROUNDRound to the nearest multiple of a number.
    TRUNCRemove fractional part of a number.
    RANDGenerate Random Numbers.
     

    STATISTICAL

    COUNTReturn the count cells which have numbers.
    COUNTAReturn the count of cells which are non-blank.
    COUNTBLANKReturn the count of cells which are blank.
    COUNTIFReturn the count of cells which meet criteria.
    COUNTIFSReturn the count of cells which meet multiple criteria.
    AVERAGEReturn average of numbers.
    AVERAGEAReturn the average of a group of numbers and text.
    AVERAGEIFReturn the average of numbers which meet criteria.
    AVERAGEIFSReturn the average of numbers which meet multiple criteria.
    MAXReturn largest values from a given set of values.
    MINReturn smallest values from a given set of values.
     

    LOOKUP

    ADDRESSCreate a cell address.
    AREASReturn a number of areas in a reference.
    CHOOSEReturn a value from a list based on position.
    COLUMNReturn the column number of the reference.
    COLUMNSReturn the number of columns in a range.
    FORMULA TEXTReturn formula from a cell as a text.
    HLOOKUPLookup for values from a row.
    HYPERLINKCreate an Hyperlink in a cell.
    INDEXReturn a value from a list or table based on location.
    INDIRECTCreate a cell reference from a text.
    LOOKUPLookup for a values in a column range.
    MATCHReturn a number representing the position of a cell in an array.
    OFFSETCreate a reference offset from given starting cell.
    ROWReturn the row number of the reference.
    ROWSReturn the number of rows in a range.
    TRANSPOSEReturn the average of numbers.
    VLOOKUPLookup for values from a column.
     

    FINANCIAL

    FVCalculate the future value of an investment.
    PMTCalculate the periodic payment for a loan.
    PVCalculate the present value of an investment.
     

    INFORMATION

    CELLGet cell information.
    ERROR.TYPEReturn a number for every error.
    INFOReturn information about current operating environment.
    ISBLANKCheck if a cell is blank.
    ISERRCheck if there is any error in the cell other than #N/A.
    ISERRORCheck if a cell has an error.
    ISEVENCheck if a cell has a even number.
    ISFORMULACheck if a cell has a has a formula.
    ISLOGICALCheck if a cell has a has a logical value.
    ISNACheck if a cell has a has a #N/A value.
    ISNONTEXTCheck if a cell has a has a non text value.
    ISNUMBERCheck if a cell has a has a number.
    ISODDCheck if a cell has a has a odd number.
    ISREFCheck if a cell has a has a reference.
    ISTEXTCheck if a cell has a has a text value.
    NConvert a logical value into a number.
    NAGenerate a #N/A error value.
    SHEETGet the sheet number of a worksheet.
    SHEETSGet number of sheets in a reference.
    TYPEGet 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