MS EXCEL: ALL FORMULAS/FUNCTIONS - LISTED BY

CATEGORY
We have provided a list of all Excel formulas (both worksheet formulas and VBA functions).
The Excel formulas are sorted by category. There are 2 type of functions in Excel Worksheet formulas and VBA functions.
Worksheet formulas are Excel functions that are used as part of a formula within a cell in
your worksheet. These are the most basic functions used when learning Excel.
VBA functions expand on the basics of Excel and allow you to use Excel's programming
environment. VBA functions are Excel functions that are used in VBA (Visual Basic for
Applications) code through modules and macros.
Below is the list of Excel formulas, sorted by category (ie: type of function).

STRING FUNCTIONS
ASC (VBA)CHAR (WS)CHR (VBA)CLEAN (WS)CODE (WS)Concat with & (WS,
VBA)CONCATENATE (WS)CURDIR (VBA)DOLLAR (WS)EXACT (WS)FIND (WS)FIXED
(WS)FORMAT Strings (VBA)INSTR (VBA)
INSTRREV (VBA)LCASE (VBA)LEFT (WS, VBA)LEN (WS, VBA)LOWER (WS)LTRIM
(VBA)MID (WS, VBA)PROPER (WS)REPLACE (WS)REPLACE (VBA)REPT (WS)RIGHT
(WS, VBA)RTRIM (VBA)SEARCH (WS)
SPACE (VBA)STR (VBA)STRCOMP (VBA)STRCONV (VBA)SUBSTITUTE (WS)T
(WS)TEXT (WS)TRIM (WS, VBA)UCASE (VBA)UPPER (WS)VAL (VBA)VALUE (WS)

NUMERIC / MATHEMATICAL FUNCTIONS

ABS (WS, VBA)ACOS (WS)ACOSH (WS)AGGREGATE (WS)ASIN (WS)ASINH
(WS)ATAN (WS)ATAN2 (WS)ATANH (WS)ATN (VBA)AVERAGE (WS)AVERAGEA
(WS)AVERAGEIF (WS)AVERAGEIFS (WS)BIN2DEC (WS)BIN2HEX (WS)BIN2OCT

(WS)CEILING (WS)CEILING.PRECISE (WS)COMBIN (WS)COMBINA (WS)COMPLEX

(WS)CONVERT (WS)COS (WS, VBA)COSH (WS)COUNT (WS)COUNTA
(WS)COUNTBLANK (WS)COUNTIF (WS)COUNTIFS (WS)DEGREES (WS)
EVEN (WS)EXP (WS, VBA)FACT (WS)FIX (VBA)FLOOR (WS)FORMAT Numbers
(VBA)FREQUENCY (WS)INT (WS, VBA)LARGE (WS)LINEST (WS)LN (WS)LOG (WS,
VBA)LOG10 (WS)MAX (WS)MAXA (WS)MDETERM (WS)MEDIAN (WS)MIN (WS)MINA
(WS)MINVERSE (WS)MMULT (WS)MOD (WS)N (WS)ODD (WS)PI (WS)POWER
(WS)PRODUCT (WS)RADIANS (WS)RAND (WS)RANDBETWEEN (WS)RANDOMIZE
(VBA)
RND (VBA)ROMAN (WS)ROUND (WS)ROUND (VBA)ROUNDDOWN (WS)ROUNDUP
(WS)SGN (VBA)SIGN (WS)SIN (WS, VBA)SINH (WS)SMALL (WS)SQRT
(WS)SUBTOTAL (WS)SUM (WS)SUMIF (WS)SUMIFS (WS)SUMPRODUCT
(WS)SUMSQ (WS)SUMX2MY2 (WS)SUMX2PY2 (WS)SUMXMY2 (WS)TAN (WS,
VBA)TANH (WS)TRUNC (WS)VAL (VBA)VALUE (WS)VAR (WS)VARA (WS)VARP
(WS)VARPA (WS)

STATISTICAL FUNCTIONS
AGGREGATE (WS)AVEDEV (WS)AVERAGE (WS)AVERAGEA (WS)AVERAGEIF
(WS)AVERAGEIFS (WS)BETA.DIST (WS)BETA.INV (WS)BETADIST (WS)BETAINV
(WS)BINOM.DIST (WS)BINOM.INV (WS)BINOMDIST (WS)CHIDIST (WS)CHIINV (WS)
CHITEST (WS)COVAR (WS)FORECAST (WS)GROWTH (WS)INTERCEPT (WS)LARGE
(WS)LINEST (WS)MAX (WS)MAXA (WS)MEDIAN (WS)MIN (WS)MINA
(WS)PERCENTILE (WS)PERCENTRANK (WS)PERMUT (WS)

PI (WS)QUARTILE (WS)RANK (WS)SLOPE (WS)SMALL (WS)STDEV (WS)STDEVA

(WS)STDEVP (WS)STDEVPA (WS)VAR (WS)VARA (WS)VARP (WS)VARPA (WS)

LOGICAL FUNCTIONS
AND (WS)AND (VBA)CASE (VBA)FALSE (WS)
IF (WS)IF-THEN-ELSE (VBA)IFs (more than 7) (WS)IFs (up to 7) (WS)
NOT (WS)OR (WS)OR (VBA)TRUE (WS)

INFORMATION FUNCTIONS
CELL (WS)ERROR.TYPE (WS)INFO (WS)ISBLANK (WS)ISDATE (VBA)ISEMPTY
(VBA)ISERR (WS)
ISERROR (WS, VBA)ISLOGICAL (WS)ISNA (WS)ISNONTEXT (WS)ISNULL
(VBA)ISNUMBER (WS)ISNUMERIC (VBA)
ISREF (WS)ISTEXT (WS)N (WS)NA (WS)TYPE (WS)

DATE / TIME FUNCTIONS

DATE (WS)DATE (VBA)DATEADD (VBA)DATEDIF (WS)DATEDIFF (VBA)DATEPART
(VBA)DATESERIAL (VBA)DATEVALUE (WS, VBA)DAY (WS, VBA)
DAYS360 (WS)FORMAT Dates (VBA)HOUR (WS, VBA)MINUTE (WS, VBA)MONTH
(WS, VBA)MONTHNAME (VBA)NETWORKDAYS (WS)NETWORKDAYS.INTL
(WS)NOW (WS, VBA)
SECOND (WS)TIME (WS)TIMESERIAL (VBA)TIMEVALUE (WS, VBA)TODAY
(WS)WEEKDAY (WS, VBA)WEEKDAYNAME (VBA)YEAR (WS, VBA)

DATABASE FUNCTIONS

DAVERAGE (WS)DCOUNT (WS)DCOUNTA (WS)DGET (WS)

DMAX (WS)DMIN (WS)DPRODUCT (WS)DSTDEV (WS)
DSTDEVP (WS)DSUM (WS)DVAR (WS)DVARP (WS)

DATA TYPE CONVERSION FUNCTIONS

CBOOL (VBA)CBYTE (VBA)CCUR (VBA)CDATE (VBA)
CDBL (VBA)CDEC (VBA)CINT (VBA)CLNG (VBA)
CSNG (VBA)CSTR (VBA)CVAR (VBA)

FINANCIAL FUNCTIONS
ACCRINT (WS)ACCRINTM (WS)AMORDEGRC (WS)AMORLINC (WS)DB (WS)DDB
(WS, VBA)FV (WS, VBA)
IPMT (WS, VBA)IRR (WS, VBA)ISPMT (WS)MIRR (WS, VBA)NPER (WS, VBA)NPV
(WS, VBA)PMT (WS, VBA)
PPMT (WS, VBA)PV (WS, VBA)RATE (WS, VBA)SLN (WS, VBA)SYD (WS, VBA)VDB
(WS)

FILE / DIRECTORY FUNCTIONS

CHDIR (VBA)CHDRIVE (VBA)CURDIR (VBA)
DIR (VBA)FILEDATETIME (VBA)FILELEN (VBA)
GETATTR (VBA)MKDIR (VBA)SETATTR (VBA)

LOOKUP / REFERENCE FUNCTIONS

ADDRESS (WS)AREAS (WS)CHOOSE (WS, VBA)COLUMN (WS)COLUMNS
(WS)HLOOKUP (WS)

HYPERLINK (WS)INDEX (WS)INDIRECT (WS)LOOKUP (WS)MATCH (WS)OFFSET

(WS)
ROW (WS)ROWS (WS)SWITCH (VBA)TRANSPOSE (WS)VLOOKUP (WS)

MORE LOOKUP FUNCTIONS

Two-Dimensional Lookup (Example #1)Two-Dimensional Lookup (Example #2)TwoDimensional Lookup (Example #3)Two-Dimensional Lookup (Example #4)

MISCELLANEOUS FUNCTIONS
Test a string for an alphanumeric valueConvert currency into wordsHow to Autonumber in
ExcelDelimit values with ASCII 29 characterCreating a custom round functionCreating a
custom average function (that excludes outlyers from the calculation)

MICROSOFT EXCEL:
FORMULAS AND FUNCTIONS /
HOW-TO GEEK SCHOOL
Lesson 2: Defining and Creating a Formula

In this lesson, we introduce you to basic rules for creating formulas and using functions. We feel
one of the best ways to learn is through practice, so we provide several examples and explain
them in detail. The topics we will cover include:

SCHOOL NAVIGATION
1.

Why Do You Need Formulas and Functions?

2.

Defining and Creating a Formula

3.

Relative and Absolute Cell Reference, and Formatting

4.

Useful Functions You Should Get to Know

5.

Lookups, Charts, Statistics, and Pivot Tables

rows and columns

example math function: SUM()

operators

operator precedence

example financial function: PMT(), loan payment

using a string function (string is shorthand for string of text) inside a formula and
nesting functions

Formulas are a mixture of functions, operators, and operands. Before we write a few
formulas, we need to create a function but before we can create a function, we first need to
understand row and column notation.

Rows and Columns

To understand how to write formulas and functions, you need to know about rows and columns.
Rows run horizontally and columns run vertically. To remember which is which, think of a column
holding up a roof columns go up-down and thus rows go left-right.

Columns are labeled by letters; rows by numbers. The first cell in the spreadsheet is A1
meaning column A, row 1. The columns are labeled A-Z. When the alphabet runs outs, Excel
places another letter in front: AA, AB, AC AZ, BA, BC, BC, etc.
Example: Function Sum()
Now lets demonstrate how to use a function.
You use functions by typing them directly in or using the function wizard. The function wizard
opens when you either pick a function from the Formulas menu from the Function Library.
Otherwise, you can type = in a cell and a handy drop-down menu will allow you to pick a
function.

The wizard tells you what arguments you need to provide for each function. It also provides a
link to online instructions if you need help understanding what the function does and how to use
it. For example, if you type =sum into a cell, the in-line wizard shows you what arguments are
required for the SUM function.

When you type a function, the wizard is inline or right at your fingers. When you select a
function from the Formulas menu, the wizard is a pop-up box. Here is the pop-up wizard for
the SUM() function.

For our first function, lets use SUM(), which adds a list of numbers.
Suppose we have this spreadsheet to contain plans for budgeting your familys vacation:

To calculate the total costs you could write =b2+b3+b4+b5 but its easier to use the SUM()
function.
In Excel, look for the symbol at the top left-hand corner of the Excel screen to find the
AutoSum button (mathematicians use the Greek letter for adding a series of numbers).

If the cursor is below the family budget numbers, Excel is smart enough to know that you want
to sum the list of numbers above where you placed the cursor, so it highlights the numbers.

Press enter to accept the range selected by Excel or use the cursor to change which cells are
selected.
If you look at what Excel put into the spreadsheet you can see that it wrote this function:

In this formula, Excel sums the numbers from B2 to B9. Note, we left some room below row 5 so
that you could add to the family vacation budget the cost will certainly go up as the kids list of
what they want to do and where they want to go grows longer!
Math functions do not work with letters, so if you put letters into the column the result is shown
as #NAME? as shown below.

#NAME? indicates that there is some kind of error. It could be any number of things including:

bad cell reference

using letters in math functions

omitting required arguments

spelling function name wrong

illegal math operations like division by 0

The easiest way to select the arguments in a calculation is to use the mouse. You can add to or
remove from the list of arguments to the function by enlarging or making smaller the box that
Excel draws when you move the mouse or click in another cell.
We have clicked on the top of the square drawn by Excel to take airline tickets out of the
budget. You can see the cross-hair symbol that you can draw to make the selected range larger
or smaller.

Press enter to confirm the results.

Calculation Operators
There are two types of operators: math and comparison.
Math
Operator

Definition

addition

subtraction, or negation, e.g., 6 * -1 = -6

multiplication

division

percent

exponent, e.g. 24 = 2 ^ 4 = 2 * 2 * 2 * 2 =16

There are other operators not related to mathematics like & which means concatenate (join
end-to-end) two strings. For example, =Excel & is Fun equals Excel is Fun.
Now we look at comparison operators.
Comparis

Definition

on
Operator
=

equals, e.g., 2=4 or b = b

>

greater than, e.g., 4 > 2 or b > a

<

less than, e.g., 2 < 4 or a < b

>=

greater than or equal to another way to think of this is >=

meanseither > or =.

<=

less than or equal to.

<>

not equal to, e.g., 4<>6

As you can see above, comparison operators work with numbers and text.
Note, if you enter =a>b into a cell it will say FALSE since a is not greater than b. b
comes after a in the alphabet, so a > b or b > a.

Operator Order Precedence

Order precedence is an idea from mathematics. Excel has to follow the same rules as
mathematics. This topic is more complicated, so take a breath and lets dive in.
Order precedence means the order in which the computer calculates the answer. As we
explained in Lesson 1, the area of a circle is r2, which is the same as * r * r. It is not (r)2.
So you have to understand the order precedence when you write a formula.
Generally, you can say this:
1.

Excel first evaluates items in parentheses working inside out.

2.

It then uses the order precedence rules of mathematics.

3.

When two items have the same precedence, Excel works left to right.

The precedence of math operators is shown below, in descending order.

( and )

When parentheses are used, they override the normal rules

of precedence. This means that Excel will do this calculation
first. We explain this further below.

Negation, e.g., -1. This is the same as multiplying a number

by -1. -4 = 4 * (-1)

Percent, means multiply by 100. E.g., 0.003 = 0.3%.

Exponent, e.g., 10 ^ 2 = 100

* and /

Multiply and divide. How can two operators have the same
precedence? It just means that if a formula has two more
operators with the same precedence, then the calculation is
done left to right.

+ and -

Addition and subtraction.

There are other precedence rules related to strings and reference operators. For the moment,
we will just stick with what we just covered. Now, lets look at some examples.
Example: Calculating the area of a circle
The area of a circle is =PI() * radius ^ 2.
Looking at the table above we see that exponents comes before multiplication. So the computer
first calculates radius ^ 2 and then it multiples that result by Pi.
Example: Calculating a raise in salary
Lets say your boss decides youre doing a great job and he or she is going to give you a 10%
raise! How would you calculate your new salary?
First, remember that multiplication comes before addition.
Is it =salary + salary * 10% or is it =salary + (salary * 10%)?
Suppose your salary is $100. With a 10% raise, your new salary will be:
= 100 + 100 * 10% = 100 + 10 = 110
You can also write it like this:

=100 + (100 * 10%) = 100 + 10 = 110

In the second case, we have make the order of precedence explicit by using parentheses.
Remember that parentheses are evaluated before any other operation.
By the way, the easier way to write this is = salary * 110%
Parentheses can be nested inside one another. So, when we write (3 + (4 * 2)), working from
inside to outside, first calculates 4 * 2 = 8, then add 3 + 8 to get 11.

A few more examples

Here is another example: = 4 * 3 / 2. What is the answer?
We see from the rules in the table above that * and / have equal precedence. So Excel works
from left to right, 4 * 3 = 12 first, then divides that by 2 to get 6.
Again you could make that explicit by writing = (4 * 3) / 2
What about = 4 + 3 * 2?
The computer sees both * and + operators. So following the rules of precedence (multiplication
comes before addition) it calculates 3 * 2 = 6 first, then adds 4 to get 10.
If you wanted to change the order of precedence you would write = (4 + 3) * 2 = 14.
What about this one = -1 ^ 3?
Then answer is -3 because the computer calculated = (-1) ^ 3 = -1 * -1 * -1 = -1.
Remember that negative times negative is positive and a negative times a positive is negative.
You can see this like this (-1 * -1) * -1 = 1 * -1 = -1.
So theres a few examples of mathematical order and precedence, we hope that helps clear a
few things about how Excel performs calculations (and thats probably enough math to last a
lifetime for some of you).
Example: Function Loan Payment (PMT)
Lets look at an example to calculate a loan payment.
Start by creating a new worksheet.

Format the numbers with dollar signs and use zero decimal places since we are not interested
in cents right now because they do not matter much when you are talking about dollars (in the
next chapter we explore how to format numbers in detail). For example, to format the interest
rate, right-click on the cell and click format cells. Pick percentage and use 2 decimal places.
Similarly, format the other cells for currency instead of percentage and pick number for the
loan term.

Now we have:

Add the SUM() function to total monthly expenses.

Note, the mortgage cell is not included in the total. Excel does not know that you want to include
that number, since there is no value there. So be careful to extend the SUM() function to the top
either by using the cursor or typing E2 where it says E3 to include the mortgage in the sum.
Put the cursor in the payment cell (B4).

On the Formulas menu select the drop-down Financial and then select the PMT function. The
wizard pops up:

Use the cursor to select the rate.,nper (loan term), Pv (present value or loan amount).
Notice that you have to divide the interest rate by 12 since interest is calculated monthly. Also
you need to multiply the loan term in years by 12 to get the loan term in months. Press OK to
save the result in the spreadsheet.
Notice that the payment is shown as a negative number: -1013.37062. To make it positive and
add it to the monthly expenses, point to the mortgage cell (E2). Type =- then use the cursor to
point to the payment field. The resulting formula is =-B4.

Now the spreadsheet looks like this:

Your monthly expenses are $1,863 Ouch!

Example: Text Function
Here we demonstrate how to use functions inside a formula and text functions.
Suppose you have a list of students as shown below. The first and last name is in one field
separated by a comma. We need to put the last and firm names into separate cells. How do we
do this?

To tackle this problem you need to use an algorithm i.e., a step-by-step procedure for doing
this.
For example, look at Washington, George. The procedure to split that into two words would be:
1.

Calculate the length of the string.

2.

Find the position of the comma (this shows where one word ends and the other begins).

3.

Copy the left-hand side of the string up until the comma.

4.

Copy the right-hand side of the string from the comma to the end.

Lets discuss how to do this with George Washington step-by-step in Excel.

1.

Calculate the length of the string with the function =LEN(A3) the result is 18.

2.

Now find the position of the comma by entering this function =FIND(,,A3) the result
is 11.

3.

Now take the left-hand side of the string up until the comma and create this nested
formula using the result from Step 1: =LEFT(A3,FIND(,,A3)-1). Note, we have to subtract
1 from the length because FIND gives the position of the comma.

Here is what that all looks like when all the functions are placed together in a formula. In cell B3,
you can see this formula takes all the information from cell A3 and inputs Washington into it.

So we have Washington, now we need to get George. How do we do this?

Note, that we could have saved the result from Step 1 in a cell by itself, say, B6, then write a
simpler formula =LEFT(A3,B6-1). But that uses up one cell for the intermittent step.
1.

Remember the position of the comma or calculate it again.

2.

Calculate the length of the string.

3.

Count the characters from the end of the string to the comma.

Take the number of characters from Step 3 and subtract one to omit the comma and space.
Lets do this step-by-step.
1.

From above, this is =FIND(,,A3)

2.

The length of the string is =LEN(A3)

3.

You will need to use some math to find the number of characters to take: =LEN(A3)
FIND(,,A3) 1

4.

The right-hand side of the string we want is =RIGHT(A3,LEN(A3) FIND(,,A3) 1)

Your spreadsheet should now look similar to the screenshot below. We copied the formulas as
text into the bottom of the spreadsheet to make it easier to read and see.

That one was a little difficult but you only need to write these formulas once.

Coming up Next
This concludes our lesson for today. You should have a fairly firm understanding now of
formulas and functions, rows and columns, and the way this all can be employed through
several definite examples.
Coming up next in Lesson 3, we will discuss cell reference and formatting as well as moving
and copying formulas so you dont have to rewrite each formula over and over again!

Excel Formulas: 10 Formulas That

Helped Me Keep My Job
APRIL 19, 2013 BY STEVE QUATRANI

Dont waste any more hours in Microsoft Excel doing things manually. There
are many ways to use Excel formulas to decrease the amount of time you
spend in Excel and increase the accuracy of your data and your reports.
Excel Formulas You Should Definitely Know:

1. SUM

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

The SUM formula does exactly what you would expect. It allows you to add 2
or more numbers together. You can use cell references as well in this formula.
The above shows you different examples. You can have numbers in there
separated by commas and it will add them together for you, you can have cell
references and as long as there are numbers in those cells it will add them
together for you, or you can have a range of cells with a colon in between the 2
cells, and it will add the numbers in all the cells in the range.
2. COUNT

Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that have numbers in
them.

This formula only works with numbers though:

It only counts the cells where there are numbers.

**Learn more about the COUNT function in this on-demand, online
course. FREE preview**
3. COUNTA

Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have
numbers and/or any other characters in them.
The COUNTA Formula works with all data types.

It counts the number of non-empty cells no matter the data type.

4. LEN

Formula: =LEN(A1)
The LEN formula counts the number of characters in a cell. Be careful though!
This includes spaces.

Notice the difference in the formula results: 10 characters without spaces in

between the words, 12 with spaces between the words.
5. TRIM

Formula: =TRIM(A1)
Gets rid of any space in a cell, except for single spaces between words. Ive
found this formula to be extremely useful because Ive often run into
situations where you pull data from a database and for some reason extra
spaces are put in behind or in front of legitimate data. This can wreak havoc if
you are trying to compare using IF statements or VLOOKUPs.

I added in an extra space behind I Love Excel. The TRIM formula removes
that extra space. Check out the character count difference with and without
the TRIM formula.

6. RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of

characters), =MID(text, start number, number of characters).
(Note: In all of these formulas, wherever it says text you can use a cell
reference as well)
These formulas return the specified number of characters from a text
string.RIGHT gives you the number of characters from the right of the text
string, LEFT gives you the number of characters from the left, and MID gives
you the specified number of characters from the middle of the word. You tell
the MID formula where to start with the start_number and then it grabs the
specified number of characters to the right of the start_number.
I used the LEFT formula to get the first word. I had it look in cell A1 and grab
only the 1st character from the left. This gave us the word I from I love
Excel
I used the MID formula to get the middle word. I had it look in cell A1, start at
character 3, and grab 5 characters after that. This gives us just the word love
from I love Excel
I used the RIGHT formula to get the last word. I had it look at cell A1 and grab
the first 6 characters from the right. This gives us Excel from I love Excel
7. VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num,

range_lookup)
By far my most used formula. The official description of what it does: Looks
for a value in the leftmost column of a table, and then returns a value in the
same row from a column you specify. (See the full explanation of
VLOOKUP) Basically, you define a value (the lookup_value) for the formula
to look for. It looks for this value in the leftmost column of a table (the
table_array).
Note: If at all possible use a number for the lookup_value. This makes it a lot
easier to make sure the data you are getting back is a correct match.
If it finds a match of the lookup_value in the left column of the table_array
it will return the value in the column you specify using the index_num. The
index_num is relative to the left most column. So, if you have the
table_index look in column A and you want what is returned to be whats in
column B the index_num would be 2 because the leftmost column, column
A in this case, is the 1st column in the table array and column B is the 2nd

column (hence the 2 for the index number). If you want what is in column C to
be returned youd put 3 for the index_num. The range_lookup is a TRUE or
FALSE value. If you put TRUE it will give you the closest match. If you put
FALSE it will only give you an exact match. I only use FALSE when using the
VLOOKUP formula.
Example:
You have 2 lists: 1 with a sales persons ID and the sales revenue for the
quarter. Another with the sales persons ID and the sales persons name. You
want to match up the sales persons name to the sales persons revenue
numbers for the quarter. They are all jumbled around so to manually match
this, even for a small number of salesmen would leave room for a high margin
of error and take a lot of time.
The first list goes from A1 to B13. The 2nd list goes from D1 to E25.
In cell C1 I would put the formula =VLOOKUP(B18, $A$1:$B$13, 2, FALSE)
B18 = the lookup_value (the sales persons ID. This is a number that appears
on both lists.)
$A$1:$B$13 = the table_array. This is the area I want the formula to search
the leftmost column (column E in this case) for the lookup_value. I went to
F because if it finds match in column E, I want it to return whats in column F.
(The money signs are there so that the table_array will stay the same no
matter where the formula is moved or copied to. This is called an absolute
reference.)
2 = the index_num. This tells the formula the number of columns away from
theleft most column to return in case of match. So, if you find a match
between the lookup_value and the leftmost column of the table array, return
whats in the same row in the 2nd column of the table (the 1st column is
always the leftmost column. It starts at 1, not 0).
FALSE= tells the formula I want it to only return the value if its an exact
match.
I would then copy and paste that formula along all the cells in column C next
to the first list. This would give me a perfectly aligned list with the sales
persons ID, sales persons revenue for the quarter, and the sales persons
name.

In order to get a nice neat list of Sales Person ID, Sales Person Name, and
Sales Person Revenue all next to each other I used the VLOOKUP formula to
compare 1 list to another.
This is a complicated formula, but an extremely useful one. Check out some
other examples: Vlookup Example, Microsofts Official Example.
**Learn more about the VLOOKUP function in this on-demand,
online course. FREE preview**
8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is

true, return this if logical statement is false)
When youre doing an analysis of a lot of data in Excel there are a lot of
scenarios you could be trying to discover and the data has to react differently
based on a different situation.
Continuing with the sales example: Lets say a salesperson has a quota to
meet. You used VLOOKUP to put the revenue next to the name. Now you can
use an IF statement that says: IF the salesperson met their quota, say Met
quota, if not say Did not meet quota (Tip: saying it in a statement like this
can make it a lot easier to create the formula, especially when you get to more
complicated things like Nested IF Statements in Excel).
It would look like this:
In the example with the VLOOKUP we had the revenue in column B and the
persons name in column C (brought in with the VLOOKUP). We could put
their quota in column D and then wed put the following formula in cell E1:
=IF(C3>D3, Met Quota, Did Not Meet Quota)
This IF statement will tell us if the first salesperson met their quota or not. We
would then copy and paste this formula along all the entries in the list. It
would change for each sales person.

Having the result right there from the IF statement is a lot easier than
manually figuring this out.

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria),

=AVERAGEIF(range, criteria, average_range)
These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF
the criteria are met. There are also the formulas: SUMIFS, COUNTIFS,
AVERAGEIFS where they will do their respective functions based on multiple
criteria you give the formula.

I use these formulas in our example to see the average revenue (AVERAGEIF)
if a person met their quota, Total revenue (SUMIF) for the just the sales
people who met their quota, and the count of sales people who met their quota
(COUNTIF)
10. CONCATENATE

A fancy word for combining data in 2 (or more) different cells into one cell.
This can be done with the Concatenate excel formula or it can be done by
simply putting the & symbol in between the two cells. If I have Steve in cell
A1 and Quatrani in cell B1 I could put this formula: =A1& &B1 and it would
give me Steve Quatrani. (The puts a space in between what you are
combining with the &). I can use =concatenate(A1, , B1) and it will give me
the same thing: Steve Quatrani
Finding The Right Excel Formulas For The Job
There are 316 built in functions in Excel. Youre not going to sit there and
memorize what all of them do (or at least I hope not!). Luckily Excel has a
built in wizard that helps you find the correct formula for what youre looking
to do (if there is one).
Click the fx next to the formula bar in Excel
This brings up a menu and in there you can type in a description of what you
are trying to do and it will bring up the correct excel formula:

I typed in remove extra spaces and it returned the TRIM formula that we
went over earlier.

More Excel Formulas

There is so much more that I use on a regular basis such as Time formulas
(NOW, TODAY, MONTH, YEAR, DAY, etc.), other formulas like AND and OR,
along with many others.
The real power comes in combining these functions into complicated excel
formulas.
Learn Excel Formulas Today!
Breaking Down Complicated Excel Formulas

=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE), , ),)), )
Do you see formulas like the one above and run away screaming
AHHHHHHHHHHHHHHHHHHHHHHHHH!!!

Well I dont blame ya!

This can be a really intimidating formula even for the most seasoned Excel
user.
I have a solution for you that makes it easy to both break down, and build up
complicated combinations of Excel formulas but first check out this short
video by fellow Udemy trainer, Mynda Treacy, that will reveal tips on how to
get inside the mind of an Excel formula:
So Whats Going on With This Excel Formula?

Lets break down all the Excel functions in it:

IFERROR
TRIM
IF
LEN
VLOOKUP
SUBSTITUTE
Im not going to go in depth into each formula, but youll get the point.

Lets start with the innermost formula. This is the end result that you are
trying to accomplish.
We want to take a phrase (that we get with a VLOOKUP) that has a space in it
and SUBSTITUTE that space with nothing. So it will take a phrase like Excel
Formula and make it ExcelFormula.
SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), , )
We use the VLOOKUP formula to get the actual phrase we want from a
different sheet in the Excel Workbook if it matches whats in cell F7.
VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)
We use an IF statement to say IF the number of characters (LEN) of whats
returned from the VLOOKUP is above 0, then run the VLOOKUP,
otherwise, putnothing here. This way if the VLOOKUP returns nothing, then
nothing will happen and this will prevent most errors.
IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE), , ),)
We then wrap it with the TRIM function which gets rid of all extra spaces,
besides 1 space in between words. Sometimes when youre getting data from a
database extra spaces can be added after a word. This can make it really hard
to compare data from different sources, so the TRIM function comes in handy
a lot!
TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE), , ),)
Finally we wrap it in the IFERROR formula. This formula will be triggered if
the other formulas wind up giving you an error. It could look something like
#N/A and thats pretty ugly. This can happen for legitimate reasons, but you
dont want to hand your boss a sheet full of #N/A symbols.
What happens is IF the combined formulas return any excel error, it will make
the cell blank, otherwise it will show the results of the formula.
=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,
FALSE), , ),)), )
There you have it, this long complicated formula broken down into simple step
by step functions.

You can use the same method for building up a long formula
Our goal is to SUBSTITUTE the spaces for nothing so we start with that. We
then have to pull it in from another sheet so we use VLOOKUP for that, etc.,
etc.
You always start with the innermost formula, the end result that you are
looking for. Then you use other formulas to help you deal with the different
situations youll have to deal with working with large datasets.
Resources to Become an Excel Expert:

This article is the tip of the iceberg. Check out these other resources for more
tips.
Resources For Showing Data:

Excel Formulas will help you deal with data, how you present that data is a
whole other animal. Check out this awesome Excel Dashboard course if
youre interested in using Excel for reporting.
Resources For Excel Help:

Mr. Excel
No matter how good you get at Excel there is too much for one person to be
great at everything Excel can do. The Mr. Excel forums are the place to go
for any questions. They have a very active community around that site and you
can probably get your excel questions answered very quickly.
OzGrid
There are a ton of examples for how to do just about anything in Excel or VBA
onOzgrid.
Learning Excel Macros and VBA

Formulas are awesome by themselves. Using VBA to create Excel Macros in

combination with Excel Formulas is how you become a true Excel expert.
I completely automated a business analyst job I had at a fortune
500 company using these methods.
How I Became the Excel Expert

It was my 3rd year in college and I went to intern for an options trading firm.
There were some brilliant people at this place and even the janitors knew
Excel better than I did. The intern in there before me was a software engineer
who had basically written a ton of macros that automated almost every daily

task I had to do. Great! I thought, Id have the easiest time doing the daily
boring tasks and I could spend time learning how to be a trader.
Well, the week after he left, EVERYTHING BROKE. None of the macros were
working and they all depended on each other. I was never trained on this I
thought, because the software engineer had only told me what buttons to push
and when, never how to do it manually. I started giving out wrong
information, needing to ask busy people to take time to help me, and was just
generally helpless. I did the best I could, but I didnt even know most excel
formulas, much less how to read and write VBA code and create Excel
Macros.
Necessity, the Mother of Invention

Then one day I was taken aside and my manager said, I know the kid before
you did all this stuff, and you werent brought here to write code, but if you
cant fix it and start giving us the correct information on time, were going to
have to go in a different direction. WHAT?! I thought. I hadnt been prepared
or trained for this, and code that another person wrote is not working, and Im
going to get fired for it? I knew he meant it. I had seen interns get fired from
here, it wasnt pretty. It was an open trading floor, you didnt get pulled aside
into someones office, you were unceremoniously escorted off the floor and
everyone knew what happened.
So, I had a goal to meet, and I had to meet it quickly. I set off to learn VBA,
Excel Formulas, and how to get all these Excel macros working. After about 2
weeks of waking up at 5:30am, struggling through the day with the help of
others, then teaching myself Excel until about 2am, I had learned enough to
read the code that was written. After about a month I became proficient at it.
After about 6 months I knew enough to write any Excel formula I wanted, read
& write VBA code, and solve any Excel problem I had without doing manual
work in Excel.
Learning it was a pain in the butt! There was no single resource to learn what I
needed to learn. I had to go all over the web to find things or ask questions on
Mr. Excel. Thats why I created a course on learning Excel. I put all my
knowledge gained over the last 5 years, all my tips & tricks into this Excel
VBA tutorial.
About the Author:

Steve Quatrani is an excel expert with years of experience using and teaching
Excel. He has created advanced systems using VBA code, advanced
dashboards using Excel and other business intelligence software, and he
continues to build new ways of viewing and analyzing data in Excel and
automating those processes. He teaches Microsoft Excel VBA Video
Tutorial on Udemy.
From Steve:
I was astounded by how little some people knew about Excel and they use it
every day! I decided to start teaching people how to use Excel and utilize the
true power of this program to make their lives easier. Throughout my video
courses I teach you everything you need to know to become the Excel expert,
to spend less time doing stuff manually Excel, and to make your life a whole
lot easier all using Excel.
Twitter: @excelintern Linkedin: Steve Quatrani Google+: Steve
Quatrani