101 Excel Functions

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