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

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.

## Logical Functions

## Lookup & Reference

VLOOKUP with dynamic column reference

VLOOKUP and Sum Multiple Columns

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 – Dynamic Reference Video

## Financial Functions

Excel Bank Reconciliation Formula

Compound Interest on Savings Formula

## Math & Trig

Excel SUMPRODUCT an alternative to SUMIFS

## Statistical Functions

AVERAGE, AVERAGEIF and AVERAGEIFS Functions

COUNT, COUNTA and COUNTBLANK Functions

MIN, MAX, SMALL and LARGE functions

RANK, RANK.AVG and RANK.EQ functions

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

## Share This

## Text Functions

ISTEXT, ISNUMBER and ISBLANK Functions.

TEXT Function examples and a clever twist.

UPPER, LOWER and PROPER functions

TRIM, CHAR and SUBSTITUTE formula

Nested SUBSTITUTE formula trick

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

MID, LEN, REPT and FIND Functions

## Date and Time Functions

Convert dates formatted as text strings to numbers

## Information

## Database Functions

## Excel Formulas in 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.

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

Add a Percentage of Total Column to a Pivot Table

Compare Columns in a Pivot Table

## Other Tools and Tricks using Excel Formulas

Data Validation – Drop Down Lists

Conditonal Formatting with Formulas

Fix date formats using Text to Columns

Extract text strings using Text to Columns

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

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!

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,

Mynda

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.

Mynda

Mynda

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

Matthew says

Thanks so much. You have made my life so very easy.

Matt

Catalin Bombea says

You’re wellcome Matthew

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

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:

Cheers,

Catalin

Brad says

Thanks was playing around and was able to use a Max count and Vlookup to get the correct outcome…..

Catalin Bombea says

You’re wellcome

Ashish Sharma says

kindly please tell me how i convet number into words.

Rupee 1500 in to one thousand five hundred only

Catalin Bombea says

Hi Ashish,

Here is a UDF posted by Phil showing how to convert numbers to words.

In the code change Dollars and cents to your local currency.

Catalin

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.

Mynda Treacy says

Thanks, Tahir. It’s great to know we are able to help you master Excel.

Mynda

Mahesh Dhiman says

I Want How To Use 2 Sheet Combined Column By Column And Match

Mynda Treacy says

Hi Mahesh,

You haven’t given me much to go on but I suspect this tutorial might help you:

http://www.myonlinetraininghub.com/vlookup-multiple-values-in-multiple-columns

Kind regards,

Mynda.

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

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.

Peter field says

Thank you , this site is great and helps me a lot

Thank you

Mynda Treacy says

Cheers, Peter Glad we can help.

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.

Cheers,

Catalin

Amanda says

Great list of formulas, thanks for sharing

Philip Treacy says

Thanks Amanda. Glad you found it useful.

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

Mynda Treacy says

Hi Yogender,

Thanks for your kind comments. I’m glad you like our Excel Formulas list

I’ll email you direct about the Excel dashboard course.

Kind regards,

Mynda.

Gamaliel says

Hello Mynda,

Good day.

Hope you are well.

I am very interested about dashboard hope you can give me also.

I just read your message. I am just trying my luck if you can help me.

God bless to you.

Best regards,

Gamaliel.

Mynda Treacy says

Hi Gamaliel,

Thanks for your message. You can find out more about my Excel Dashboard course here.

Please let me know if you have any questions.

Kind regards,

Mynda.

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.

Mynda Treacy says

Wow. Tanks for your kind words, Paul.

It’s rewarding to know that what we do is both appreciated and really helping people.

Warm regards,

Mynda.