Top 100 Excel Functions with Examples + Sample Files

Top 100 Excel Functions with Examples + Sample Files2018-08-17T13:56:40+00:00

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 end, its usage through a simple example so that you can use it in real life.

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. REPT Function: It repeats a text which you specify (the number of times). You just need to specify the text and the repetition number.
  8. 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.
  9. 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.
  10. 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

(C). Time Functions


(D). Logical Functions


(E). Mathematical Functions


(F). Statistical Functions


(G). Lookup Functions


(H). Financial Functions


(I). Information Functions

  • Santhu ycs says:

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

  • Karan Jauhar says:

    I want to have an example as well after explaining

  • ahmed reza says:

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

  • Gopal Prasad says:

    Gogia sir, plz suggest solution, if any

  • Gopal Prasad says:

    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

    • Puneet Gogia says:

      You have already sorted in above snapshot?

      • Gopal Prasad says:

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

  • Naveen Kumar says:

    please give formulas and function in the pdf formet

  • Naran says:

    Thanks Puneet for your support

  • Umesh Misra says:

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

  • NIKHIL PARMAR says:

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

  • WaywardSailor says:

    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.

  • G G AMBWANI says:

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

  • Satheesh Kumar.Ps says:

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

  • sunil mishra says:

    How Can I creat a daly sale report in excel
    please help me

  • Emmanuel Martins Udu says:

    Puneet, you are simply amazing.

  • Ryan says:

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

  • Om says:

    Hi,
    Please provide the link for Inv. Management System

  • Sathishkumar Manasa says:

    very nice useful puneet how i download this excel function book

  • Sudhir Kumar says:

    How can I create a timezone convertor??

  • Abhishekkumar Jasoriya says:

    Thanks punnet for providing such a good platform to learn excel

  • Ron007 says:

    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

  • Binaya Karmacharya says:

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

  • ratanak says:

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

  • rathanak says:

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

  • Bj Sutton says:

    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 says:

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

  • Heather says:

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

    • Puneet Gogia says:

      Fully Agree With You. Thanks

  • Dan Kordelski says:

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

    • Puneet Gogia says:

      Thanks Dan.

  • Mohammed Mubin Sayyed says:

    OFFSET and exact use of Array with detailed example.

    • Puneet Gogia says:

      Thanks Mohammed Mubin Sayyed

  • iyke says:

    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 says:

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

      • iyke says:

        Great! Puneet. I am looking forward to this.

  • iyke says:

    OFFSET/MATCH AND ROW/COLUMN

    • Puneet Gogia says:

      Thanks Iyke.

  • Peter Wilson says:

    Index or match

    • Puneet Gogia says:

      Thanks Peter

  • Asif Hafeez says:

    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 says:

      Thanks Asif.

  • Angel Gonzalez says:

    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 says:

      Thanks Angel

  • Srinivasan Venkatraman says:

    Offset plays a vital role

    • Puneet Gogia says:

      Thanks Srinivasan

  • Diana says:

    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 says:

      Alright Diana. Thanks for your reply.

  • Brenda G says:

    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 says:

      Thanks Brenda. I will for sure.

      You’ll get your E-book on 30 Apr

  • Torstein S Johnsen says:

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

    • Puneet Gogia says:

      Hey Torstein,

      You will get the both.

      Thanks for words.

      You’ll get your E-book on 30 Apr

  • Eanna says:

    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 says:

      Hello Eanna,

      Already Added.

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

  • Gautam Bhardwaj says:

    very helpful, i really appreciate. keep it up Bro

    • Puneet Gogia says:

      Thanks Gautam

  • Rohit Sharma says:

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

    • Puneet Gogia says:

      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

  • Inet Kemp says:

    Great to have such a summary available

    • Puneet Gogia says:

      Thanks Inet

  • >