Excel Formulas

Excel FormulasToolbar

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

The 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 skills to the next level check out our Excel training course.

The Basics

Absolute References Explained – These are the $ signs you see in formulas, and they should be one of the first things you learn.

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

Writing Formulas Efficiently

Named Ranges – have so many applications and are a great time saving tip that makes building and deciphering formulas much quicker and easier

Time calculations – If you haven’t done so already it won’t be long before you have to work with time and dates. Here are some fundamental rules.

Space Operator – is not very well known, but you can put it to a range of uses. Pull this trick out at parties and people will think you’re some kind of superhero ;)

Logical Functions

IF Function – enables you to perform logical tests on your data. You can then tell Excel what to do if the test evaluates to TRUE, or if it evaluates to FALSE.

IFERROR – New in Excel 2007, this function allows you to hide the errors returned by formulas. A great example is when VLOOKUP evaluates to #N/A. No more nested IF(ISNA… formulas slowing down your workbook. Plus IFERROR is much more efficient and easy to understand.

Nested IF Formulas – This extends the number of criteria you can test but don’t get carried away nesting too many IF’s though.

IF, OR and AND used together – Now we’re cooking with fire. Nesting OR and AND with IF gives you even more logic to play with, extending the functionality of IF.

Lookup & Reference

VLOOKUP Exact Match version – This is my all-time favourite. Not because it’s the best, but because it’s one of the first I learnt that unleased the power of Excel for me. Check out the video tutorial as well as written lesson.

VLOOKUP Sorted List version – I discovered this version of VLOOKUP a long time after I’d master the exact match method. Although very similar, this allows you to apply it in quite a different way.

VLOOKUP with dynamic column reference – Enables you to automate updating the col_index_num argument so you can copy VLOOKUP across columns and not need to edit it.

VLOOKUP and Sum Multiple Columns – this allows you to lookup a value and sum a group of columns.

VLOOKUP Multiple Criteria – enables you to specify multiple criteria in your lookup value.

VLOOKUP Multiple Sheets – 3D VLOOKUP which enables you to look up many worksheets to locate the lookup value.

VLOOKUP Multiple Values in Multiple Columns – enables you to lookup multiple values and match them to multiple columns before returning your result. It uses a quirky IF function trick. I also show you how to do the same thing with INDEX & MATCH.

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

Lookup and Return Multiple Values – enables you to find multiple matches and return multiple results

HLOOKUP Exact Match and Sorted List – these are the same as VLOOKUP’s except on rows of data instead of columns.

INDEX and MATCH – a VLOOKUP alternative that solves some of its limitations like looking up to the left.

CHOOSE – on its own it isn’t all that special, but when you team it up with some other functions they become very clever.

OFFSET – a lot of people struggle to understand how the OFFSET function works, but I make it easy with my treasure map analogy.

OFFSET – Dynamic Reference Video

INDIRECT Function – this is best known for fixing a range of cells you want to reference.

HYPERLINK Function – hyperlinks can be inserted using the hyperlink tool, or you can write a formula. Here I share with you a shortcut to inserting hyperlink formulas.

Financial Functions

Excel Bank Reconciliation Formula – how to match debits and credits in a spreadsheet.

FV – for calculating compound interest on Savings

NPER – calculate how long it’ll take you to become a millionaire :-)

ACCRINT – calculates the accrued interest for a security that pays interest periodically.

Math & Trig

SUMIF & SUMIFS Explained – SUMIF enables you to employ logic to what you sum, and if you have Excel 2007 or later you can use the SUMIFS function to sum data based on multiple criteria.

SUBTOTAL Explained – this is one of those best kept secrets. It has so many different applications it’s a wonder more people don’t know about it.

ROUND, ROUNDUP, ROUNDDOWN – rounding numbers is made easy with this trio.

FLOOR and CEILING – These enable you to round the decimal places of a value to be divisible by a number you specify. For example, rounding up or down to the nearest 5 cents.

RAND and RANDBETWEEN – I use these all the time to generate random data for writing my tutorials but they have some other clever uses too.

SUMPRODUCT – is not only a great alternative for Excel 2003 users who wish they had SUMIFS, AVERAGEIFS and COUNTIFS. But it also has some other advantages which makes it more flexible than the *IFS range of built in functions.

Statistical Functions

AVERAGE, AVERAGEIF and AVERAGEIFS – these are much like SUM, SUMIF and SUMIFS except for average. No surprises there! But I also show you a clever way to use AVERAGEIFS with a data validation list to make it dynamically update.

COUNT, COUNTA and COUNTBLANK Functions – counting in Excel can be a bit tricky depending on whether you want to count numbers or text or both. Here I show you the ropes.

COUNTIF & COUNTIFS Explained – If you’ve used SUMIF/IFS you can probably guess that these functions allow you to count based on criteria. Here I show you a few examples using COUNTIF and COUNTIFS plus a video tutorial.

STDEV & STDEVP Explained – I remember Standard Deviation from my accounting days. Thankfully you can put away the calculator because it can be easily done in Excel.

MIN, MAX, SMALL and LARGE – there’s more to these that meets the eye. Here I share a couple of clever ways to use them.

RANK, RANK.AVG and RANK.EQ – We all want to know who came 1st, 2nd, 3rd, but what if there’s a tie? Check out this post to see solutions to tie breaks and other clever uses.

Between Formula using MEDIAN – there’s no such function as BETWEEN so here is a workaround using MEDIAN.

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

Text Functions

ISTEXT, ISNUMBER and ISBLANK. – Logical tests you can use to test if a range contains text, numbers or is empty.

TEXT – enables you to convert numbers to text. You might wonder why you’d ever need this so I’ve included some examples and a clever twist.

T Function – this is one of the shortest functions but it can have a big impact in the right scenario. Here I show you a few different ways you can put it to work.

UPPER, LOWER and PROPER – When you import data from another source it can often need tidying up before you can use it. These enable you to fix the case quickly and easily.

SUBSTITUTE – This is similar to find and replace except in formula form.

TRIM, CHAR and SUBSTITUTE – Use TRIM to remove spaces at the beginning and end of text. CHAR allows you to display characters by entering their code number. This is great if you want to show a character not on your keyboard. In this example I show you how you can use all 3 to handle the non-breaking space that sometimes gets imported when you copy data from a web page.

Nested SUBSTITUTE trick – Replace multiple items in one value.

CLEAN – This is great for cleaning data copied from a web page which contains some funky and usually unwanted characters.

CONCATENATE – there comes a time when you need to join text together. You can use the CONCATENATE function or you can use the ampersand symbol. I prefer the ampersand as it’s much quicker to type, but here I show you both methods.

SEARCH and FIND – These two are very similar. The main difference is SEARCH is not case sensitive, whereas FIND is.

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

MID, LEN, REPT and FIND – Rearranging data often requires teamwork from a myriad of functions. These are the usual suspects you’ll need to tackle most text rearranging jobs.

Convert Numbers into Words

Date and Time Functions

Rounding time and converting time to decimals – working with time in Excel can be quite challenging if you don’t know these tricks. Here I share with you my Date and Time 101, including a clever way to handle the problem of negative time often encountered when calculating hours worked by shift workers.

TIME – Here I share a workbook that converts time zones.

EOMONTH – I’d be lost without EOMONTH. I use it all the time (no pun intended) with SUMIFS/AVERAGEIFS/COUNTIFS to add up data by months.

EDATE – This is great for working out due dates or how overdue something is.

DATEDIF – you’d be forgiven for having never heard of DATEDIF. You won’t find it documented anywhere in Excel because it’s only available for backward compatibility. Here I show you how to use DATEDIF and a myriad of uses for it. It’s a shame it’s so secret.

NETWORKDAYS – if you do any sort of project planning then this is a great function to have in your tool belt. In this tutorial I team it up with TODAY and EOMONTH.

Convert dates formatted as text strings to numbers – this is handy if you import data from other systems and the dates come in as text instead of real dates.

Information

N Function – another very short function but quite handy. N enables you to put notes inside your formulas, much like a programmer would annotate their code to help them and others understand their logic. That wasn’t the original purpose if N, but it’s handy nonetheless.

Database Functions

DSUM, DAVERAGE, DCOUNT etc. – These are a great alternative to complex and slow array formulas.

Charts

Chart Axis Label Tricks – getting your axis labels right can make a huge difference to the readability of your charts and set your work apart from the amateurs who just use the defaults./p>

Chart Secondary Axis – It won’t be long before you find the need to plot data on a secondary axis. It can be used for plotting two sets of data with vastly differing ranges of values in the one chart…although it is not my preference. I prefer to separate the data into two charts then there’s no confusion over which axis is for which series.

Histogram Charts – Here I show you how to use the FREQUENCY function to compile your data into bins ready for plotting in a histogram.

In-cell Charts – If you don’t have Excel 2010 or later then these are an alternative to the new Sparklines.

Gantt Charts – Got a small project you’re working on? You can use this simple Gantt chart to visualise your schedule.

Gantt Chart Template using Conditional Formatting – This Gantt chart can be customised to suit your needs. It’s still really only good for small-ish projects.

Pivot Tables

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

Pivot Tables Explained – If you work with large volumes of data you must learn PivotTables. They might take a bit of practice but once you learn them it’s like riding a bike. You never forget and you wonder what you thought was so hard about them.

Pivot Table Tutorial – If you prefer to learn by watching as opposed to reading you can watch my PivotTable video tutorial.

Auto-refresh Pivot Tables – If you update your PivotTable data source regularly or you have lots of PivotTables in one workbook then this is a great timesaver.

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

Reverse Pivot Table – this is genius. If your data is not in the right format for a PivotTable you might be able to rearrange it in seconds using this tip.

Add a Percentage of Total Column to a Pivot Table – This is a clever way to analyse a column of values. View this tutorial as a video or read the written instructions.

Group Data in a Pivot Table – Grouping dates is a common need when working with PivotTables, but did you know you can also group ages into bands and other data. I show you how to do both in the video tutorial, or you can read the instructions.

Compare Columns in a Pivot Table – Month on month comparison of data is a common task. You can easily set up your PivotTable to do this for you. Watch my video tutorial or follow the set by step written instructions.

Slicers – New in Excel 2010 once you start using Slicers you’ll wonder how you lived without them. They’ll transform your PivotTables into easy to use professional looking report.

Other Tools and Tricks

Evaluate Formula Tool – If you want to see under the Excel hood then this is your secret window. It’s an essential tool for debugging and writing complex formulas.

Tables and Structured References – In Excel 2007 these had a revamp and are now a must have for your tool belt. They can make writing and maintaining formulas quick and easy by utilising the automatic dynamic ranges they have known as Structured References. Learn them and you’ll never look back.

Data Validation – Drop Down Lists – who doesn’t like a drop down list? They have so many uses. In this tutorial I also show you a clever way you can make your data validation list dynamic.

Custom Cell Formats – check out this list of clever custom formats. I even include a solution to formatting credit card numbers, which can’t be done with a custom number format.

Conditional Formatting – There are a load of built in conditional formats you can put to work right away without too much fuss.

Conditonal Formatting with Formulas – if the built in Conditional Formats aren’t cutting it you probably need to write a custom format. I tell you, they can be confusing and frustrating if you don’t know these key rules.

How to Apply Filters – filters allow you to quickly analyse your data by filtering out the stuff you don’t want to see.

Advanced Filters – so much more than just filters. There’s a myriad of ways you can use Advanced Filters which I cover in this tutorial.

How to insert Subtotals – this tool allows you to quickly summarise your data. It automatically adds the SUBTOTALs and groups and outlines your data. I used this a lot when working with large amounts of data that I didn’t want in a PivotTable but still wanted the ability to quickly summarise it.

Dynamic Named Ranges – these are an advanced trick you must know. They allow you to write formulas so that you never have to update them when you add more data to your range.

Fix date formats using Text to Columns – if you don’t like the MID, LEFT and RIGHT functions for rearranging your data then Text to Columns is a much easier option for fixing up data you’ve imported from another source.

Extract text strings using Text to Columns

How to Join Text Together – This is the CONCATENATE and Ampersand tutorial from the ‘Text Functions’ section above. I’ve listed it again because it’s a must know tip.

How to insert Outlines – Ever wondered how to insert those nice grouping buttons the Subtotal tool does for you automatically? I show you how here.

Camera Tool – It’s not easy to find, but I show you where it’s hiding and a great way you can use it in dashboard type reports. I also show you a cool trick where you can use it with a formula to dynamically update the picture displayed.

Shapes and SmartArt – add depth to your workbooks with Shapes and SmartArt. You can even make the text it displays dynamic. Or link them to Macros and use them as buttons plus much more.

Importing data – In this tutorial I show you how to import data from Access, Web pages and Text or CSV files. Plus how to handle importing data without delimiters.

Array Formulas – These are the formulas that have the {curly braces}. They have a few nuances which set them apart but in this tutorial I lift the lid on them and step you through the logic of how they work.

Wildcards – There are a few different types of wildcards and in this tutorial I show you how you can use them with COUNTIF, SUMIF and VLOOKUP.

Excel Workspace – this is a great time saving tip for those of us who open the same group of workbooks regularly as it allows you to list a group of workbooks together and open them with one click, plus some other cool features.

Go To Special – I love this tool. It has so many features and in this tutorial I show you how you can use it to quickly remove blanks from your data.

EVALUATE – this is another secret function you won’t find in the function list. It’s acutally an Excel v4.0 macro function and in this tutorial you can learn a clever way to use it with data validation lists.

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

Found This Useful?

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

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 *

Comments

  1. Darell says

    I see you doing many successful things in EXCEL and I am not smart enough to tackle the following problem using VBA. I tried for about 3 weeks and was partially successful using CMD functions but it required a lot of time editing my EXCEL file to get good info. I am using EXCEL 2010 and on Windows 8.1 operating system. In the old windows and EXCEL I could copy and paste.Not anymore…

    I am trying to find a way to copy search folders and put them in an EXCEL file preferably with links to original location. I can’t find a way to create a directory listing of the files I have on various hard drive directories. If I had them in EXCEL I could sort and search for the files I want to find. I have well over 100,000 files in various directories. Any help or suggestions would be appreciated. Thanks,Darell

  2. raja munidasa says

    i have learn excel vba on my own by trial and error basis spending lot of time.first time i found a web site which gives so clear instruction to learn excel.
    it is a great community service.as a buddhist i believe sharing knowledge with others make you be born with intelligence and a life time satisfaction you can get for knowing that so many others are benefitted by your kindness and knowledge.

    thank you so much!

  3. Jo says

    I’ve added Check boxes to my spreadsheet and linked it to another spreadsheet so then when checked or not it displays “True” or “False”. This isn’t appropriate for what I’m using it for so I’m trying to change this so it displays Checked = “YES” or not checked = “NO”. I can’t seem to get my IF formula to work … am I heading the right direction? Or way off base?? HELP!!

    • Mynda Treacy says

      Hi Jo,

      You can’t change what appears in the linked cell, but you can link another cell containing a formula to the linked cell. For example, if your linked cell is G1 then in H1 you could enter this formula:

      =IF(G1=TRUE,"Yes","No")

      Then in H1 you will see the Yes if the box is checked, or No if it’s not.

      Hope that helps.

      Mynda

  4. Kennet Danielsen says

    Hi Mynda.

    Always a fan.

    http://www.myonlinetraininghub.com/excel-formulas is a god site for a quick view on formulas.

    If i may suggest that you expand it slightly to be a little more helpful.

    If you at each formula name added a few words describing what it basically is for. then you could CTRL+F the page with keywords and mind find exactly what you need.

    Just a suggestiong though, since i know it is a tedious task.

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

      Mynda

  6. Mike says

    Hi,
    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!

  7. Matthew says

    Hi

    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?

    Thanks

    Matt

    • 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:
      =$D6=TODAY()
      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.
      Catalin

  8. 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%.
    thanks

    • 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.
      Thanks,
      Catalin

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

    Thanks

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

      =INDEX(Sheet6!C1:C100,SUMPRODUCT((Sheet6!C4:C100=MAX(Sheet6!C4:C100))*ROW(Sheet6!C4:C100)))

      Cheers,
      Catalin

  10. Ashish Sharma says

    kindly please tell me how i convet number into words.

    Rupee 1500 in to one thousand five hundred only

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

  12. 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:="<>"
    ws1.UsedRange.Copy

    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:
        Ctrl+F
        Shift+8 (basically just do a search for any value, using the asterisk “*” wildcard)
        Alt+I
        Ctrl+A (this will select all nonblanks actually)
        Alt+F4 (to close the search window)
        Cheers, Catalin

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

      Mynda.

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

          =SUMIFS($C$2:$C$16,A2:A16,">=01/01/2013",A2:A16,"<01/02/2013")

          You can add more criterias, if you need to.
          Cheers,
          Catalin

  14. yogenderp excel says

    hi
    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
    yognder

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