Functions

Discover how functions in Excel help you save time. If you are new to functions in Excel, we recommend you to read our introduction to Formulas and Functions first.

 

1 Count and Sum Functions

Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

 

Count  |  Countif  |  Countifs  |  Sum  |  Sumif  |  Sumifs

The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

Count

To count the number of cells that contain numbers, use the COUNT function.

Count Function

 

Countif

To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

Countif Function

Countifs

To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

Countifs Function

Sum

To sum a range of cells, use the SUM function.

Sum Function

Sumif

To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).

Sumif Function, Two Arguments

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).

Sumif Function, Three Arguments

Sumifs

To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

Sumifs Function

General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.

2 Logical Functions

 Logical: Learn how to use Excel's logical functions such as the IF, AND and OR function.

 

If Function  |  And Function  |  Or Function

Learn how to use Excel's logical functions such as the IF, AND and OR function.

If Function

The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

1. Select cell C2 and enter the following function.

If Function

 

The IF function returns Correct because the value in cell A1 is higher than 10.

And Function

The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

1. Select cell D2 and enter the following formula.

And Function

The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.

Or Function

The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

1. Select cell E2 and enter the following formula.

Or Function

The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.

General note: the AND and OR function can check up to 255 conditions.

3 Cell References

 Cell References: Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

Relative Reference  |  Absolute Reference  |  Mixed Reference

Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

Relative Reference

By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.

Relative Reference Example

 

1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.

Relative Reference Result

Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.

Absolute Reference

See the formula in cell E3 below.

1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number of cell H3 ($H$3) in the formula of cell E3.

Absolute Reference Example

2. Now we can quickly drag this formula to the other cells.

Absolute Reference Result

The reference to cell H3 is fixed (when we drag the formula down and across). As a result, the correct lengths and widths in inches are calculated.

Mixed Reference

Sometimes we need a combination of relative and absolute reference (mixed reference).

1. See the formula in cell F2 below.

Mixed Reference Example

2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at the formula in cell G2.

Mixed Reference Example

Do you see what happens? The reference to the price should be a fixed reference to column B. Solution: place a $ symbol in front of the column letter of cell B2 ($B2) in the formula of cell F2. In a similar way, when we drag cell F2 down, the reference to the reduction should be a fixed reference to row 6. Solution: place a $ symbol in front of the row number of cell B6 (B$6) in the formula of cell F2.

Result:

Mixed Reference Example

Note: we don't place a $ symbol in front of the row number of B2 (this way we allow the reference to change from B2 (Jeans) to B3 (Shirts) when we drag the formula down). In a similar way, we don't place a $ symbol in front of the column letter of B6 (this way we allow the reference to change from B6 (Jan) to C6 (Feb) and D6 (Mar) when we drag the formula across).

3. Now we can quickly drag this formula to the other cells.

Mixed Reference Result

The references to column B and row 6 are fixed.

4 Date & Time Functions

 Date & Time: To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

Year, Month, Day  |  Date Function  |  Current Date & Time  |  Hour, Min, Sec  |  Time Function

To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

Date and Time in Excel

Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings. Learn more about Date and Time formats.

 

Year, Month, Day

To get the year of a date, use the YEAR function.

Year Function

Note: use the MONTH and DAY function to get the month and day of a date.

Date Function

1. To add a number of days to a date, use the following simple formula.

Add Days

2. To add a number of years, months and/or days, use the DATE function.

Add Years, Months and Days

Note: the DATE function accepts three arguments: year, month and day. Excel knows that 6 + 2 = 8 = August has 31 days and rolls over to the next month (23 August + 9 days = 1 September).

Current Date & Time

To get the current date and time, use the NOW function.

Now Function

Note: use the TODAY function to get the current date only. Use NOW()-TODAY() to get the current time only (and apply a Time format).

Hour, Min, Sec

To return the hour, use the HOUR function.

Hour Function

Note: use the MINUTE and SECOND function to return the minute and second.

Time Function

To add a number of hours, minutes and/or seconds, use the TIME function.

Add Hours, Minutes and Seconds

Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.

5 Text Functions

 Text: Excel has many functions to offer when it comes to manipulating text strings.

 

Join Strings  |  Left  |  Right  |  Mid  |  Len  |  Find  |  Substitute

Excel has many functions to offer when it comes to manipulating text strings.

Join Strings

To join strings, use the & operator.

Join Strings

 

Note: to insert a space, use " "

Left

To extract the leftmost characters from a string, use the LEFT function.

Left Function

Right

To extract the rightmost characters from a string, use the RIGHT function.

Right Function

Mid

To extract a substring, starting in the middle of a string, use the MID function.

Mid Function

Note: started at position 5 (p) with length 3.

Len

To get the length of a string, use the LEN function.

Len Function

Note: space (position 8) included!

Find

To find the position of a substring in a string, use the FIND function.

Find Function

Note: string "am" found at position 3.

Substitute

To replace existing text with new text in a string, use the SUBSTITUTE function.

Substitute Function

6 Lookup & Reference Functions

 Lookup & Reference: Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

VLookup  |  HLookup  |  Match  |  Index  |  Choose

Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

VLookup

The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

VLookup Function in Excel

 

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Copy Vlookup Function

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.

HLookup

In a similar way, you can use the HLOOKUP (Horizontal lookup) function.

HLookup Function

Match

The MATCH function returns the position of a value in a given range.

Match Function

Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.

Index

The INDEX function returns a specific value in a two-dimensional or one-dimensional range.

Index Function, Two-dimensional Range

Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

Index Function, One-dimensional Range

Note: 97 found at position 3 in the range E4:E7.

Choose

The CHOOSE function returns a value from a list of values, based on a position number.

Choose Function

Note: Boat found at position 3.

7 Financial Functions

Financial: This chapter illustrates Excel's most popular financial functions.

Pmt  |  Rate  |  Nper  |  Pv  |  Fv

To illustrate Excel's most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that's what you hope to achieve when you pay off a loan).

We make monthly payments, so we use 6%/12 = 0.5% for Rate and 20*12 = 240 for Nper (total number of periods). If we make annual payments on the same loan, we use 6% for Rate and 20 for Nper.

Pmt

Select cell A2 and insert the PMT function.

Insert Excel's Most Popular Financial Function

 

Note: The last two arguments are optional. For loans the Fv can be omitted (the future value of a loan equals 0, however, it's included here for clarification). If Type is omitted, it is assumed that payments are due at the end of the period.

Result. The monthly payment equals $1,074.65.

Pmt Function

Tip: when working with financial functions in Excel, always ask yourself the question, am I making a payment (negative) or am I receiving money (positive)? We pay off a loan of $150,000 (positive, we received that amount) and we make monthly payments of $1,074.65 (negative, we pay).

Rate

If Rate is the only unknown variable, we can use the RATE function to calculate the interest rate.

Rate Function

Nper

Or the NPER function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, it takes 240 months to pay off this loan.

Nper Function

We already knew this, but we can change the monthly payment now to see how this affects the total number of periods.

Nper Function

Conclusion: if we make monthly payments of $2,074.65, it takes less than 90 months to pay off this loan.

Pv

Or the PV (Present Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, how much can we borrow? You already know the answer.

Pv Function

Fv

And we finish this chapter with the FV (Future Value) function. If we make monthly payments of $1,074.65 on a 20-year loan, with an annual interest rate of 6%, do we pay off this loan? Yes.

Fv Function

But, if we make monthly payments of only $1,000.00, we still have debt after 20 years.

Fv Function

8 Statistical: An overview of some very useful statistical functions in Excel.

9 Round: This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.

10 Formula Errors: This chapter teaches you how to deal with some common formula errors in Excel.

11 Array Formulas: This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.