Learn Excel Functions – Complete Tutorial

Learn Excel functions and take your Excel skills to the next level! 🚀 Related examples and functions are shown on the right side of every chapter. See the detailed overview below.

1. Count and Sum

– Countif: The COUNTIF function helps you count cells that follow a certain rule in Excel. This section provides easy examples to help you learn how to use the COUNTIF function.

– Count Blank/Nonblank Cells: Learn how to count empty and non-empty cells in Excel with this example.

– Count Characters: The LEN function helps you determine the length of a text string by counting its characters. You can count certain characters in Excel by combining the LEN and SUBSTITUTE functions.

– Not Equal To Operator: This operator in Excel compares two values and returns TRUE if they are not equal. Let’s see some examples inside this tutorial.

– Count Cells with Text: In this section, you will learn how to find and count text entries in Excel cells. You can get more details on the COUNTIF function page about how to count cells with particular text.

– Sum: The SUM function in Excel helps you add numbers from multiple cells, whether they are together or spread out, including full columns. The AUTOSUM function helps you add the whole row or column in Excel.

– Running Total: Learn how to add up values step-by-step to get a running total in Excel. Excel adjusts the running total on its own each time you input new data into the list.

– Sumif: The SUMIF function adds only the cells that match a condition. Find lots of easy-to-understand SUMIF examples on this page.

– Sumproduct: The SUMPRODUCT function in Excel multiplies matching values from ranges and sums them up.

2. Logical

– If: IF function tests a condition and returns one value if true and another if false. This page includes many clear and easy IF examples.

– Comparison Operators: The Comparison operators are used in Excel to compare values and return a status such as true or false. It is used to determine the relationship between values like greater, smaller or equal to, etc.

– Or: The result of OR will be false if all of the conditions is false, otherwise the output will be true.

– Roll the Dice: Learn to create a simulation of two dice rolls in Excel. Need it fast? Feel free to download the file.

– Ifs: For checking multiple conditions, use the IFS function in Excel 2016 or above. The IFS function searches for the first TRUE condition and gives back its value.

– Contains Specific Text: Use Excel’s ISNUMBER and SEARCH functions to identify specific text inside a cell. There’s no CONTAINS function in Excel.

– Switch: Using the SWITCH function in Excel, you can find the first matching value from a list and return its related result.

– If Cell is Blank: To find out whether a cell is blank in Excel, use the IF formula with double quotes to represent an empty value. You can use IF and ISBLANK together to get the same result.

– Absolute Value: Use ABS in Excel to convert any number to its absolute (non-negative) value. In other words, ABS makes sure the result is always positive, even if the input is negative.

– And:. The AND function returns TRUE only when every condition is true. If any condition is false, it returns FALSE.

3. Cell References

– Copy a Formula: To copy press CTRL + C together. To paste in Excel, select the target cell then press the CTRL and the V key together. The other way to do it, click the small box in the corner of the cell and drag it to the targeted cells.

– 3D reference: A 3D reference helps you use the same cell or range from different worksheets in your formulas. First, we’ll look at the alternative.

– Name Box: It helps you to easily locate and go to a cell, multiple cells, or a named range. The name box helps you easily create a named range, which is an important step.

– External References: An external reference means accessing cells from a separate Excel file.

– Hyperlinks: Use the ‘Insert Hyperlink’ feature in Excel to link to files, web pages, or places within your file. Alternatively, you can use the built-in HYPERLINK function.

– Union and Intersect: Follow this example to learn the union and intersect operators in Excel.

– Percent Change: Excel commonly uses the percent change formula to show how much a value has increased or decreased. For example, to determine the Monthly Change and Total Change.

– Add a Column: To add a new column in Excel, simply right-click the column label and choose Insert. When you insert new rows or columns in Excel, the program automatically adjusts the cell references in your formulas to reflect the changes.

– Absolute Reference: To make sure a cell reference does not change when copied, add a dollar sign ($) before both the column letter and the row number. This locks the reference. If you copy a formula with an absolute reference, it will still refer to the same cell, no matter where you place it.

– Address: When you provide row number and column number with the ADDRESS function, it returns a cell reference as a text, like ‘A1’, where row is 1 and column is ‘A’.

4. Date & Time

– DateDif: To determine how many days, months, or years are between two dates, you can use the DATEDIF function.

– Today’s Date: The TODAY function in Excel returns today’s date into the selected cell. To show the current date and time, automatically use the NOW function.

– Date and Time Formats: Excel supports various formats for displaying dates and times.

– Calculate Age: You can calculate age in Excel by combining the DATEDIF function with the current date using TODAY().Using the age formula, you can see a person’s age exactly in years, months, and days.

– Time Difference: It can be a bit confusing to calculate the difference between two times in Excel. Internally, time is represented using numbers from 0 to 1.

– Weekdays: Learn how to use the WEEKDAY, NETWORKDAYS, and WORKDAY functions in Excel to create useful formulas for working with weekdays. Ready to start learning Excel in a simple and effective way?

– Days until Birthday: Calculate the days until your birthday in Excel by following these simple steps.

– Last Day of the Month: You can use the EOMONTH function in Excel to find the last day of any month.

– Add or Subtract Time: You can use Excel’s TIME function to perform simple addition or subtraction with hours, minutes, and seconds. SUM function also helps you to add times in Excel.

– Quarter: A basic formula to determine the quarter for a specific date. There is no default function in Excel that performs this action.

– Day of the Year: Day of the Year shows which day number it is within the year for any given date. There is no default function in Excel that performs this action.

– Days between Dates: In Excel, the number of days between two dates can be found by simply subtracting one date from another, using the DAYS or DATEDIF functions.

5. Text

– Separate Strings: In Excel, the RIGHT, LEN, FIND, and LEFT functions help you break a string into smaller parts. If you’re new to formulas, Flash Fill can help you separate text strings quickly in Excel.

– Count Words: In Excel, you can count words by using the TRIM, LEN, SUBSTITUTE, and SUMPRODUCT functions. For instance, let’s find out how often a specific word shows up in a cell. This trick is pretty cool.

– Text to Columns: The Convert Text to Columns Wizard splits the contents of one cell into multiple columns based on delimiters or fixed widths.

– Find: To find the exact location of a word or letter within a text, you can use the FIND function. The FIND function is case-sensitive.

– Search: The SEARCH function in Excel helps identify the place where a particular text is found inside another text. The SEARCH function searches text without considering letter case and supports wildcards.

– Change Case: In Excel, LOWER makes text lowercase, UPPER makes it uppercase, and PROPER capitalizes each word. At the beginning, if you are not comfortable with Excel formulas then you can use the Flash Fill function to change text case in Excel.

– Remove Spaces: The TRIM function is used in Excel to clear spaces before, after, and between words, making the text clean. You can use the SUBSTITUTE function to get rid of all spaces, including non-breaking spaces, in your text.

– Compare Text: This example helps you learn two ways to compare text in Excel. One method checks if letters are uppercase or lowercase (case-sensitive), while the other method does not consider letter case.

– Substitute vs Replace: The SUBSTITUTE function in Excel helps you replace specific words or characters. The REPLACE function helps you update text at a specific location.

– Text: In Excel, LOWER converts text to lowercase, UPPER changes text to uppercase, and PROPER formats text with capitalized first letters in each word.

– Concatenate: To join two or more text strings in Excel, you can use functions like CONCATENATE, CONCAT, TEXTJOIN, or the & operator.

– Substring: There’s no SUBSTRING function in Excel. Excel offers functions such as MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM, and MAX to help extract and manage text within cells.

6. Lookup & Reference

– Vlookup: In Excel, the VLOOKUP function is very popular and helpful for searching specific information. Here, you can explore multiple VLOOKUP examples explained in a simple way.

– Tax Rates: This example explains how to apply the VLOOKUP function in Excel to find the tax amount based on income.

– Index and Match: Instead of using VLOOKUP, consider using INDEX and MATCH for more flexibility. The INDEX and MATCH formula is a better option to perform advanced lookups in Excel.

– Two-way Lookup: For this use INDEX and MATCH. To retrieve data from a range that has both rows and columns, INDEX and MATCH are a great option.

– Offset: The OFFSET function in Excel is used to find a cell or range by moving a certain number of rows and columns from a starting cell.

– Case-sensitive Lookup: VLOOKUP looks up values without checking if the letters are uppercase or lowercase. If you need to match text exactly, including uppercase and lowercase letters, you can use the INDEX, MATCH, and EXACT functions in Excel.

– Left Lookup: The VLOOKUP function can only search in columns to the right of your search column. It doesn’t work if the data you want is in a column to the left. In these cases, you should use the INDEX and MATCH functions together, which can find data in any column and return data from any other column.

– Locate Maximum Value: In Excel, the MAX function is used to find the highest value in a list of numbers. To get the cell address of the highest value in a column, use the MAX, MATCH, and ADDRESS functions.

– Indirect: The INDIRECT function in Excel changes a text string into a cell reference, making it dynamic. By using the & operator, you can join several text strings to create a reference. This feature allows you to create flexible formulas that adjust as your data or references change.

– Two-column Lookup: Learn to perform a lookup depending on values from two different columns.

– Closest Match: To get the nearest value to your target in a column, apply the INDEX, MATCH, ABS, and MIN functions together in Excel. VLOOKUP in Excel finds a value in a table and returns a matching or closest related result from that list.

– Compare Two Columns: In Excel, you can compare two columns by combining the IF, ISERROR, and MATCH functions. It is possible to display duplicates as well as unique values.

– Xlookup: If you are using Excel 365 or Excel 2021, it is better to use XLOOKUP instead of VLOOKUP. The XLOOKUP function is more user-friendly and includes useful improvements.

– Xmatch: The XMATCH function in Excel is designed to replace the older MATCH function. This function is more simple to use and offers additional features. It helps you find the position of values in a list or range more easily.

7. Financial

– PMT: The PMT function calculates the loan payment amount with fixed payments and a constant interest rate. This page provides simple and beginner-friendly PMT examples.

– Loans with Different Durations: This example guides you through comparing loans with various durations using Excel.

– Investment or Annuity: In this example, you will learn how to determine the future value of an investment or the present value of an annuity.

– Compound Interest: What does compound interest mean, and what is the Excel formula used to find it? This example is designed to help you understand the answers to these questions.

– CAGR: Excel does not have a specific function for CAGR. Use the RRI function to calculate it by providing the total number of years, the beginning value, and the ending value.

– Loan Amortization Schedule: In this tutorial, you will learn how to make a loan amortization schedule in Excel.

– NPV: To compute NPV in Excel, simply enter the future cash flows into the NPV formula. After getting the result, subtract the initial investment to find the Net Present Value.

– IRR: In Excel, enter all the project cash flows in order, and use the IRR function to calculate the return rate. The internal rate of return is the interest rate that makes the net present value of all project cash flows equal to zero.

– Depreciation: Excel offers five different depreciation functions. We are evaluating an asset that costs $10,000 initially, will be used for 10 years, and has an estimated value of $1,000 at the end.

– Profit Margin: Excel is a useful tool for calculating different types of profit margins. It helps you easily find gross profit margin, operating profit margin, and net profit margin by using simple formulas.

8. Statistical

– Average: The AVERAGE function in Excel finds the arithmetic mean by adding numbers, then dividing by their count.

– Negative Numbers to Zero: An easy way in Excel to convert all negative numbers to zero without affecting positive numbers.

– Random Numbers: You can easily generate random numbers in Excel using two built-in functions. RAND and RANDBETWEEN.

– Rank: You can use the RANK function in Excel to identify the highest, second-highest, and other positions of numbers in a list. The RANK.AVG function in Excel calculates the average rank for values that have the same number in a list.

– Percentiles and Quartiles: In this section, you will learn the use of PERCENTILE and QUARTILE functions in Excel in a simple way.

– Box and Whisker Plot: Understand the use of a box and whisker plot, display the smallest value, first quartile, median, third quartile, and largest value in a data set.

– AverageIf: Excel’s AVERAGEIF function computes the average of cells that fulfill a certain condition in Excel. AVERAGEIFS computes the average for cells that fulfill more than one requirement.

– Forecast: Using the FORECAST.LINEAR function in Excel, you can predict future values based on a straight-line trend. Excel’s FORECAST.ETS function forecasts future values by using a method called Exponential Triple Smoothing that includes seasonal effects.

– MaxIfs and MinIfs: In Excel 2016 or later, MAXIFS and MINIFS allow you to identify the maximum and minimum values according to one or more specified conditions.

– Weighted Average: Learn to calculate a weighted average in Excel using SUMPRODUCT and SUM functions.

– Mode: To find which number shows up the most in your list you can use the MODE function. Use MODE.MULT to find multiple modes.

– Standard Deviation: This page teaches you how to calculate standard deviation in Excel. These functions help measure data spread, making it easier to understand how values vary within a dataset.

– Frequency: The FREQUENCY function helps you find how frequently values fall into ranges you define with a bin table in Excel.

9. Round

– Chop off Decimals: This example explains how to use two Excel functions to remove decimal digits. INT and TRUNC.

– Nearest Multiple: Learn how to round numbers in Excel using three simple functions: MROUND, CEILING, and FLOOR. This tutorial includes examples to show how each one works.

– Even and Odd: EVEN and ODD round numbers in Excel; ISEVEN and ISODD check if a number is even or odd, returning TRUE or FALSE. The ISEVEN and ISODD functions are useful when you need to check if a number is even or odd.

– Mod: The MOD function in Excel returns the remainder after division, letting you check if a number divides without a remainder, or if it is even or odd.

– Rounding Times: The MROUND function helps you to round any time value to the nearest quarter-hour, half-hour, or full hour.

10. Formula Errors

– IfError: In Excel, you can use the IFERROR function to return a specific result, such as a text message, if your formula results in an error.

– IsError: This example explains how the ISERROR function works in Excel.

– Aggregate: Functions such as SUM, COUNT, LARGE, and MAX do not give results when there are errors in the cell range. However, the AGGREGATE function is an easy way to fix this problem.

– Circular Reference: This kind of error happens when a formula directly or indirectly references its own cell. This causes an error because Excel cannot solve it.

– Formula Auditing: The main purpose of formula auditing is to understand the relationship between formulas and the related cells in Excel. To understand the Formula Auditing easily, we will use an example.

– Sum Range with Errors: Understand how to create an array formula that adds values from a range containing errors. If your range has errors, you can still calculate the total using the AGGREGATE function.

– Floating Point Errors: Excel uses floating-point numbers to store and calculate data. Occasionally, the result of a formula is a close estimate rather than an exact number.

– IFNA: In Excel, you can use the IFNA function to replace the #N/A error with a message you want to show. The IFNA function helps you manage #N/A errors in your formulas.

11. Array Formulas

– Count Errors: The example explains creating a formula in Excel to quickly find and count errors within a group of cells, making error tracking simple and efficient.

– Count Unique Values: You will learn to make a simple formula using an array to count how many unique values are in a list. To count unique values in Excel 365/2021, apply the UNIQUE function first, then use COUNTA to count those unique entries.

– Count with Or Criteria: Using OR criteria to count in Excel may be confusing for beginners. This article includes basic examples to help beginners understand the concepts easily.

– Sum Every Nth Row: In this tutorial, you will learn to create an array formula to calculate the sum of every nth row. In this example, we will illustrate with n = 3, but you may use any number you want.

– Sum Largest Numbers: Use LARGE to find big numbers and SUMIF to add them up in Excel.

– Sum with Or Criteria: In this tutorial, we’ll look at how to sum values in Excel using OR conditions, which can be a bit tricky for beginners. This article provides several easy-to-understand examples.

– Most Frequently Occurring Word: In this example, you will learn how to identify the most common word in Excel.

– Dynamic Arrays: By entering a dynamic array formula in one cell, you can populate several cells at once. In Excel 365 and Excel 2021, “Spilling” means a formula automatically fills multiple cells with results.

– LET function: Use the LET function in Excel 365/2021 to define a variable and set its value, which can be a number, text, or result of a formula. You can include these variables in your calculation.

– Array Manipulation: Excel 365’s array manipulation features let you easily combine, reshape, and resize arrays, making data handling and analysis faster and more flexible.

– Lambda: Lambda lets you create custom Excel functions by defining them with LAMBDA and saving via the Name Manager for reuse.

– TextSplit: Excel 365 provides the TEXTSPLIT function to separate text into rows or columns by specifying a delimiter, such as a comma or space.

Visit our next section: Data Analysis.