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

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

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

4 Date & Time: To enter a date, use the "/" or "-" characters. To enter a time, use the ":" (colon).

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

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

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

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

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

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

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

Functions+

 

Become an Excel pro! You can find related examples and functions on the right side of each chapterat the bottom of each chapter. Below you can find an overview.

1 Count and Sum: Countif | Count Blank/Nonblank Cells | Count Characters | Not Equal To | Count Cells with Text | Sum | Running Total | Sumif | Sumproduct

2 Logical: If | Comparison Operators | Or | Roll the Dice | Ifs | Contains Specific Text | Switch | If Cell is Blank | Absolute Value

3 Cell References: Copy a Formula | 3D-reference | External References | Hyperlinks | Union and Intersect | Percent Change | Add a Column | Absolute Reference | Address

4 Date & Time: DateDif | Today's Date | Date and Time Formats | Calculate Age | Time Difference | Weekdays | Days until Birthday | Last Day of the Month | Add or Subtract Time | Quarter | Day of the Year | Days between Dates

5 Text: Separate Strings | Count Words | Text to Columns | Find | Search | Change Case | Remove Spaces | Compare Text | Substitute vs Replace | Text | Concatenate | Substring

6 Lookup & Reference: Vlookup | Tax Rates | Index and Match | Two-way Lookup | Offset | Case-sensitive Lookup | Left Lookup | Locate Maximum Value | Indirect | Two-column Lookup | Closest Match | Compare Two Columns | Xlookup

7 Financial: PMT | Loans with Different Durations | Investment or Annuity | Compound Interest | CAGR | Loan Amortization Schedule | NPV | IRR | Depreciation | Profit Margin

8 Statistical: Average | Negative Numbers to Zero | Random Numbers | Rank | Percentiles and Quartiles | Box and Whisker Plot | AverageIf | Forecast | MaxIfs and MinIfs | Weighted Average | Mode | Standard Deviation | Frequency

9 Round: Chop off Decimals | Nearest Multiple | Even and Odd | Mod | Rounding Times

10 Formula Errors: IfError | IsError | Aggregate | Circular Reference | Formula Auditing | Floating Point Errors | IFNA

11 Array Formulas: Count Errors | Count Unique Values | Count with Or Criteria | Sum Every Nth Row | Sum Largest Numbers | Sum Range with Errors | Sum with Or Criteria | Most Frequently Occurring Word | Dynamic Arrays | LET function

Functions+

 

Below you can find the most popular pages in this section.

1 Vlookup: The VLOOKUP function is one of the most popular functions in Excel. This page contains many easy to follow VLOOKUP examples.

2 Percent Change: The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change.

3 Loan Amortization Schedule: This example teaches you how to create a loan amortization schedule in Excel.

4 Random Numbers: Excel has two very useful functions when it comes to generating random numbers. RAND and RANDBETWEEN.

5 If: The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples.

6 Standard Deviation: This page explains how to calculate the standard deviation based on the entire population using the STDEV.P function in Excel and how to estimate the standard deviation based on a sample using the STDEV.S function in Excel.

7 Count Unique Values: This example shows you how to create an array formula that counts unique values.

8 Countif: The powerful COUNTIF function in Excel counts cells based on one criteria. This page contains many easy to follow COUNTIF examples.

9 Offset: The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

10 Compare Two Columns: To compare two columns, use IF, ISERROR and MATCH in Excel. You can display the duplicates or the unique values.

11 Compound Interest: What's compound interest and what's the formula for compound interest in Excel? This example gives you the answers to these questions.

12 Sumif: The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples.

13 Concatenate: Use CONCATENATE, CONCAT, TEXTJOIN or the & operator in Excel to concatenate (join) two or more text strings.

14 Weighted Average: To calculate a weighted average in Excel, simply use SUMPRODUCT and SUM.

15 Index and Match: Use INDEX and MATCH in Excel and impress your boss. Instead of using VLOOKUP, use INDEX and MATCH. To perform advanced lookups, you'll need INDEX and MATCH.

16 Sumproduct: To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's powerful SUMPRODUCT function.

17 Contains Specific Text: To check if a cell contains specific text, use ISNUMBER and SEARCH in Excel. There's no CONTAINS function in Excel.

18 Pmt: The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate. This page contains many easy to follow PMT examples.

19 Calculate Age: To calculate the age of a person in Excel, use DATEDIF and TODAY. The DATEDIF function has three arguments.

20 Indirect: Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings.

21 CAGR: There's no CAGR function in Excel. However, simply use the RRI function in Excel to calculate the compound annual growth rate (CAGR) of an investment over a period of years.

22 If Cell is Blank: Use the IF function and an empty string in Excel to check if a cell is blank. Use IF and ISBLANK to produce the exact same result.

23 AverageIf: The AVERAGEIF function in Excel calculates the average of cells that meet one criteria. AVERAGEIFS calculates the average of cells that meet multiple criteria.

24 Substring: There's no SUBSTRING function in Excel. Use MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and MAX in Excel to extract substrings.

25 Sum: Use the SUM function in Excel to sum a range of cells, an entire column or non-contiguous cells.

26 Remove Spaces: The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces.

27 Comparison Operators: Use comparison operators in Excel to check if two values are equal to each other, if one value is greater than another value, etc.

Check out all 300 examples.