Excel Formulas

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.

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

IFERROR

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.

CHOOSE

OFFSET Function

OFFSET – Dynamic Reference Video

INDIRECT Function

HYPERLINK Function

Financial Functions

Excel Bank Reconciliation Formula

Compound Interest on Savings Formula

FV Function

NPER Function

ACCRINT Formula

Math & Trig

SUMIF & SUMIFS Explained

SUBTOTAL Explained

ROUND, ROUNDUP, ROUNDDOWN

FLOOR and CEILING Functions

RAND and RANDBETWEEN

Excel SUMPRODUCT an alternative to SUMIFS

Statistical Functions

AVERAGE, AVERAGEIF and AVERAGEIFS Functions

COUNT, COUNTA and COUNTBLANK Functions

COUNTIF & COUNTIFS Explained

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

ISTEXT, ISNUMBER and ISBLANK Functions.

TEXT Function examples and a clever twist.

T Function

UPPER, LOWER and PROPER functions

SUBSTITUTE formula

TRIM, CHAR and SUBSTITUTE formula

Nested SUBSTITUTE formula trick

CLEAN formula

CONCATENATE

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

TIME

EOMONTH

EDATE

DATEDIF

NETWORKDAYS

Rounding Time

Convert time to decimals

Convert dates formatted as text strings to numbers

Information

N Function

Database Functions

DSUM, DAVERAGE, DCOUNT etc

Excel Charts

Excel Chart Axis Label Tricks

Excel Chart Secondary Axis

Excel Histogram Charts

In-cell Charts

Gantt Charts

Gantt Chart Template using Conditional Formatting

PivotTables

PivotTables Explained

PivotTable Tutorial

Auto-refresh PivotTables

Extract sub-sets of data using a PivotTable Report,

Reverse PivotTable

Add a Percentage of Total Column to a PivotTable

Group Data in a PivotTable

Compare Columns in a PivotTable

Other Tools and Tricks

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

EVALUATE Function

Didn’t find what you were looking for?

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

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 *

Comments

  1. Ashish Sharma says

    kindly please tell me how i convet number into words.

    Rupee 1500 in to one thousand five hundred only

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

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

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

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

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