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 […]
The INDIRECT function in Excel returns the reference specified by a text string. 1. For example, the INDIRECT function below reduces to =INDIRECT(“C2”), =C2, 5 Do we really need the INDIRECT function for this? Yes. Below you can find the result without using the INDIRECT function. 2. For example, the function below reduces to =SUM(INDIRECT(“E3:E6”)), […]
This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to =MATCH(12,A:A,0), […]
This example teaches you how to lookup a value in a two-dimensional range. We use the MATCH and INDEX function. Below you can find the sales of different ice cream flavors in each month. 1. To find the position of Feb in the range A2:A13, use the MATCH function. The result is 2. 2. To […]
A drawback of the VLOOKUP function is that it can only look up values in the leftmost column of a table. However, sometimes you need to look up a value in any column and return the corresponding value to the left. To achieve this, simply use the INDEX and the MATCH function. 1. The MATCH […]
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 […]
This example illustrates four different ways to concatenate (join) strings in Excel. 1. Simply use the & operator to join strings. Note: to insert a space, use ” “ 2. The CONCATENATE function produces the exact same result. 3a. The CONCAT function in Excel 2016 produces the exact same result. 3b. The CONCAT function can […]
This example shows the difference between the SUBSTITUTE function and the REPLACE function. 1a. If you know the text to be replaced, use the SUBSTITUTE function. 1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute. 2. If you know the position of […]
The FIND function and the SEARCH function are very similar to each other. This example shows the difference. 1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive. 2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive. Note: […]
This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive). .
The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string. 1. For example, cell A1 below contains the string ” Excel Easy “ Note: the TRIM function returns the string “Excel Easy”. 2. For example, cell A1 below contains a […]
This example teaches you how to convert a text string to lower, upper or proper case in Excel. 1. Use the LOWER function to convert all letters in a text string to lowercase. 2. Use the UPPER function to convert all letters in a text string to uppercase. 3. Use the PROPER function to convert […]
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names. 1. Select the range with full names. 2. On the Data tab, in the Data Tools group, click […]
This example describes how to count the number of words in a cell. 1a. The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function. 2a. The SUBSTITUTE function replaces existing text with […]
This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN functon to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of the string […]
This example teaches you how to separate strings in Excel. The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9. 1. To […]
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. Note: to insert a space, use ” “ Left To extract the leftmost characters from a string, use the […]
Year, Month, Day | Date Function | Current Date & Time | Hour, Minute, Second | Time Function | DateDif | weekdays | Time Sheet | Days until Birthday | Last Day of the Month | Holidays | Day of the Year | Quarter To enter a date in Excel, use the “/” or “-” characters. To enter a time, use the […]
Relative Reference | Absolute Reference | Mixed Reference | Copy Exact Formula | 3D-reference | External References | Hyperlinks | 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 […]
If Function | And Function | Or Function | Nested If | Ifs | Switch | Roll the Dice 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. […]
