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.

1Count 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.

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 a complete 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 | And

3 Cell References: Copy a Formula | 3D-reference | Name Box | 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 | Xmatch

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 | Sum Range with Errors | Floating Point Errors | IFNA

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

Best of Functions+

Join more than 1 million monthly learners. Explore the best of what we offer master new Excel functions and become great at Excel. Happy learning!

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 age in Excel use the DATEDIF function and TODAY. You can also use the age formula to calculate a person’s exact age in years months and days.

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 Average: The AVERAGE function in Excel calculates the average (arithmetic mean) of a group of numbers.

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.

28 NPV: The correct NPV formula in Excel uses the NPV function to calculate the present value of a series of future cash flows and subtracts the initial investment.

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

30 Xlookup: If you have Excel 365 or Excel 2021 use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

31 Hyperlinks: Use the ‘Insert Hyperlink’ dialog box in Excel to create a hyperlink to an existing file a web page or a place in this document. You can also use the HYPERLINK function.

32 Forecast: The FORECAST.LINEAR function in Excel predicts a future value along a linear trend. The FORECAST.ETS function in Excel predicts a future value using Exponential Triple Smoothing which takes into account seasonality.

33 Add or Subtract Time: Use the TIME function in Excel to add or subtract hours minutes and seconds. To add up times in Excel simply use the SUM function.

34 Copy a Formula: Simply use CTRL + c and CTRL + v to copy and paste a formula in Excel. Use the fill handle in Excel to quickly copy a formula to other cells.

35 Time Difference: Calculating the difference between two times in Excel can be tricky. Times are handled internally as numbers between 0 and 1.

36 IRR: Use the IRR function in Excel to calculate a project’s internal rate of return. The internal rate of return is the discount rate that makes the net present value equal to zero.

37 Not Equal To: In Excel <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let’s take a look at a few examples.

38 Today’s date: To enter today’s date in Excel use the TODAY function. To enter the current date and time use the NOW function.

39 Absolute Reference: To create an absolute reference in Excel add $ symbols to a cell or range reference. This locks the reference. When you copy a formula an absolute reference never changes.

40 Running Total: This page teaches you how to create a running total (cumulative sum) in Excel. A running total changes each time new data is added to a list.

41 Rank: The RANK function in Excel returns the rank of a number in a list of numbers. Use RANK.AVG to return the average rank if more than one number has the same rank.

42 Weekdays: Use WEEKDAY NETWORKDAYS and WORKDAY to create cool weekday formulas in Excel. Are you ready to improve your Excel skills?

43 Last Day of the Month: To get the date of the last day of the month in Excel use the EOMONTH (End of Month) function.

44 DateDif: To calculate the number of days months or years between two dates in Excel use the DATEDIF function. The DATEDIF function has three arguments.

45 Frequency: The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table.

46 Two-column Lookup: This example teaches you how to perform a two-column lookup in Excel.

47 Circular Reference: A circular reference in Excel occurs when a formula directly or indirectly refers to its own cell. This is not possible.

48 Named Range: Create a named range or a named constant and use these names in your Excel formulas. This way you can make your formulas easier to understand.

49 Text to Columns: To separate the contents of one Excel cell into separate columns you can use the ‘Convert Text to Columns Wizard’.

50 Dynamic Arrays: Dynamic array formulas entered into a single cell fill multiple cells. This behavior in Excel 365/2021 is called spilling.

Check out all 300 examples.