Advanced Formulas in MS Excel

Lesson Goals

Learn about naming and labeling cells and ranges of cells.
Learn to use names and labels in formulas.
Learn to create formulas that span multiple worksheets.
Learn to use the conditional IF function and its variants in formulas.
Learn to use the PMT function to calculate payments for loans.
Learn to use the LOOKUP function.
Learn to use the VLOOKUP function.
Learn to use the HLOOKUP function.
Learn to use the CONCAT function to join the contents of numerous cells.
Learn to use the TRANSPOSE function.
Learn to use the PROPER, UPPER, and LOWER functions to alter the casing of text.
Learn to use the LEFT, RIGHT, and MID functions to return characters from the start or end of a string, or a specific number of text characters.
Learn to use various date functions.

Using Named Ranges in Formulas

Some of the advantages to naming and labeling cells include:

Using names can make it easier to understand what a formula does (e.g., =Q3Sales*Commission).
Names work throughout a workbook, so using names can simplify the process of creating formulas that span multiple sheets.

Here is a list of things you need to know about cell names:

The first character of a name must be either a letter, a backslash (\), or an underscore (_).
Other (non-first) characters can be letters, numbers, underscores, or periods.
Spaces cannot be used.
The maximum number of characters in a name is 255.
Anything that could be a cell reference (e.g., C10, $B$7) cannot be used as a name.
Names are not case sensitive (e.g., the names "total", "Total", and "TOTAL" are the same in Excel).

Naming a Single Cell

To name a cell:

Select the cell you wish to name.
In the Name Box (to the left of the formula bar), type the name:
Press Enter.

Naming a Range of Cells

To name a range of cells:

Select the cells in the range you wish to name.
In the Name Box (to the left of the formula bar), type the name:
Press Enter.

Naming Multiple Single Cells Quickly

To quickly name cells using their row and column headings:

Select the rows and columns containing the range you wish to name:
On the Formulas tab, in the Defined Names group, click the Create from Selection command:
In the Create Names from Selection dialog box, check the desired boxes and click OK:
In the following image, cells can now be referred to using the row and column headings:

Hint: If step 3 isn't working for you, make sure that you named cell B1 "QuarterlyIncome" with no space and that you did not include a space within "QuarterlyIncome" in the formula for B13.

Using Named Ranges in Formulas

Duration: 10 to 20 minutes.

In this exercise, you will practice naming cells and will use named cells in a formula.

Open Using Names.xlsx from your Excel2019.2/Exercises folder.
Name cell B1 "Quarterly Income".
Use the Create from Selection command to name cells in the range A3:E8.
Using only names in your formulas, answer the questions in column A of the worksheet.

Solution:

To name cell B1:
1. Select cell B1.
2. Type "QuarterlyIncome" in the Name Box and press Enter:.
To name cells A3:E8:
1. Select cells A3:E8.
2. On the Formulas tab, in the Defined Names group, click the Create from Selection command:
3. In the Create Names from Selection dialog box, check Top row and Left column and click OK:
To answer the questions in column A of the worksheet:
1. How much was spent on Clothes and Fun Stuff in January? Type "=Jan Clothes+Jan Fun_Stuff".
2. How much was spent on Groceries in Jan and Mar? Type "=Jan Groceries+Mar Groceries".
3. What % of Quarterly Income did Electric account for from Jan to Mar? Type "=Total Electric/QuarterlyIncome".

Using Formulas That Span Multiple Worksheets

To create a formula that spans multiple worksheets:

If you haven't named cells, then:
1. Select the sheet and cell into which you wish to type the formula.
2. Type "=".
3. Select the sheet that includes the data you will use in your formula.
4. Select the cell that contains the data.
5. Enter an operator (+, -, *, /).
6. Either select another cell in that sheet or select another sheet and cell to complete the formula.
If your formula contains named cells or ranges, simply:
1. Select the sheet and cell into which you wish to type the formula.
2. Type "=".
3. Enter the formula using names.

Entering a Formula Using Data in Multiple Worksheets

Duration: 10 to 20 minutes.

In this exercise, you will enter a formula using data from multiple sheets first without names and then using named cells.

Open Multiple Worksheets.xlsx from your Excel2019.2/Exercises folder.
Answer the questions in rows 3, 4, and 5 of Sheet2 using the data in Sheet1 without using named cells.
Answer the questions in rows 10, 11, and 12 of Sheet2 using the data in Sheet1 using named cells. Note: The cells are already named.

Solution:

To answer the questions in rows 3, 4, and 5:
1. How much was spent on Clothes and Fun Stuff in January?
  1. Type "=".
  2. Select Sheet1 by clicking on it.
  3. Select cell B7.
  4. Type "+".
  5. Select cell B8.
  6. Press Enter.
  7. The formula you entered will read: "=Sheet1!B7+Sheet1!B8".
2. How much was spent on Groceries in Jan and Mar?
  1. Type "=".
  2. Select Sheet1 by clicking on it.
  3. Select cell B4.
  4. Type "+".
  5. Select cell D4.
  6. Press Enter.
  7. The formula you entered will read: "=Sheet1!B4+Sheet1!D4".
3. What percent of Quarterly Income did Electric account for from Jan to Mar?
  1. Type "=".
  2. Select Sheet1 by clicking on it.
  3. Select cell E5.
  4. Type "/".
  5. Select cell B1.
  6. Press Enter.
  7. The formula you entered will read: "=Sheet1!E5/QuarterlyIncome".
To answer the questions in rows 10, 11, and 12:
1. How much was spent on Clothes and Fun Stuff in January? Type "=Jan Clothes+Jan Fun_Stuff".
2. How much was spent on Groceries in Jan and Mar? Type "=Jan Groceries+Mar Groceries".
3. What percent of Quarterly Income did Electric account for from Jan to Mar? Type "=Total Electric/QuarterlyIncome".

Using the IF Function

The IF function can be used to execute formulas only under certain conditions or to execute different formulas based on specified conditions. This is known as conditional logic. To use the IF function, you need to know:

Logical Test. This is simply the thing you want to test. For example:
1. If the number is greater than 10, then...
2. If the value is "blue", then...
Value if True. This is the value to return if the requirement is met (the logical test is true).
Value if False. This is the value to return if the requirement is not met (the logical test is false).

Here are some things to know about the IF function:

In plain English, the IF function says: If X condition is true, put Y value in this cell; otherwise, put Z value in the cell.
The value returned by the IF function can be a number, text, a formula, or a reference to another cell.
Enter "" (open and close quotes) if you do not wish to return a value.
You can test up to seven conditions by nesting IF functions within the original IF function. Here is an example in which nested IF functions are used to return grades:

To use the IF function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
2. Under Select a function, select IF.
3. Click OK.
In the Function Arguments dialog box:
1. Enter the logical test (e.g., B2>10, B2<C2, B2="Blue").
2. Type in the value if true.
3. Type in the value if false.
4. Click OK.

Using AND/OR Functions

The AND and OR functions are similar to the IF function in that they are logical functions.

The syntax of the AND function is: =AND(logical1,logical2, ...). It returns TRUE if all arguments are true.

The syntax of the OR function is: =OR(logical1,logical2, ...). It returns TRUE if any arguments are true.

Note that when you see a small picture of a worksheet with a red arrow on it next to a data entry field, you can click this image to select a cell, rather than typing the cell's location into the data entry field. For example:

Click the circled image:
The Function Arguments dialog box opens up. Click a cell and the cell's location appears in the Function Arguments dialog box. Click the image to the far right of the Function Arguments dialog box to return to the previous dialog box:
Note that in the original dialog box, the selected cell's location has been added into the data entry field:

This is especially useful when referring to cells on a separate worksheet.

Here are some examples of the IF statement in use:

=IF(A1=B1,"Same","Different"):
=IF(A1="Blue",B1,C1):
=IF(A1>100,"Victory!","Try again."):

Using the SUMIF, AVERAGEIF, and COUNTIF Functions

SUMIF

There are a few variations of the IF function that may be useful to be aware of when working with Excel.

The SUMIF function is a variation of the IF function, which allows you to specify criteria for a sum. For example, you may want to sum only the numbers in a column that are above 100.

To use SUMIF, you need to know:

range. The range of cells to which you want to apply the criteria.
criteria. This can be text, numbers, a function, or an expression. For example, the criteria could be > 100.

AVERAGEIF

AVERAGEIF does what it sounds like: it averages a range of cells. For example, you could average students' grades in a spreadsheet.

To use AVERAGEIF, you need to know:

range. Enter a range of at least two cells to which to apply the criteria.
criteria. The criteria defines what is to be averaged, such as numbers, expressions, and text.

COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet the criteria you specify. For example, you can count the number of students who received As.

To use the COUNTIF function, you need to know:

range. Enter a range of at least two cells to which to apply the criteria.
criteria. The criteria defines what is to be averaged, such as numbers, expressions, and text.

Using the IF Function

Duration: 15 to 25 minutes.

In this exercise, you will practice using the IF function.

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "IF".
Use the IF function to enter "Yes" or "No" in column E based on whether revenue from each customer exceeded $10,000 (i.e., if revenue exceeded $10,000, enter "Yes"; otherwise, enter "No").
Use the IF function to enter "Yes" or "No" in column F based on whether the number of purchases from each customer was greater than or equal to 20 (i.e., if # of purchases exceeded 19, enter "Yes"; otherwise, enter "No".)
Use the IF function to enter the revenue received from customers located in Utica, and only Utica, in column G (i.e., if the customer is located in Utica, enter revenue; otherwise, leave blank).

Solution:

Use the IF function to enter "Yes" or "No" in column E based on whether revenue from each customer exceeded $10,000.
1. The information you need to enter this formula is:
  1. Logical Test: If revenue is greater than $10,000, then...
  2. Value if True: "Yes"
  3. Value if False: "No"
2. The formula is: =IF(B2>10000,"Yes","No")
3. Enter the formula using the Insert Function command:
  1. Select cell E2.
  2. On the Formulas tab, in the Function Library group, click the Insert Function command:
  3. In the Insert Function dialog box:
    1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
    2. Under Select a function, select IF.
    3. Click OK.
  4. In the Function Arguments dialog box, enter the following values and click OK:
    1. Logical_test: B2>10000
    2. Value_if_true: "Yes"
    3. Value_if_false: "No"
  5. Copy the formula from cell E2 to cells E3:E8.
Use the IF function to enter "Yes" or "No" in column F based on whether the number of purchases from each customer was greater than or equal to 20.
1. The information you need to enter this formula is:
  1. Logical Test: If the number of purchases is greater than 19, then...
  2. Value if True: "Yes"
  3. Value if False: "No"
2. The formula is: =IF(C2>19,"Yes","No")
3. Enter the formula using the Insert Function command:
  1. Select cell F2.
  2. On the Formulas tab, in the Function Library group, click the Insert Function command:
  3. In the Insert Function dialog box:
    1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
    2. Under Select a function, select IF.
    3. Click OK.
  4. In the Function Arguments dialog box, enter the following values and click OK:
    1. Logical_test: C2>19
    2. Value_if_true: "Yes"
    3. Value_if_false: "No"
  5. Copy the formula from cell F2 to cells F3:F8.
Use the IF function to enter the revenue received from customers located in Utica, and only Utica, in column G.
1. The information you need to enter this formula is:
  1. Logical Test: If city is Utica, then...
  2. Value if True: Revenue (B2)
  3. Value if False: None ("")
2. The formula is: =IF(D2="Utica",B2,"")
3. Enter the formula using the Insert Function command:
  1. Select cell G2.
  2. On the Formulas tab, in the Function Library group, click the Insert Function command:
  3. In the Insert Function dialog box:
    1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
    2. Under Select a function, select IF.
    3. Click OK.
  4. In the Function Arguments dialog box, enter the following values and click OK:
    1. Logical_test: D2="Utica"
    2. Value_if_true: B2
    3. Value_if_false: ""
  5. Copy the formula from cell G2 to cells G3:G8.

Using the PMT Function

The PMT function is used to calculate payments on loans. In order to use the PMT function, you need to know:

Rate. The interest rate.
Nper. The number of payments.
Pv. The present value of the future payments, or the amount of the loan.
Fv. The future value, or the cash balance after the final payment has been made. NOTE: the cash balance is not the remaining balance of the loan. Instead, it is the cash on hand after the loan is paid off. If the loan won't be completely paid off, then the future value is a negative number.
Type. Whether the payments are made at the beginning or end of each period.

To use the PMT function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "Payment" or, in the Or select a category drop-down box, select Financial.
2. Under Select a function, select PMT.
3. Click OK.
In the Function Arguments dialog box:
1. Enter the interest rate (Rate) or the cell in which it is located. If your worksheet contains the annual interest rate and payments will be made monthly, then select the annual rate and divide by 12.
2. Enter the number of payments (Nper).

Here are some examples:

To calculate a 24-month $3,000 loan with 9% interest, assuming the loan is to be completely paid off and payments are made at the end of each period:

=PMT(0.09/12,24,3000,0,0) or =PMT(0.09/12,24,3000):

To calculate a 15-year $200,000 loan with 6% interest, assuming half the loan is to be paid off and payments are made at the end of each period:

=PMT(0.06/12,180,200000,-100000):

Note that 15 years = 180 months.

To calculate a 15-year $200,000 loan with 6% interest, assuming half the loan is to be paid off and payments are made at the beginning of each period:

=PMT(0.06/12,180,200000,-100000,1):

Using the PMT Function

Duration: 15 to 25 minutes.

In this exercise, you will...

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "PMT".
Calculate the payments for Loans 1, 2, 3, and 4.
Assume you purchased a house for $240,000 and took out a 30-year mortgage for the whole amount with an interest rate of 6%. What is your payment? Enter the formula in cell B9.
Assume you purchased a car for $29,000 and took out a loan for the whole amount with an interest rate of 9%. You are to pay off $20,000 of the loan in 4 years. Payments are to be made at the beginning of each period. What is your payment? Enter the formula in cell B10.

Solution:

Loan 1:
1. Formula: "=PMT(C2/12,D2,B2,E2)"
2. Solution:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command:
  2. In the Insert Function dialog box, select PMT and click OK:
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: C2/12
    2. Nper: D2
    3. Pv: B2
    4. Fv: E2
    5. Type: Leave blank.
Loan 2:
1. Formula: "=PMT(C3/12,D3,B3)"
2. Solution:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
  2. In the Insert Function dialog box, select PMT and click OK.
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: C3/12
    2. Nper: D3
    3. Pv: B3
    4. Fv: Leave blank.
    5. Type: Leave blank.
Loan 3:
1. Formula: "=PMT(C4/12,D4,B4)"
2. Solution:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
  2. In the Insert Function dialog box, select PMT and click OK.
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: C4/12
    2. Nper: D4
    3. Pv: B4
    4. Fv: Leave blank.
    5. Type: Leave blank.
Loan 4:
1. Formula: "=PMT(C5/12,D5,B5,E5,1)"
2. Solution:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
  2. In the Insert Function dialog box, select PMT and click OK.
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: C5/12
    2. Nper: D5
    3. Pv: B5
    4. Fv: E5
    5. Type: 1.
Assume you purchased a house for $240,000 and took out a 30-year mortgage for the whole amount with an interest rate of 6%. What is your payment?
1. The formula is: =PMT(0.06/12,360,240000)
2. To solve this using the Insert Function command:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
  2. In the Insert Function dialog box, select PMT and click OK.
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: 0.06/12
    2. Nper: 360
    3. Pv: 240000
    4. Fv: Leave blank.
    5. Type: Leave blank.
Assume you purchased a car for $29,000 and took out a loan for the whole amount with an interest rate of 9%. You are to pay off $20,000 of the loan in 4 years. Payments are to be made at the beginning of each period. What is your payment?
1. Formula: =PMT(0.09/12,48,29000,9000,1)
2. To solve this using the Insert Function command:
  1. On the Formulas tab, in the Function Library group, click the Insert Function command.
  2. In the Insert Function dialog box, select PMT and click OK.
  3. In the Function Arguments dialog box, enter the following values and click OK:
    1. Rate: 0.09/12
    2. Nper: 48
    3. Pv: 29000
    4. Fv: 9000
    5. Type: 1.

Using the LOOKUP Function

The LOOKUP function returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.

To use the LOOKUP function, you need to know:

Lookup value. The value you will use to identify individual records in your table.
Lookup vector. For a vector syntax, this will be a range that contains one row or column.
Array. For an array syntax, this is the range you want to compare with the lookup value.

To use the LOOKUP function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "LOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
2. Under Select a function, select LOOKUP.
3. Click OK.
4. In the Select Arguments dialog box, choose a vector or array syntax and click OK.
In the Function Arguments dialog box (the following is for a vector):
1. Enter the Lookup_value.
2. Enter the Lookup_vector.
3. Enter the Result_vector.
4. Click OK.

Using the VLOOKUP Function

The VLOOKUP function is used to pull a value from a list or table based on a corresponding value. For example, if you have a worksheet with a table showing employee names, hire date, and salary, you could use VLOOKUP in a separate worksheet to pull the hire date and salary for individual employees from the first worksheet. In this example, the employee name serves as a key, identifying which information from the first worksheet you wish to pull.

To use the VLOOKUP function, you need to know:

Lookup value. The value you will use to identify individual records in your table. The Lookup Value must be in the left-most column of your table.
Table array. The table that contains the data you will use VLOOKUP to retrieve. This table can be in another worksheet or even another workbook from the one in which you enter the VLOOKUP function.
Col index num. The Lookup Value is always in the left-most column of the Table Array (column #1, regardless of where in the worksheet the table is located). The next column to the right is column #2, then column #3, etc. The Col index num is simply the number of the column that contains the value you wish to retrieve.
Range lookup. Enter False if the Lookup Value must match exactly. If you enter True or leave blank, Excel will assume the table is sorted in ascending order and will select the best match. Note that if the table is not sorted in ascending order, Excel likely won't correctly find the best match.

To use the VLOOKUP function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
2. Under Select a function, select VLOOKUP.
3. Click OK.
In the Function Arguments dialog box:
1. Enter the Lookup_value or the cell in which it is located.
2. Enter the Table_array.
3. Enter the Col_index_num.
4. Enter the Range_lookup. If you leave this blank, Excel will treat this as if you entered True.
5. Click OK.

Using the VLOOKUP Function

Duration: 15 to 25 minutes.

In this exercise, you will use the VLOOKUP function to automatically fill in the description and price of items on an invoice based on the item number.

Open VLOOKUP.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "Invoice".
Use the VLOOKUP function to query the Description and Price from the table located in the sheet named "Table". You will need to insert the VLOOKUP function into cells B7:B15 and D7:D15.
1. Hint: Including absolute references when referring to the range will enable the formulas to be copied to other cells within the column.
What is Item Number 135798 and what does it cost?
What is Item Number 678452 and what does it cost?

Solution:

To query the Description from the table located in the sheet named "Table":
1. In the sheet named "Invoice", select cell B7.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
  2. Under Select a function, select VLOOKUP.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. Enter the Lookup_value: A7.
  2. Enter the Table_array: Table!A2:C9 (click the cell selection arrow, then the sheet named "Table", and then select the cells).
  3. Enter the Col_index_num: 2 (because the description is in the second column in the table).
  4. Enter the Range_lookup: False (because you require an exact match).
  5. Click OK.
To query the Price from the table located in the sheet named "Table":
1. In the sheet named "Invoice", select cell D7.
2. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
3. In the Insert Function dialog box:
  1. Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
  2. Under Select a function, select VLOOKUP.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. Enter the Lookup_value: A7.
  2. Enter the Table_array: Table!A2:C9 (click the cell selection arrow, then the sheet named "Table", and then select the cells).
  3. Enter the Col_index_num: 3 (because the description is in the third column in the table).
  4. Enter the Range_lookup: False (because you require an exact match).
  5. Click OK.
Select cell B7 and edit the formula to make the table references absolute (change A2:C9 to $A$2:$C$9). Then do the same in cell D7.
Copy cell B7 to cells B8:B15 and cell D7 to D8:D15.
Enter "135798" into any cell under "Item Number" on the invoice. Item Number 135798 is a rake and costs $12.98.
Enter "678452" into any cell under "Item Number" on the invoice. Item Number 678452 is a wrench and costs $6.99.

Using the HLOOKUP Function

The HLOOKUP function is very similar to the VLOOKUP function. The only significant difference is that while the VLOOKUP function looks for a value in the left-most column of a table and returns a value on the same row as that value, the HLOOKUP function looks for a value in the top row of a table and returns a value in the same column as that value. To use the HLOOKUP function, you need to know:

Lookup value. The value you will use to identify individual records in your table. The Lookup Value must be in the top row of your table.
Table array. The table that contains the data you will use HLOOKUP to retrieve. This table can be in another worksheet or even another workbook from the one in which you enter the HLOOKUP function.
Row index num. The Lookup Value is always in the top row of the Table Array (row #1, regardless of where in the worksheet the table is located). The next row down is row #2, then row #3, etc. The Row index num is simply the number of the row that contains the value you wish to retrieve.
Range lookup. Enter False if the Lookup Value must match exactly. If you enter True or leave blank, Excel will assume the table is sorted in ascending order and will select the best match. Note that if the table is not sorted in ascending order, Excel likely won't correctly find the best match.

To use the HLOOKUP function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "HLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
2. Under Select a function, select HLOOKUP.
3. Click OK.
In the Function Arguments dialog box:
1. Enter the Lookup_value or the cell in which it is located.
2. Enter the Table_array.
3. Enter the Row_index_num.
4. Enter the Range_lookup. If you leave this blank, Excel will treat this as if you entered True.
5. Click OK.

Using the CONCAT Function

The CONCAT function (called CONCATENATE in versions of Excel previous to 2019) is used to join the contents of multiple cells. For example, if you have a worksheet with first names in one column and last names in another column, you can use the CONCAT function to join the first and last names into one column. This function supports cell references, as well as range references.

Here are some things to know about the CONCAT function:

You can join up to 255 text strings.
The text string can include text, numbers, and cell references.
You can include text not found in the worksheet by adding it via the Function Arguments dialog box (or directly into the formula). For example, if you have a worksheet with city names in one column and state names in another column, and wish to join them into one column, you can add a comma and space (", ") between the two.

To use the CONCAT function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "CONCAT" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select CONCAT.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text1 data entry field, enter the first text field or the cell in which it is located.
2. In the Text2 data entry field, enter the first text field or the cell in which it is located.
3. Etc. (New fields appear when you use the bottom field.)
4. Click OK.

Using the CONCAT Function

Duration: 10 to 15 minutes.

In this exercise, you will practice using the CONCAT function.

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "CONCATENATE".
Use the CONCAT function to join the first and last names into full names in column C.
Use the CONCAT function to join the cities and states so that they appear as "city, state".

Solution:

To join the first and last names into full names:
1. In the sheet named "CONCAT", select cell C2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "CONCAT" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select CONCAT.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text1 data entry field, enter cell A2.
  2. In the Text2 data entry field, enter a space (" ").
  3. In the Text3 data entry field, enter cell B2.
  4. Click OK.
5. Copy cell C2 to cells C3:C7.
To join the cities and states so that they appear as "city, state":
1. In the sheet named "CONCAT", select cell G2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command.
3. In the Insert Function dialog box:
  1. Search on "CONCAT" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select CONCAT.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text1 data entry field, enter cell E2.
  2. In the Text2 data entry field, enter a comma and a space (", ").
  3. In the Text3 data entry field, enter cell F2.
  4. Click OK.
5. Copy cell G2 to cells G3:G7.

Using the TRANSPOSE Function

You can use the TRANSPOSE function to return a horizontal range of cells as a vertical range or a vertical range as a horizontal range.

To use TRANSPOSE, you must know the array, the range of cells you want to transpose. To use the TRANSPOSE function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "TRANSPOSE" or, in the Or select a category drop-down box, select Recommended.
2. Under Select a function, select TRANSPOSE.
3. Click OK.
In the Function Arguments dialog box:
1. In the Array data entry field, enter the range of cells or an array of values you want to transpose.
2. Click OK.

Using the PROPER, UPPER, and LOWER Functions

The PROPER function is used to make the first letter in each word uppercase and all other letters lowercase. To use the PROPER function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "PROPER" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select PROPER.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text data entry field, enter the cell containing the text you wish to capitalize the first letters of.
2. Click OK.

The UPPER Function

The UPPER function is used to make all letters in words uppercase.

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "UPPER" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select UPPER.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text data entry field, enter the cell containing the text you wish to make all uppercase.
2. Click OK.

The LOWER function

The LOWER function is used to make all letters in words lowercase.

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "LOWER" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select LOWER.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text data entry field, enter the cell containing the text you wish to make all lowercase.
2. Click OK.

The TRIM Function

Another text function you may use is the TRIM function. TRIM allows you to remove the spaces in phrases, leaving only single spaces between words.

The LEN Function

Another text function that may be useful is the LEN function. LEN allows you to determine a text string's length.

Using the PROPER Function

Duration: 5 to 10 minutes.

In this exercise, you will practice using the PROPER function.

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "PROPER".
In column B, use the PROPER function to capitalize the first letters of the names in column A.

Solution:

To capitalize the first letters of the names in column A:
1. In the sheet named "PROPER", select cell B2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "PROPER" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select PROPER.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text data entry field, enter A2.
  2. Click OK.
5. Copy cell B2 to cells B3:B7.

Using the LEFT, RIGHT, and MID Functions

The LEFT and RIGHT functions are used to return characters from the start or end of a string. For example, you could use the LEFT and RIGHT functions to:

Return the area codes from a list of phone numbers (if the phone numbers are formatted as 315-333-4444, you would use the LEFT function to return the first three characters).
Return the zip codes from a list of addresses (if the addresses are formatted as Syracuse, NY 13210, you would use the RIGHT function to return the last five characters).
Return a piece of an identifying ID (if you have a list of IDs in which the first four digits represent a product, the next six digits represent the date, and the last three digits represent the store the product was sold from, you could use the LEFT function to return the four digits representing the product or the RIGHT function to return the three digits representing the store the product was sold from).

To use the LEFT and RIGHT functions:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "LEFT" or "RIGHT" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select LEFT or RIGHT.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text field, enter the cell containing the text string from which you wish to return characters.
2. In the Num_Chars field, enter the number of characters you want to return.
3. Click OK.

The MID Function

The MID function is used when you want to return a specific amount of characters from a string of text. You specify the number of characters.

To use the MID function:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "MID" or, in the Or select a category drop-down box, select Text.
2. Under Select a function, select MID.
3. Click OK.
In the Function Arguments dialog box:
1. In the Text field, enter the text characters that you wish to return.
2. In the Start_num field, enter the position of the first character that you wish to extract.
3. In the Num_Chars field, enter the number of characters you want to return.
4. Click OK.

Excel 2016 inroduced a number of new functions, including the following. To view all of the functions, click each button in the Function Library.

DECIMAL function: Available on the Math & Trig tab. This function converts the text of a number in a given base into a decimal number.
ACOT function: Available on the Math & Trig tab. This function returns the arccotangent of a number.
ENCODEURL function: Available on the More Functions tab in the Web section. This function returns a URL-encoded string.
DAYS function: Available on the Date & Time tab. This function shows the number of days between two dates.

Excel 2019 then added some more new functions:

IFS: Tests conditions in the order you specify.
MINIFS: Returns the smallest function in a range (which meets the criteria).
MAXIFS: Returns the largest function in a range (which meets the criteria).
SWITCH: Used to return the first matching result of the evaluation of an expression against a list of values in order.
TEXTJOIN: Used to combine text from different ranges; each item is separated by a delimiter.

Using the LEFT and RIGHT Functions

Duration: 5 to 15 minutes.

In this exercise, you will practice using the LEFT and RIGHT functions.

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "LEFT-RIGHT".
In column B, use the LEFT function to display only the area codes of the phone numbers listed in column A.
In columns E and F, use the LEFT and RIGHT functions to display the Store IDs and Salesperson IDs for the sales listed in column D.

Solution:

To display only the area codes of the phone numbers listed in column A:
1. In the sheet named "LEFT-RIGHT", select cell B2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "LEFT" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select LEFT.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text field, enter cell A2.
  2. In the Num_Chars field, enter the number "3".
  3. Click OK.
5. Copy cell B2 to cells B3:B7.
To display the Store IDs for the sales listed in column D:
1. In the sheet named "LEFT-RIGHT", select cell E2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command.
3. In the Insert Function dialog box:
  1. Search on "LEFT" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select LEFT.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text field, enter cell D2.
  2. In the Num_Chars field, enter the number "3".
  3. Click OK.
5. Copy cell E2 to cells E3:E7.
To display the Salesperson IDs for the sales listed in column D:
1. In the sheet named "LEFT-RIGHT", select cell F2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command.
3. In the Insert Function dialog box:
  1. Search on "RIGHT" or, in the Or select a category drop-down box, select Text.
  2. Under Select a function, select RIGHT.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Text field, enter cell D2.
  2. In the Num_Chars field, enter the number "2".
  3. Click OK.
5. Copy cell F2 to cells F3:F7.

Using Date Functions

The three most commonly used date functions are YEAR, MONTH, and DAY. These functions are used to return only the year, month, or day from a date. To use the YEAR, MONTH, and DAY functions:

On the Formulas tab, in the Function Library group, click the Insert Function command:
In the Insert Function dialog box:
1. Search on "Year", "Month", or "Day" or, in the Or select a category drop-down box, select Date & Time.
2. Under Select a function, select YEAR, MONTH, or DAY.
3. Click OK.
In the Function Arguments dialog box:
1. In the Serial_number field, enter the cell in which the full date is located.
2. Click OK.

Using the NOW and TODAY Functions

Two other date functions are NOW and TODAY. They are useful when you need to show the current date information or need to calculate something based on that information.

NOW: Returns the current date and time.
TODAY: Returns the current date, but the time is set to 12:00:00 AM.

Using the YEAR, MONTH, and DAY Functions

Duration: 5 to 15 minutes.

In this exercise, you will practice using the YEAR, MONTH, and DAY functions.

Open Functions.xlsx from your Excel2019.2/Exercises folder and go to the sheet named "Date".
Use the YEAR, MONTH, and DAY functions to add the years, months, and days of the dates found in column A to columns B, C, and D.

Solution:

To add the year of the dates found in column A to column B:
1. In the sheet named "Date", select cell B2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "Year" or, in the Or select a category drop-down box, select Date & Time.
  2. Under Select a function, select YEAR.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Serial_number field, enter cell A2.
  2. Click OK.
5. Copy cell B2 to cells B3:B6.
To add the month of the dates found in column A to column C:
1. In the sheet named "Date", select cell C2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "Month" or, in the Or select a category drop-down box, select Date & Time.
  2. Under Select a function, select MONTH.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Serial_number field, enter cell A2.
  2. Click OK.
5. Copy cell C2 to cells C3:C6.
To add the day of the dates found in column A to column D:
1. In the sheet named "Date", select cell D2.
2. On the Formulas tab, in the Function Library group, click the Insert Function command:
3. In the Insert Function dialog box:
  1. Search on "Day" or, in the Or select a category drop-down box, select Date & Time.
  2. Under Select a function, select DAY.
  3. Click OK.
4. In the Function Arguments dialog box:
  1. In the Serial_number field, enter cell A2.
  2. Click OK.
5. Copy cell D2 to cells D3:D6.

Creating Scenarios

In Excel 2013 and later, scenarios are input values that you can create and save, which return different calculated results. You can use them in what-if scenarios.

Utilize the Watch Window

The Watch Window is a feature that allows you to keep formulas that you need to view in sight, rather than having to jump around in a worksheet.

To use the Watch Window:

Select the Formulas tab, and in the Formula Auditing group, select Watch Window.
In the worksheet, select the cells you want to watch and click Add Watch in the Watch Window.
In the Add Watch dialog box, click Add.

Consolidate Data

To consolidate data from multiple worksheets into one master worksheet:

Select the Data tab, and from the Data Tools tab, select Consolidate.
While in the Consolidate dialog box, click and drag to select cells.
Use the Add button to continue to add data, and click OK when you are done.

Enable Iterative Calculations

You can enable iterative calculations to locate circular references.

To enable iterative calculations:

From the File menu tab, select Options.
Select the Formulas section, and from the Calculation options section, check the Enable iterative calculation check box.

What-If Analyses

The Data tab's Forecast group (in Excel 2013, the Data Tools group) contains the What-If Analysis drop-down list, which contains a number of what-if tools. One of these tools is Goal Seek.

To use Goal Seek:

Select the cell with the formula you are going to solve for.
Select the Data tab, and in the Forecast group (Data Tools group in Excel 2013), select What-If Analysis, and then select Goal Seek.
In the To value field, enter the goal.
In the By changing cell field, enter the cell where you want the result to be and then click OK.

Use the Scenario Manager

To manage what-if models so you can quickly access them, you can use the Scenario Manager.

To use the Scenario Manager:

Select the Data tab, and in the Forecast group (Data Tools group in Excel 2013), select What-If Analysis, and then select Scenario Manager.
In the Scenario Manager dialog box, click Add. Type a name for the scenario in the Scenario name text box, and in the Changing cells text box, type the names of the cells you want to change and click OK.
In the Scenario Values dialog box that appears, type the values for the changing cells.
Click Add to add more scenarios and click OK when you are done.

Use Financial Functions

The Excel financial functions are complex financial formulas that contain multiple steps. These functions cover things as calculating net present value, the depreciation of an asset, and loan payments, amongst others.

To access the financial functions, from the Formulas tab, in the Function Library group, select Financial. Financial Command