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 a quick 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

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 a description of the first 50 pages.

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

2 Count Blank/Nonblank Cells: Use the COUNTBLANK function in Excel to count blank cells and the COUNTA function in Excel to count nonblank cells.

3 Count Characters: Use the LEN function to count characters in Excel. Use LEN and SUBSTITUTE to count specific characters in Excel.

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

5 Count Cells with Text: This page illustrates multiple ways to count cells with text in Excel.

6 Sum: Use the SUM function in Excel to sum a range of cells, an entire column or non-contiguous cells. To create awesome SUM formulas, combine the SUM function with other Excel functions.

7 Running Total: This example 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.

8 Sumif: Use the SUMIF function in Excel to sum cells based on numbers, text strings or dates that meet specific criteria.

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

10 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, if one value is less than another value, etc.

11 Or: The OR function in Excel returns TRUE if any of the conditions are true and returns FALSE if all conditions are false.

12 Roll the Dice: This example teaches you how to simulate the roll of two dice in Excel. If you are in a hurry, simply download the Excel file.

13 Ifs: Use the IFS function in Excel 2016 or later when you have multiple conditions to meet. The IFS function returns a value corresponding to the first TRUE condition.

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

15 Switch: This example teaches you how to use the SWITCH function in Excel 2016 or later instead of the IFS function.

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

17 Absolute Value: The ABS function in Excel returns the absolute value of a number. In other words: the ABS function removes the minus sign (-) from a negative number, making it positive.

18 Copy a Formula: When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to.

19 3D-reference: A 3D-reference in Excel refers to the same cell or range on multiple worksheets. First, we'll look at the alternative.

20 External References: An external reference in Excel is a reference to a cell or range of cells in another workbook.

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

22 Union and Intersect: This example illustrates how to use the union and intersect operators in Excel.

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

24 Add a Column: To add a column in Excel, right-click the column letter and click Insert. When you insert a column or row, cell references update automatically.

25 Absolute Reference: An absolute reference in Excel refers to a fixed location on a worksheet. When you copy a formula, an absolute reference never changes.

26 Address: The ADDRESS function in Excel creates a cell reference as text, based on a given row and column number.

27 DateDif: To get the number of days, weeks or years between two dates in Excel, use the DATEDIF function. The DATEDIF function has three arguments.

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

29 Date and Time Formats: Dates and times in Excel can be displayed in a variety of ways. To apply a Date or Time format, execute the following steps.

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

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

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

33 Days until Birthday: To calculate the number of days until your birthday in Excel, execute the following steps.

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

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

36 Quarter: An easy formula that returns the quarter for a given date. There's no built-in function in Excel that can do this.

37 Day of the Year: An easy formula that returns the day of the year for a given date. There's no built-in function in Excel that can do this.

38 Separate Strings: This example teaches you how to separate strings in Excel.

39 Count Words: Use TRIM, LEN, SUBSTITUTE and SUMPRODUCT in Excel to count words. First, let's count the total number of words in a cell or range of cells.

40 Text to Columns: To separate the contents of one Excel cell into separate columns, you can use the 'Convert Text to Columns Wizard'.

41 Find: Use the FIND function in Excel to find the position of a substring in a string. The FIND function is case-sensitive.

42 Search: Use the SEARCH function in Excel to find the position of a substring in a string. The SEARCH function is case-insensitive and supports wildcards.

43 Change Case: To change the case of text in Excel, use LOWER, UPPER or PROPER. If you're not a formula hero, use Flash Fill to change case in Excel.

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

45 Compare Text: This example shows two ways to compare text strings in Excel. One is case-sensitive and one is case-insensitive.

46 Substitute vs Replace: If you know the text to be replaced, use the SUBSTITUTE function in Excel. If you know the position of the text to be replaced, use the REPLACE function.

47 Text: When joining text and a number, use the TEXT function in Excel to format that number. This page contains many easy to follow TEXT function examples.

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

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

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