Excel Formulas

Excel FormulasExcel Formulas on Toolbar

Here you’ll find a list of common Microsoft Excel formulas and Excel functions explained in plain English, and applied to real life examples.

You won’t find any of that techno babble you get in the Excel Function Wizard here.

The Excel Tutorials are grouped in line with the Function Library so they’re easy to find when you need them.

And when you’re ready to take your Excel skills to the next level check out our Excel training course.

Excel Formulas – The Basics

Absolute References Explained

Excel Formulas Not Working? Get help here. In this video I share several tips that will help you understand any formula and why it’s not returning the correct result.

Tabular Data – the perfect data format and 5 other formats that will make your Excel life difficult.

Writing Formulas Efficiently

Named Ranges

Time calculations

Space Operator

Logical Functions

IF Statement Explained


Nested IF Formulas

IF, OR and AND used together

Lookup & Reference

VLOOKUP Exact Match version

VLOOKUP Sorted List version

VLOOKUP with dynamic column reference

VLOOKUP and Sum Multiple Columns

VLOOKUP Multiple Criteria

VLOOKUP Multiple Sheets

VLOOKUP Multiple Values in Multiple Columns

VLOOKUP with CHOOSE – a trick that allows you to look up columns to the left.

Lookup and Return Multiple Values

HLOOKUP Exact Match and Sorted List

INDEX and MATCH – a VLOOKUP alternative that solves some of its limitations.


OFFSET Function

OFFSET – Dynamic Reference Video



Financial Functions

Excel Bank Reconciliation Formula

Compound Interest on Savings Formula

FV Function

NPER Function


Math & Trig

SUMIF & SUMIFS Explained

SUBTOTAL Explained


FLOOR and CEILING Functions


Excel SUMPRODUCT an alternative to SUMIFS

Statistical Functions




STDEV & STDEVP Explained

MIN, MAX, SMALL and LARGE functions

RANK, RANK.AVG and RANK.EQ functions

Between Formula using MEDIAN

TRIMMEAN IF formula – find the average if criteria is met, and excludes outliers.

Text Functions


TEXT Function examples and a clever twist.

T Function

UPPER, LOWER and PROPER functions



Nested SUBSTITUTE formula trick

CLEAN formula


SEARCH and FIND Functions

5 Step System for nesting MID functions to extract text strings.

MID, LEN, REPT and FIND Functions

Convert Numbers into Words

Date and Time Functions






Rounding Time

Convert time to decimals

Convert dates formatted as text strings to numbers


N Function

Database Functions


Excel Formulas in Charts

Excel Chart Axis Label Tricks

Excel Chart Secondary Axis

Excel Histogram Charts

In-cell Charts

Gantt Charts

Gantt Chart Template using Conditional Formatting

Pivot Tables

GetPivotData Function – most people hate this function when they first see it but don’t be put off, it is well worth mastering.

Pivot Tables Explained

Pivot Table Tutorial

Auto-refresh Pivot Tables

Extract sub-sets of data using a Pivot Table Report,

Reverse Pivot Table

Add a Percentage of Total Column to a Pivot Table

Group Data in a Pivot Table

Compare Columns in a Pivot Table


Other Tools and Tricks using Excel Formulas

Evaluate Formula Tool

Excel Array Formulas

Excel Tables

Data Validation – Drop Down Lists

Custom Cell Formats

Conditional Formatting

Conditonal Formatting with Formulas

How to Apply Filters

Advanced Filters

How to insert Subtotals

Dynamic Named Ranges

Fix date formats using Text to Columns

Extract text strings using Text to Columns

How to Join Text Together

How to insert Outlines

Camera Tool

Shapes and SmartArt

Importing data into Excel

Array Formulas

Excel Wildcard

Excel Workspace

Excel Go To Special


Didn’t find what you were looking for?

Try our blog or use the Search box at the top of the page.

Failing that you could try Microsoft themselves, but they don’t always keep their site up to date :(

Excel Training Course

Are you ready to take your Excel skills to the next level? Check out our Excel training course.

Found This Useful?

If you found our explanations of Excel formulas and functions useful, please help us out by sharing this page through Facebook, Twitter, Google + and Linked In.

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *


  1. john pyskaty says

    Mynda, do you have any excel template sheets on the stock market for excel pivot table that has the ( date , high , low , close , volume , adj. close) for stock symbol information that I can use for (min. hour, day, week, month & year) of a stock web query, so i don’t have to go back to yahoo finance (historical prices). Mynda, I am 73 and have very little computer skills.
    Any help you can give would be appreciated

    John Pyskaty

    • Mynda Treacy says

      Hi John,

      Sorry, I don’t have anything like this and I’m not aware of any either. Have you tried a Google search?

      Kind regards,


  2. Mike says

    I have some “bad” data that I’m trying to purge from a spreadsheet. I have a column of e-mail addresses but many of the e-mail addresses are no good (do not have an “@” or a “.”). How can I write a formula or use a function to find all of these bad e-mail addresses in the column?
    Thank you!

    • Mynda Treacy says

      Hi Mike,

      Let’s say your addresses are in column A starting in cell A2; you could write this formula in cell B2:


      Formulas returning FALSE are ‘bad’ and you can then sort/filter to group them all together and then delete them.

      Kind regards,


      • Mike says

        Hi Mynda, thank you for the reply. I tried the formula you suggested but unfortunately it’s giving me a FALSE for every instance, not just those email addresses that do not contain an “@” or a “.”.
        For example, one of the cells in the Email Address column is “www.thevest.com”. Obviously not an e-mail address, so I’d like to be able to remove that.
        Another is “PHD”.
        So I’m hoping to find a formula that will verify that the “@” and the “.” are missing so I can delete those instances.
        Thanks again!

        • Mynda Treacy says

          Hi Mike,

          The formula should return a FALSE for http://www.thevest.com so it’s working correctly in that instance. Likewise for ‘PHD’. What I don’t have is an example of where the formula returns a FALSE when it should return TRUE. e.g. for mike@help.com the formula should return TRUE. You can then filter to show only the FALSE results and then delete them.

          It would be best if you raised a help desk ticket with your Excel file so we can see all of the data in context and you can cite examples of cells that you would expect to remove. That way we can test all of the variables.



  3. Matthew says


    I am trying to organise my “Next Action Due Column” I would like to have a field fill as red when the date contained in that field is todays date/current date. This is in order to help prioritise my to-do list. Can you advise how I would be able to do this?



    • Catalin Bombea says

      Hi Matthew,
      You have to create a conditionally formatting rule for the dates range. If the dates are in column D for example (D2:D500), the formula for the conditionally formatting rule will be:
      To create the rule, select D2:D500, from Home tab–>Styles group-Conditional Formatting–>New Rule–>Use a formula–>Type the above formula then select your formatting.
      This rule will highlight the cells that have the same date as today.

  4. esther says

    which formula do i use so that i can allow users to change % rate of inflation to see its impact on costs and sell prices? let’s say the inflation rate is 10%.

    • Catalin Bombea says

      Hi Esther,
      Can you please upload a sample file with details of your calculations? It’s hard to understand what are you trying to do without an example.
      Use our Help Desk system to upload a file.

  5. Brad says

    I couldn’t find what I was looking for, I don’t know what I need. I have 2 columns. One with a date and one with a number. I want to know the date and number of the most recent entry from another tab. how do I do this?


    • Catalin Bombea says

      Hi Brad,
      Please use our Help Desk to upload a sample file with your calculations and details of what you are trying to achieve, it;s much easier to see your data structure to give you a functional formula, otherwise you can try a general formula to get the latest date from column C:



  6. Ashish Sharma says

    kindly please tell me how i convet number into words.

    Rupee 1500 in to one thousand five hundred only

  7. Tahir says

    You Are working exceptional by spreading the knowledge for Microsoft Excel. As far as me is concerned, I got Much More from your source of communication.

  8. Catalin Bombea says

    Hi Emman, my opinion is that operation can only be done using VBA, you can try to use this code to select the used range from a worksheet, and paste it in another worksheet:
    Sub SelectUsedRange()

    Dim ws As Worksheet

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    ws1.UsedRange.AutoFilter field:=1, Criteria1:="<>"

    ws2.Paste ws2.Range("A1")

    ws1.AutoFilterMode = False
    Application.CutCopyMode = False

    End Sub
    Cheers, Catalin

    • emman says

      Hi Catalin,
      Thanks a lot for taken time for me n given this suggestion. But i don’t think its a exact solution for the above question…actually its a interview question which i was attended last Saturday. The meaning of question is to select all non blanks (number or text or both) through macros it should be highlighted?

      • Catalin Bombea says

        Hi Emman, the code will not select nonblanks, will just copy them to another sheet…
        There is another way, i know it from Rick Rothstein , but it’s not so intuitive, but if you do it several times, you will see the functionality:
        Here is the sequence of keys to use:
        Shift+8 (basically just do a search for any value, using the asterisk “*” wildcard)
        Ctrl+A (this will select all nonblanks actually)
        Alt+F4 (to close the search window)
        Cheers, Catalin

  9. CL Lim says

    Hi! Mynda Treacy,

    Thank you so much for your kindness in sharing such an informative tips and tricks to all Excel users. The formulas have been clearly explained and elaborated which can be easily understood by the readers. It really helps all of us and improve our knowledge in applying it in our job.

    A very sincere gratitude to you for sharing the knowledge to all Excel users.

    Thank you once again.

    Sincere regards,
    CL Lim

    • Mynda Treacy says

      Hi CL,

      Thank you for your kind words. It’s rewarding to know our tutorials are helpful.

      Kind regards,


      • Pete Soriano says

        Mynda, I hope you can help me with this. I have a worksheet containing several columns. Column A has the dates from January 1 through December 31. Column B is the name of particulars in each row. Column C is the corresponding amount in each row. I like to tell excel to give me the total amount for January in D1 and Feb in E2 and so on. Please help me with the formula and send it by email to facilitate getting my work done.
        Thank you.

        • Catalin Bombea says

          Hi Pete,
          the formula you should use is:


          You can add more criterias, if you need to.

  10. yogenderp excel says

    i am yogender andhra pradesh, India

    i know knowledge in excel
    i am working as excel trainer so i have learn advanced tip& tricks
    your website is very use full all learners

    further i would like join in online excel dashboards
    please send about dashboards

    thanking you

  11. Paul Durand says

    This is an excerpt of a letter that I sent to my friends and family. “I stumbled onto this site looking for a resolution to a problem on a complicated spreadsheet I was developing. I don’t usually have an ongoing need for that kind of skill set. So retaining that information isn’t something that I would normally do unless I happen to be using it repetitively. Long story short, this site as well as My Online Training Hub has restored my faith in a simple, readable explanation of Excel formulas not to mention all of the Microsoft office software. The information is free as is 150 hrs of free training. Additionally, they offer 100 Excel tips and tricks that is nothing less than functionally superb. If you have a need for this occasional skill set, or your children or grandchildren have that need, this is the place. If you have a more pressing or ongoing need, they offer training that is remarkably inexpensive.

    My problem was repetitive enough on my developing spreadsheet that I was totally stumped regardless of the research that I had done up to stumbling onto this site. I emailed them and they sent me a possible solution ( in less than 8 hours), which didn’t seem to work. They also offered to take a look at the spreadsheet if there suggestion didn’t work. I sent my template late one evening and got the answer back in less than 4 hours. Wow – in todays world??

    I can’t say enough about this site and encourage you to store this email of their website in a location that you can access, should you ever have the need for this type of info. The resolution to my problem was critical if I was going to move forward with a custom made budget projection workbook. This was going to be the crux of a business that is being formed. PROBLEM SOLVED. The info is a quick and easy read.