Insert functions into a formula

Formulas that combine operators with basic operands such as numeric and string values are the mainstay of any Excel spreadsheet. But to get the most benefit from the spreadsheet model, you need to expand your formula repertoire to include worksheet functions. Excel includes dozens of these functions, and they’re essential to making your worksheet easier to work with and more powerful.

To insert a function into a formula

1. Enter your formula up to the point where you want to insert the function.

2. On the Formulas tab, in the Function Library group, click the category that contains the function you want to use. Then on the category menu, click the function.

3. In the Function Arguments dialog box, enter the function arguments, then click OK.

Perform logical operations by using the IF, AND, OR, and NOT functions

In the computer world, we very loosely define something as intelligent if it can perform tests on its environment and act in accordance with the results of those tests. However, a computer is a binary machine, so “acting in accordance with the results of a test” means that it can do only one of two things. Even with this limited range of options, you can still bring a great deal of intelligence to your worksheets. Your formulas will be able to test the values in cells and ranges and then return results based on those tests. This is all done with the logical functions in Excel, which are designed to create decision-making formulas.

The following table describes the most common logical functions.

You use the IF function to test some condition and then return a value based on the result of that test. This is the simplest version of the IF function:

IF(logical_test, value_if_true)

The logical_test argument is a logical expression—that is, an expression that returns TRUE or FALSE (or their equivalent numeric values: 0 for FALSE and any other number for TRUE); the value_if_true argument is the value returned by the function if logical_test evaluates to TRUE.

A logical expression compares two or more numbers, text strings, cell contents, or function results. If the expression is true, it’s given the logical value TRUE (which is equivalent to any nonzero value); if the expression is false, it’s given the logical value FALSE (which is equivalent to zero). The following table summarizes the comparison operators you can use in logical expressions.

For example, suppose cell B2 contains a sales rep’s total sales and you want to give the rep a 10-percent bonus if those sales are greater than $100,000. Here’s a formula that does that:

=IF(B2 > 100000, B2 * 0.1)

The logical expression B1 > 100000 is used as the test. Assume that you add this formula to cell C2. If the logical expression proves to be true (that is, if the value in cell B2 is greater than 100,000), the function returns the value B2 * 0.1—that is, 10 percent of the number in B2—and that’s the value you see in cell C2.

When the IF function test returns FALSE (that is, the value in column B is less than or equal to 100,000), the function returns FALSE as its result. That’s not inherently bad, but the worksheet would look tidier (and, hence, be more useful) if the formula returned, for instance, the value 0 instead.

To do this, you need to use the full IF function syntax:

IF(logical_test, value_if_true, value_if_false)

The extra value_if_false argument is the value returned by the function if logical_test evaluates to FALSE. Here’s a modification of the sales rep bonus calculation that takes a FALSE result into account:

=IF(B2 > 100000, B2 * 0.1, 0)

It’s often necessary to perform an action if and only if two conditions are true. For example, you might want to pay a salesperson a bonus if and only if dollar sales exceed a certain amount and unit sales also exceed some minimum value. If the dollar sales, the unit sales, or both fall below the minimum, no bonus is paid. In Boolean logic, this is called an And condition because one expression and another must be true for a positive result.

In Excel, And conditions are handled by the AND logical function:

AND(logical1[, logical2,...])

Here, logical1 and logical2 arguments are the logical conditions to test. (The ellipsis means that you can enter as many conditions as you need.) The AND result is calculated as follows:

• If all the arguments return TRUE (or any nonzero number), AND returns TRUE.

• If one or more of the arguments return FALSE (or 0), AND returns FALSE.

Consider a formula that uses AND to check whether a sales rep’s sales total is greater than $100,000 and units total is greater than 10,000. That formula could look like this:

=AND(B2 > 100000, C2 > 10000)

In many worksheet models, you need to take an action if one thing or another is true. For example, you might want to pay a salesperson a bonus if she exceeds the dollar sales budget or if she exceeds the unit sales budget. In Boolean logic, this is called an Or condition.

In Excel, Or conditions are handled by the OR function:

OR(logical1[, logical2,...])

Here, logical1 and logical2 arguments are the logical conditions to test. (The ellipsis means that you can enter as many conditions as you need.) The OR result is calculated as follows:

• If one or more of the arguments return TRUE (or any nonzero number), OR returns TRUE.

• If all of the arguments return FALSE (or 0), OR returns FALSE.

Consider the following formula, which uses OR to check whether a sales rep’s sales total is greater than $100,000 or the units total is greater than 10,000:

=OR(B2 > 100000, C2 > 10000)

You sometimes need to return the opposite of a logical expression. For example, you might want to set up a worksheet that looks for the sales reps who did not make their sales quota and then flag those reps for further training. If you have a worksheet that already returns a TRUE or FALSE value based on whether a sales rep meets his quota, flagging whether the sales rep needs more training is a matter of returning the opposite value: if a sales rep meets her quota (TRUE), she doesn’t need training (FALSE); if a sales rep doesn’t meet his quota (FALSE), he does need training (TRUE).

In Excel, you return the opposite of a logical expression by using the NOT function:

NOT(logical)

Here, the logical argument is the logical condition to test. The NOT result is calculated as follows:

• If logical is TRUE (or any nonzero number), NOT returns FALSE.

• If logical is FALSE (or 0), NOT returns TRUE.

The following formula uses NOT in the Training Required? column (E) to return the opposite of the logical value in the Bonus column (D):

=NOT(D2)

To insert a logical function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Logical, click one of the functions in the list (such as IF, AND, OR, or NOT), and then enter the arguments, if any, as described earlier in this topic.

Perform logical operations by using nested functions

You can create sophisticated logical tests by combining one or more logical functions within a single expression. In particular, you can create complex expressions by including one logical function within another. This is called nesting, and the inner function is called a nested function.

A good time to use nested IF functions arises when you need to calculate a tiered payment or charge. That is, if a certain value is X, you want one result; if the value is Y, you want a second result; and if the value is Z, you want a third result. For example, suppose you want to calculate tiered bonuses for a sales team as follows:

• If the salesperson did not meet the sales target, no bonus is given.

• If the salesperson exceeded the sales target by less than 10 percent, a bonus of $1,000 is awarded.

• If the salesperson exceeded the sales target by 10 percent or more, a bonus of $5,000 is awarded.

Assuming that cell D2 contains the percentage that each salesperson’s actual sales were above or below her target sales, here’s a formula that handles these rules:

=IF(D2 < 0, "", IF(D2 < 0.1, 1000, 5000))

To nest logical functions

1. Insert the first logical function into your formula.

2. Enter the function arguments as described earlier in this topic, including the logical function you want to nest as one of the arguments.

Perform multiple logical tests with the IFS function

Nesting one IF function inside another is a handy way to perform a couple of logical tests, but the method quickly becomes unwieldly and difficult to decipher when the nesting goes three or more IF functions deep. If your data analysis requires more than two logical tests, you can make your worksheet model easier to read by turning to the IFS function:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2,…])

The IFS function consists of a series of logical tests, each of which has an associated return value. IFS performs each logical test in turn, and when it comes across the first logical test to return TRUE, it returns that logical test’s associated value.

For example, here’s how you’d use IFS to calculate the tiered bonuses that I introduced in the previous section:

=IFS(D2 < 0, 0, D2 < 0.1, 1000, D2 >= 0.1, 5000)

Perform logical operations with criteria

You use the SUM, AVERAGE, and COUNT functions in Excel to return the total value, the mean value, and the number of values, respectively, within a specified range. These functions operate over the entire range, but it’s often the case that you want the sum, the average, or the count of only those cells that meet some criterion. For example, you might want the total sales for just the customers from the United States. Even more sophisticated calculations are possible when you use multiple criteria. For example, you might want the total sales for those customers who are from the United States and who are based in Oregon.

These seem like they would require complex combinations of the IF, AND, and OR functions nested within the SUM, AVERAGE, or COUNT function. Fortunately, Excel includes three summary functions that you can use to specify multiple criteria without complex nesting: SUMIF, AVERAGEIF, and COUNTIF. In each case, the “IF” part of the function name implies that an IF function is built into each function, without being explicitly invoked.

The SUMIF function is similar to SUM, except that it sums only those cells in a range that meet a specified condition:

SUMIF(range, criteria[, sum_range])

Here, the range argument is the range of cells to use for the criteria; the criteria argument is the criteria, entered as text, that Excel applies to range to determine which cells to sum; sum_range is an optional argument that specifies the range from which the sum values are taken. Excel sums only those cells in sum_range that correspond to the cells in range and meet the criteria. If you omit sum_range, Excel uses range for the sum.

For the criteria argument, you generally use the following format:

"operator value"

Here, you replace operator with a comparison operator such as equal to (=) or greater than (>), and you replace value with a comparison value. Many Excel functions also support text-only criteria arguments that optionally use one or more wildcard characters: * to match any number of characters and ? to match any single character.

For example, the following formula sums only those values in the range B3:B21 that are greater than 100000:

=SUMIF(B3:B21, "> 100000")

The COUNTIF function counts the number of cells in a range that meet a single condition:

COUNTIF(range, criteria)

Here, replace range with the range of cells to use for the count; replace criteria with the criteria, entered as text, that Excel applies to range to determine which cells to count.

For example, you can use the following formula to count the number of cells in the range D3:D21 that are greater than or equal to 0.1:

=COUNTIF(D3:D21, ">= 0.1")

The AVERAGEIF function calculates the average of a range set for those items that meet a specified condition:

AVERAGEIF(range, criteria[, average_range])

Here, the range argument is the range of cells to use for the criteria; the criteria argument is the criteria, entered as text, that Excel applies to range to determine which cells to average; average_range is an optional argument that specifies the range from which the average values are taken. Excel averages only those cells in average_range that correspond to the cells in range and meet the criteria. If you omit average_range, Excel uses range for the average.

For example, the following formula averages only those values in the range D3:D21 that correspond to those values in the range C3:C21 that are greater than or equal to 10,000:

=AVERAGEIF(C2:C21, "> 10000", D3:21)

To insert a SUMIF function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Math & Trig, click SUMIF, and then enter the arguments, as described earlier in this topic.

To insert an AVERAGEIF or COUNTIF function into a formula

• ➜   On the Formulas tab, in the Function Library group, click More Functions, click Statistical, click AVERAGEIF or COUNTIF, and then enter the arguments, as described earlier in this topic.

Perform logical operations with multiple ranges and criteria

The SUMIF, COUNTIF, and AVERAGEIF functions that I describe in the previous section replace simple nested functions with a single condition. However, there are many situations in which your logical formula requires multiple criteria applied to multiple ranges. To handle these situations, Excel includes five summary functions that you can use to specify multiple criteria: SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS. In each case, the “IFS” part of the function name implies that multiple IF functions are built into each function, without being explicitly invoked.

The SUMIFS function sums cells in a range that correspond to those cells in one or more ranges that meet one or more criteria:

SUMIFS(sum_range, range1, criteria1[, range2, criteria2, ...])

The sum_range argument is the range from which the sum values are taken. Excel sums only those cells in sum_range that correspond to the cells that meet the criteria. The range1 argument is the first range of cells to use for the sum criteria, and the criteria1 argument is the first criterion, entered as text, that determines which cells to sum. Excel applies the criterion to range1. You can enter up to 127 range/criterion pairs.

Consider the following SUMIFS function example. In this model, SUMIFS is used to calculate the total bonuses (range E3:E21) just for those sales reps whose total units (range C3:C21) are greater than 10,000:

=SUMIFS(E3:E21, C3:C21, " > 10000")

The AVERAGEIFS function averages cells in a range that correspond to those cells in one or more ranges that meet one or more criteria:

AVERAGEIFS(average_range, range1, criteria1[, range2, criteria2, ...])

The average_range argument is the range from which the average values are taken. Excel averages only those cells in average_range that correspond to the cells that meet the criteria. The range1 argument is the first range of cells to use for the average criteria, and the criteria1 argument is the first criterion, entered as text, that determines which cells to average. Excel applies the criterion to range1. You can enter up to 127 range/criterion pairs.

In the following example, AVERAGEIFS is used to calculate the average sales (range B3:B21) just for those sales reps whose total units (C3:C21) are greater than 10,000 and whose return rate (D3:D21) is less than 10 percent:

=AVERAGEIFS(B3:B21, C3:C21, " > 10000", D3:D21, " < 0.1")

The COUNTIFS function counts the number of cells in one or more ranges that meet one or more criteria:

COUNTIFS(range1, criteria1[, range2, criteria2, ...])

The range1 argument is the first range of cells to use for the count, and the criteria1 argument is the first criterion, entered as text, that determines which cells to count. Excel applies the criterion to range1. You can enter up to 127 range/criterion pairs.

In the following example, COUNTIFS is used to count the number of sales reps whose dollar sales (range B3:B21) are greater than $100,000, whose unit sales (C3:C21) are greater than 10,000, and whose return rate (D3:D21) is less than 10 percent:

=COUNTIFS(B3:B21, " > 100000", C3:C21, " > 10000", D3:D21, " < 0.1")

The MAXIFS function returns the maximum value from a range that corresponds to those cells in one or more ranges that meet one or more criteria:

MAXIFS(max_range, range1, criteria1[, range2, criteria2, ...])

The max_range argument is the range from which the maximum value is taken. Excel looks for the maximum only in those cells in max_range that correspond to the cells that meet the criteria. The range1 argument is the first range of cells to use for the maximum criteria, and the criteria1 argument is the first criterion, entered as text, that determines which cells to include. Excel applies the criterion to range1. You can enter up to 127 range/criterion pairs.

Consider the following MAXIFS function example. In this model, MAXIFS is used to calculate the maximum return rate (from the range D3:D21) just for those sales reps whose total sales (range B3:B21) are greater than $100,000 and whose total units (range C3:C21) are greater than 10,000:

=MAXIFS(D3:D21, B3:B21, "> 100000", C3:C21, " > 10000")

The MINIFS function returns the minimum value from a range that corresponds to those cells in one or more ranges that meet one or more criteria:

MINIFS(min_range, range1, criteria1[, range2, criteria2, ...])

The min_range argument is the range from which the minimum value is taken. Excel looks for the minimum only in those cells in min_range that correspond to the cells that meet the criteria. The range1 argument is the first range of cells to use for the minimum criteria, and the criteria1 argument is the first criterion, entered as text, that determines which cells to include. Excel applies the criterion to range1. You can enter up to 127 range/criterion pairs.

Consider the following MINIFS function example. In this model, MINIFS is used to calculate the minimum units (from the range C3:C21) just for those sales reps whose total sales (range B3:B21) are greater than $100,000 and whose return rate (range D3:D21) are less than 10 percent:

=MINIFS(C3:C21, B3:B21, " > 100000", D3:D21, "< 0.1")

To insert a SUMIFS function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Math & Trig, click SUMIFS, and then enter the arguments, as described earlier in this topic.

To insert an AVERAGEIFS, COUNTIFS, MAXIFS, or MINIFS function into a formula

• ➜   On the Formulas tab, in the Function Library group, click More Functions, click Statistical, click the function—AVERAGEIFS, COUNTIFS, MAXIFS, or MINIFS,—and then enter the arguments, as described earlier in this topic.

Apply multiple exact-match criteria to a value

The IFS function that I describe earlier in this objective enables you to create sophisticated criteria using comparison operators such as less than (<) and greater than or equal to (>=). However, it’s often the case that your comparisons require only exact matches using the equal operator (=). In such cases, it is often easier to use the SWITCH function rather than IFS.

The SWITCH function evaluates an expression, compares the result to a list of values that you supply, and then returns a value that corresponds to the first match that Excel finds:

SWITCH(expression, value_to_match1, value_to_return1[, value_to_match2, value_to_return2, ..., value_if_no_match])

SWITCH first evaluates expression. SWITCH then compares that result to each value_to_match argument; when SWITCH comes across the first match, it returns the corresponding value_to_return; if no match is found, SWITCH returns the value_if_no_match argument. You can enter up to 126 match/return value pairs.

For example, given a date, the MONTH function returns a number between 1 (for January) and 12 (for December). For a date in cell A2, you can use the following SWITCH formula to return the date’s month name based on the results of the MONTH(A2) expression:

==SWITCH(MONTH(A2), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "No matching month!")

To insert a SWITCH function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Logical, click SWITCH, and then enter the arguments, as described earlier in this topic.

Reference the date and time by using the NOW and TODAY functions

If you need a date for an expression operand or a function argument, you can enter it by hand if you have a specific date in mind. Much of the time, however, you need more flexibility, such as always entering the current date or time.

When you need to use the current date in a formula, a function, or an expression, use the TODAY function, which doesn’t take any arguments. This function returns the serial number of the current date, with midnight as the assumed time. For example, on November 1, 2019, the TODAY function returns the serial number 43770.0.

Note that TODAY is a dynamic function that doesn’t always return the same value. Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, TODAY updates its value to return the current system date.

When you need to use the current time in a formula, a function, or an expression, use the NOW function, which doesn’t take any arguments. This function returns the serial number of the current time, with the current date as the assumed date. For example, at 11:24 AM on November 1, 2019, the NOW function returns the serial number 43770.47549.

If you want only the time component of the serial number, subtract TODAY from NOW:

=NOW - TODAY

Just like the TODAY function, NOW is a dynamic function that doesn’t keep its initial value (that is, the time at which you entered the function). Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, NOW updates its value to return the current system time.

To insert the TODAY or NOW function in a formula

• ➜   On the Formulas tab, in the Function Library group, click Date & Time, then click TODAY or NOW.

Serialize numbers by using date and time functions

The date functions in Excel work with or return date serial numbers. You can also work in the opposite direction by taking numbers that represent a specified year, month, and day, and serialize them into a valid Excel date. When you have a date serial number, you can use more Excel date functions to manipulate and perform calculations with those dates.

Similarly, the time functions work with or return time serial numbers, but you can also work in the opposite direction by taking numbers that represent a specified hour, minute, and second, and serialize them into a valid Excel time. When you have a time serial number, you can use other Excel time functions to manipulate and perform calculations with those times.

A date consists of three components: the year, month, and day. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper date out of them. You can do that by using the DATE function:

DATE(year, month, day)

The following table describes the DATE function arguments.

For example, the expression DATE(2020, 12, 25) returns Christmas Day in 2020.

Note, too, that DATE adjusts for wrong month and day values. For example, the expression DATE(2020, 12, 32) returns January 1, 2021. Here, DATE adds the extra day (there are 31 days in December) to return the date of the next day.

The three components of a date—year, month, and day—can also be extracted individually from a specified date. This might not seem all that interesting at first, but many useful techniques arise out of working with a date’s component parts. You can extract a date’s components by using the YEAR, MONTH, and DAY functions:

YEAR(serial_number) MONTH(serial_number) DAY(serial_number)

In each function, serial_number is the date (or a string representation of the date) you want to work with. For example, on August 23, 2020, the expression YEAR(TODAY) returns 2020.

The MONTH function returns a number between 1 and 12 that corresponds to the month component of a specified date. The DAY function returns a number between 1 and 31 that corresponds to the day component of a specified date.

The WEEKDAY function returns a number that corresponds to the day of the week on which a specified date falls:

WEEKDAY(serial_number[, return_type])

The following table describes the WEEKDAY function arguments.

For example, WEEKDAY("8/23/2020") returns 1 because August 23, 2020, is a Sunday.

Because Excel treats dates as serial numbers, you can calculate the difference between two dates by subtracting one date from another. However, the result includes weekends and holidays. In many business situations, you need to know the number of workdays between two dates. For example, when calculating the number of days an invoice is past due, it’s often best to exclude weekends and holidays.

To calculate the serial number of the day that is a specified number of working days from a start date, with weekends and holidays excluded, use the WORKDAY function:

WORKDAY(start_date, days[, holidays])

To calculate the number of workdays between two dates, use the NETWORKDAYS function (read the name as “net workdays”):

NETWORKDAYS(start_date, end_date[, holidays])

The following table describes the WORKDAYS and NETWORKDAYS function arguments.

A time consists of three components: the hour, minute, and second. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper time out of them. You can do that by using the TIME function:

TIME(hour, minute, second)

The following table describes the TIME function arguments.

For example, TIME(14, 45, 30) returns the time 2:45:30 PM. Like the DATE function, TIME adjusts for wrong hour, minute, and second values. For example, TIME(14, 60, 30) returns 3:00:30 PM. Here, TIME takes the extra minute and adds 1 to the hour value.

The three components of a time—hour, minute, and second—can also be extracted individually from a specified time, by using the HOUR, MINUTE, and SECOND functions:

HOUR(serial_number)

MINUTE(serial_number)

SECOND(serial_number)

In each case, the serial_number argument is the time (or a string representation of the time) you want to work with. The HOUR function returns a number between 0 and 23 that corresponds to the hour component of a specified time. For example, HOUR(0.5) returns 12.

The MINUTE function returns a number between 0 and 59 that corresponds to the minute component of a specified time. For example, if it’s currently 3:15 PM, MINUTE(NOW) returns 15.

The SECOND function returns a number between 0 and 59 that corresponds to the second component of a specified time. For example, SECOND("2:45:30 PM") returns 30.

To insert a date or time function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Date & Time, click one of the date or time functions, and then enter the arguments, as described earlier in this chapter.

Consolidate data

Many businesses create worksheets for specific tasks and then distribute them to various departments. The most common example is budgeting. Accounting might create a generic “budget” template that each department or division in the company must fill out and return. Similarly, you often see worksheets distributed for inventory requirements, sales forecasting, survey data, experimental results, and more.

Creating these worksheets, distributing them, and filling them in are all straightforward operations. The tricky part comes when the sheets are returned to the originating department, and all the new data must be combined into a summary report showing companywide totals. This task is called consolidating the data, and it’s often complicated and time consuming, especially for large worksheets. Fortunately, Excel has powerful features that can take the drudgery out of consolidation.

Excel can consolidate your data by using the following methods:

• Consolidating by position With this method, Excel consolidates the data from several worksheets, using the same range coordinates on each sheet. You can use this method if the worksheets you’re consolidating have an identical layout.

• Consolidating by category This method tells Excel to consolidate the data by looking for identical row and column labels in each sheet. For example, if one worksheet lists monthly Gizmo sales in row 1 and another lists monthly Gizmo sales in row 5, you can consolidate this information as long as both sheets have a “Gizmo” label at the beginning of these rows.

In both cases, you specify one or more source ranges (the ranges that contain the data you want to consolidate) and a destination range (the range where the consolidated data will appear).

If the sheets you’re working with have the same layout, consolidating by position is the easiest way to go. For example, consider three workbooks—Division I Budget, Division II Budget, and Division III Budget. Each sheet uses the same row and column labels, so they’re ideal candidates for consolidation by position.

During the consolidation procedure, Excel defaults to creating links to the source data, which means it updates the consolidated data when the source data changes. To make this happen, Excel does three things:

• It adds link formulas to the destination range for each cell in the source ranges you selected.

• It consolidates the data by adding SUM functions (or whichever operation you select) that total the results of the link formulas.

• It outlines the consolidation worksheet and hides the link formulas.

If you want to consolidate data from worksheets that don’t use the same layout, you need to tell Excel to consolidate the data by category. In this case, Excel examines each of your source ranges and consolidates data that uses the same row or column labels.

To consolidate data by position

1. Create a new worksheet that has the same layout as the sheets you’re consolidating.

2. In this new consolidation worksheet, click the upper-left corner of the destination range.

3. On the Data tab, in the Data Tools group, click Consolidate to open the Consolidate dialog box.

4. In the Function list, click the operation to use during the consolidation. You’ll use Sum most of the time, but Excel has 10 other operations to choose from, including Count, Average, Max, and Min.

5. For each source range—not including any row and/or column labels—enter the range coordinates in the Reference box, then click Add. If the source data is open in another worksheet or workbook, you can click the range box and then select the data.

6. If you want the consolidated data to change whenever you make changes to the source data, make sure that the Create links to source data check box is selected.

7. Click OK. Excel gathers the data, consolidates it, and then adds it to the destination range.

To consolidate data by category

1. Create or select a new worksheet and click the upper-left corner of the destination range.

   Important

   When consolidating by category, it isn’t necessary to enter labels for the consolidated data because Excel does it for you automatically. If you want to see the labels in a particular order, it’s okay to enter them yourself. However, if you do enter the labels yourself, make sure you spell the labels exactly as they’re spelled in the source worksheets.

2. On the Data tab, in the Data Tools group, click Consolidate to open the Consolidate dialog box.

3. In the Function list, click the operation to use during the consolidation.

4. For each source range—including any row and/or column labels—enter the range coordinates in the Reference box, then click Add. If the source data is open in another worksheet or workbook, you can click the range box and then select the data.

5. If you want Excel to use the data labels in the top row of the selected ranges, under Use labels in, select the Top row check box. If you want Excel to use the data labels in the left column of the source ranges, select the Left column check box.

6. If you want the consolidated data to change whenever you make changes to the source data, make sure that the Create links to source data check box is selected.

7. Click OK. Excel gathers the data, consolidates it, and then adds it to the destination range.

Perform what-if analysis by using Goal Seek and Scenario Manager

What-if analysis is perhaps the most basic method for interrogating your worksheet data. With what-if analysis, you first calculate a formula, D, based on the input from variables A, B, and C. You then say, “What if I change variable A? Or B or C? What happens to the result?”

Perform what-if analysis by using Goal Seek

Here’s a what-if question for you: What if you already know the result you want? For example, you might know that you want to have $50,000 saved to purchase new equipment five years from now, or that you have to achieve a 30 percent gross margin in your next budget. If you need to manipulate only a single variable to achieve these results, you can use the Goal Seek feature in Excel. You tell Goal Seek the final value you need and which variable to change, and it finds a solution for you (if one exists).

When you set up a worksheet to use Goal Seek, you usually have a formula in one cell and the formula’s variable—with an initial value—in another. (Your formula can have multiple variables, but with Goal Seek you can manipulate only one variable at a time.) Goal Seek operates by using an iterative method to find a solution. That is, Goal Seek first tries the variable’s initial value to see whether that produces the result you want. If it doesn’t, Goal Seek tries different values until it converges on a solution.

Before you run Goal Seek, you need to set up your worksheet in a particular way. This means doing three things:

• Set up one cell as the changing cell. This is the value that Goal Seek will iteratively manipulate to attempt to reach the goal. Enter an initial value (such as 0) into the cell.

• Set up the other input values for the formula and make them proper initial values.

• Create a formula for Goal Seek to use to try to reach the goal.

For example, suppose you’re a small-business owner looking to purchase new equipment worth $50,000 five years from now. Assuming your investments earn 5 percent annual interest, how much do you need to set aside every year to reach this goal? You can set up a worksheet like this to use Goal Seek:

• Make cell C5 the changing cell: the annual deposit into the fund (with an initial value of 0).

• In cells C3 and C4, enter the constants for the FV function.

• Make cell C7 the cell that contains the FV function that calculates the future value of the equipment fund. When Goal Seek is done, this cell’s value should be $50,000.

  

To perform what-if analysis by using Goal Seek

1. On the Data tab, in the Forecast group, click What-If Analysis, then click Goal Seek to open the Goal Seek dialog box.

2. In the Set cell box, enter a reference to the cell that contains the formula you want Goal Seek to manipulate.

3. In the To value box, enter the final value you want for the goal cell.

4. In the By changing cell box, enter a reference to the changing cell.

   

5. Click OK. Excel begins the iteration and displays the Goal Seek Status dialog box. When Excel finishes, the dialog box tells you whether Goal Seek found a solution.

6. If Goal Seek found a solution, you can accept the solution by clicking OK. To ignore the solution, click Cancel.

   

Perform what-if analysis by using Scenario Manager

By definition, what-if analysis is not an exact science. All what-if models make guesses and assumptions based on history, expected events, and other factors. A particular set of guesses and assumptions that you plug into a model is called a scenario. Because most what-if worksheets can take a wide range of input values, you usually end up with a large number of scenarios to examine. Instead of going through the tedious chore of inserting all these values into the appropriate cells, Excel has a Scenario Manager feature that can handle the process for you.

As an example, imagine a worksheet model that analyzes a mortgage. You’d use such a model to decide how much of a down payment to make, how long the term should be, and whether to include an extra principal paydown every month.

Here are some possible questions to ask this model:

• How much will I save over the term of the mortgage if I use a shorter term, make a larger down payment, and include a monthly paydown?

• How much more will I end up paying if I extend the term, reduce the down payment, and forgo the paydown?

These are examples of scenarios that you would plug into the appropriate cells in the model. Scenario Manager helps by letting you define a scenario separately from the worksheet. You can save specific values for any or all of the model’s input cells, give the scenario a name, and then recall the name (and all the input values it contains) from a list.

Before creating a scenario, you need to decide which cells in your model will be the input cells. These will be the worksheet variables—the cells that, when you change them, change the results of the model. Excel calls these the changing cells. You can have as many as 32 changing cells in a scenario. For best results, follow these guidelines when setting up your worksheet for scenarios:

• The changing cells should be constants. Formulas can be affected by other cells, and that can throw off the entire scenario.

• To make it easier to set up each scenario, and to make your worksheet easier to understand, group the changing cells and label them.

• For even greater clarity, assign a name to each changing cell.

To add a scenario

1. On the Data tab, in the Forecast group, click What-If Analysis, then click Scenario Manager to open the Scenario Manager dialog box.

2. Click Add to open the Add Scenario dialog box.

3. In the Scenario name box, enter a name for the scenario.

4. In the Changing cells box, enter references to your worksheet’s changing cells. You can type in the references (be sure to separate noncontiguous cells with commas) or select the cells directly on the worksheet.

5. In the Comment box, enter a description for the scenario. This description appears in the Comment section of the Scenario Manager dialog box.

6. Click OK. Excel opens the Scenario Values dialog box.

7. In the Scenario Values dialog box, enter values for the changing cells.

8. To add more scenarios, click Add to return to the Add Scenario dialog box and repeat steps 3 through 7. Otherwise, click OK to return to the Scenario Manager dialog box.

9. Click Close to save the scenario and return to the worksheet.

To display a scenario

1. On the Data tab, in the Forecast group, click What-If Analysis, then click Scenario Manager to open the Scenario Manager dialog box.

2. In the Scenarios list, click the scenario you want to display.

3. Click Show. Excel enters the scenario values in the changing cells.

4. Repeat steps 2 and 3 to display other scenarios.

5. Click Close to return to the worksheet.

Calculate data by using financial functions

Excel is loaded with financial features that give you powerful tools for building models that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment on a loan, the future value of an annuity, the internal rate of return of an investment, or the yearly depreciation of an asset.

Most of the formulas you’ll work with involve three factors—the present value (the amount something is worth now); the future value (the amount something will be worth in the future); and the interest rate (or the discount rate)—plus two related factors: the periods, the number of payments or deposits over the term of the loan or investment, and the payment, the amount of money paid out or invested in each period.

Although Excel has dozens of financial functions that use many different arguments, the following table describes the arguments you’ll use most frequently.

When building financial formulas, you need to ask yourself the following questions:

• Who or what is the subject of the formula? On a mortgage analysis, for example, are you performing the analysis on behalf of yourself or the bank?

• Which way is the money flowing with respect to the subject? For the present value, future value, and payment, enter money that the subject receives as a positive quantity, and enter money that the subject pays out as a negative quantity. For example, if you’re the subject of a mortgage analysis, the loan principal (the present value) is a positive number because it’s money that you receive from the bank; the payment and the remaining principal (the future value) are negative because they’re amounts that you pay to the bank.

• What is the time unit? The underlying unit of both the interest rate and the period must be the same. For example, if you’re working with the annual interest rate, you must express the period in years. Similarly, if you’re working with monthly periods, you must use a monthly interest rate.

• When are the payments made? Excel differentiates between payments made at the end of each period and those made at the beginning.

The following table shows the most commonly used financial functions.

To insert a financial function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Financial, click the financial function you want to use, and then enter the arguments, as described earlier in this chapter.

Calculate data by using the NPER function

In some loan scenarios, you need to borrow a certain amount at the current interest rates, but you can spend only so much on each payment. If the other loan factors are fixed, the only way to adjust the payment is to adjust the term of the loan: a longer term means smaller payments, and a shorter term means larger payments.

You could figure out the term by adjusting the nper argument of the PMT function until you get the payment you want. However, Excel offers a more direct solution in the form of the NPER function, which returns the number of periods of a loan:

NPER(rate, pmt, pv[, fv][, type])

Here, rate is the fixed rate of interest over the term of the loan; pmt is the periodic payment (a negative value, since this is money you are paying out); pv is the loan principal; fv is the optional future value of the loan (if omitted, the default is 0); and type is the optional type of payment (use 0, the default, for end-of-period payments or use 1 for beginning-of-period payments).

You can also use NPER for investments. Given an investment goal, if you have an initial deposit and an amount that you can afford to deposit periodically, how long will it take to reach your goal at the prevailing market interest rate? You answer this question by using the NPER from the point of view of an investment:

NPER(rate, pmt, pv[, fv][, type])

Here, rate is the fixed rate of interest over the term of the investment; pmt is the amount invested each period payment; pv is the initial investment; fv is the optional future value of the investment (if omitted, the default is 0); and type is the optional type of deposit (use 0, the default, for end-of-period deposits or use 1 for beginning-of-period deposits).

To insert the NPER function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Financial, click NPER, and then enter the arguments, as described in this section.

Calculate data by using the PMT function

When negotiating a loan to purchase equipment or a mortgage for your house, the first concern that comes up is almost always the size of the payment you’ll need to make each period. This is basic cash-flow management because the monthly (or whatever) payment must fit within your budget.

To return the periodic payment for a loan, use the PMT function:

PMT(rate, nper, pv[, fv][, type])

Here, rate is the fixed rate of interest over the term of the loan; nper is the number of payments over the term of the loan; pv is the loan principal; fv is the optional future value of the loan (if omitted, the default is 0); and type is the optional type of payment (use 0, the default, for end-of-period payments or use 1 for beginning-of-period payments).

You can also use PMT with investment formulas. Suppose that you want to reach a future investment value goal by a certain date and that you have an initial amount to invest. Given current interest rates, how much extra do you have to periodically deposit into the investment to achieve your goal? Use the PMT function from the point of view of an investment:

PMT(rate, nper, pv[, fv][, type])

Here, rate is the fixed rate of interest over the term of the investment; nper is the number of deposits over the term of the investment; pv is the initial investment; fv is the optional future value of the investment (if omitted, the default is 0); and type is the optional type of deposit (use 0, the default, for end-of-period deposits or use 1 for beginning-of-period deposits).

To insert the PMT function into a formula

• ➜   On the Formulas tab, in the Function Library group, click Financial, click PMT, and then enter the arguments, as described in this section.

Trace precedence and dependence

Some formula errors result from referencing other cells that contain errors or inappropriate values. The first step in troubleshooting these kinds of formula problems is to determine which cell (or group of cells) is causing an error. This is straightforward if the formula references only a single cell, but it gets progressively more difficult as the number of references increases. (Another complicating factor is the use of range names, because it won’t be obvious which range each name is referencing.)

To determine which cells are wreaking havoc on your formulas, you can use Excel auditing features to visualize and trace a formula’s input values and error sources. The formula-auditing features operate by creating tracers—arrows that literally point out the cells involved in a formula. You can use tracers to find two kinds of cells:

• Precedents These are cells that are directly or indirectly referenced in a formula. For example, suppose that cell E4 contains the formula =E2; E2 is a direct precedent of E4. Now suppose that cell E2 contains the formula =D2/2; this makes D2 a direct precedent of E2 and also an indirect precedent of cell E4.

• Dependents These are cells that are directly or indirectly referenced by a formula in another cell. In the preceding example, cell E2 is a direct dependent of D2, and E4 is an indirect dependent of D2.

  

To trace cell precedents

1. Select the cell that contains the formula whose precedents you want to trace.

2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents. Excel adds a tracer arrow to each direct precedent.

3. Repeat step 2 to trace more levels of precedents.

   Tip

   You also can trace precedents by double-clicking the cell if you have turned off in-cell editing. You do this by clearing the Allow Editing Directly In Cells check box on the Advanced page of the Excel Options dialog box.

To trace cell dependents

1. Select the cell whose dependents you want to trace.

2. On the Formulas tab, in the Formula Auditing group, click Trace Dependents. Excel adds a tracer arrow to each direct dependent.

3. Repeat step 2 to trace more levels of dependents.

Monitor cells and formulas by using the Watch Window

It is very common for a formula to refer to a precedent that resides in a different worksheet. That’s a powerful technique, but it does mean that you might not be able to see the formula cell and the precedent cell at the same time. This could also happen if the precedent exists in another workbook or even elsewhere on the same sheet if you’re working with a large model.

This is a problem because there’s no easy way to determine the current value or formula of the unseen precedent. If you’re having a problem, troubleshooting requires that you track down the far-off precedent to see if it might be the culprit. That’s bad enough with a single unseen cell, but what if your formula refers to 5 or 10 such cells? And what if those cells are scattered in different worksheets and workbooks?

This level of hassle—not at all uncommon in the spreadsheet world—was no doubt the inspiration behind an elegant solution: the Watch Window. You use this window to keep track of both the value and the formula in any cell in any worksheet in any open workbook.

To add a watch

1. Go to the workbook or worksheet that contains the cell or cells you want to watch.

2. On the Formulas tab, in the Formula Auditing group, click Watch Window to open the Watch Window.

3. Click Add Watch to open the Add Watch dialog box.

4. Do either of the following:

   • In the worksheet, select the cell or cell range you want to watch.

   • In the Select the cells that you would like to watch the value of box, enter a reference formula for the cell or cell range (for example, =A1).

5. Click Add to add the cell or cells to the Watch Window.

To remove a watch

1. On the Formulas tab, in the Formula Auditing group, click Watch Window to open the Watch Window.

2. Click the watch you want to remove.

3. Click Delete Watch. Excel removes the watch.

Validate formulas by using error-checking rules

If you use Microsoft Word, you’re probably familiar with the blue lines that appear under words and phrases that the grammar checker has flagged as being incorrect. The grammar checker operates by using a set of rules that determine correct grammar and syntax. As you type, the grammar checker operates in the background, constantly monitoring your writing. If something you write goes against one of the grammar checker’s rules, the wavy line appears to let you know there’s a problem.

Excel has a similar feature: the formula error checker. Like the grammar checker, the formula error checker uses a set of rules to determine correctness, and it operates in the background to monitor your formulas. If it detects something amiss, it displays an error indicator—a green triangle—in the upper-left corner of the cell containing the formula. When you select the cell with the formula error, Excel displays an icon beside the cell. If you point to the icon, a ScreenTip describes the error.

Clicking the icon displays the following options:

• Corrective actions One or more commands (depending on the type of error) that Excel suggests for either fixing the problem or helping you troubleshoot the error.

• Help on this error Click this option to get information on the error from the Excel Help system.

• Ignore Error Click this option to leave the formula as is.

• Edit in Formula Bar Click this option to display the formula in Edit mode in the formula bar. You can then fix the problem by editing the formula.

• Error Checking Options Click this option to display the Formulas page of the Excel Options dialog box (discussed next).

To run an error check on the current worksheet

• ➜   On the Formulas tab in the Formula Auditing group, click the Error Checking menu, then click Error Checking.

Like the Word grammar checker, the Excel formula error checker has several rules that control which errors it flags. You can configure these rules on the Formulas page of the Excel Options dialog box. The following list describes each rule, including the errors each rule flags and some of the corrective actions that Excel makes available on the error-checking menu that appears when you click the error icon next to a flagged cell:

• Cells containing formulas that result in an error Flags formulas that evaluate to an error value such as #DIV/0! or #NAME?.

• Inconsistent calculated column formula in tables In calculated columns, flags any cell that contains a formula with a different structure than the other cells in the column. The error-checking menu for this error includes the Restore To Calculated Column Formula command, which you can use to update the formula so that it’s consistent with the rest of the column.

• Cells containing years represented as 2 digits Flags formulas that contain date text strings in which the year contains only two digits (a possibly ambiguous situation because the string could refer to a date in either the 1900s or the 2000s). The error-checking menu includes two commands—Convert XX To 19XX and Convert XX To 20XX—that you can use to convert the two-digit year to a four-digit year.

• Numbers formatted as text or preceded by an apostrophe Flags cells that contain a number that is either formatted as text or preceded by an apostrophe. The error-checking menu includes the Convert To Number command to convert the text to its numeric equivalent.

• Formulas inconsistent with other formulas in the region Flags formulas that are structured differently from similar formulas in the surrounding area. The error-checking menu includes commands such as Copy Formula From Left to bring the formula into consistency with the surrounding cells.

• Formulas which omit cells in a region Flags formulas that omit cells that are adjacent to a range referenced in the formula. For example, suppose that the formula is =AVERAGE(C4:C21), where C4:C21 is a range of numeric values. If cell C3 also contains a numeric value, the formula error checker flags the formula to alert you to the possibility that you missed including cell C3 in the formula.

• Unlocked cells containing formulas Flags formulas that reside in unlocked cells. This isn’t an error so much as a warning that other people could tamper with the formula even after you have protected the sheet. The error-checking menu includes the command Lock Cell to lock the cell and prevent users from changing the formula after you protect the sheet.

• Formulas referring to empty cells Flags formulas that reference empty cells. The error-checking menu includes the Trace Empty Cell command to help you find the empty cell. (At this point, you can either enter data in the cell or adjust the formula so that it doesn’t reference the cell.)

• Data entered in a table is invalid Flags cells that violate a table’s data-validation rules. This can happen if you set up a data-validation rule with only a Warning or Information style, in which case the user can still opt to enter the invalid data. The formula error checker flags the cells that contain invalid data. The error-checking menu includes the Display Type Information command, which shows the data-validation rule that the cell data violates.

• Misleading number formats Flags cells that link to another cell but that have a number format that is not only different from the linked cell’s number format but that is also potentially misleading. You see this error, for example, if cell B3 is formatted as Currency, cell E4 links to B3 with the formula =B3, and E4 is formatted as Short Date. (For comparison, note that you would not see an error if cell E4 was formatted as Percentage.)

To configure the error-checking rules

• ➜   On the Formulas page of the Excel Options dialog box, in the Error checking rules section, select or clear the check boxes to choose the rules you want to apply. Then click OK.

  Tip

  You can display the Formulas page directly by clicking a cell containing an error, clicking the error checker icon, and then clicking Error Checking Options.

Evaluate formulas

If a formula produces no warnings or error values, the result might still be in error. If the result of a formula is incorrect, one method that can help you understand and fix the problem is to calculate multipart formulas one term at a time. In the formula bar, select the expression you want to calculate, then press F9. Excel converts the expression into its value. Make sure that you press the Esc key when you’re done to avoid entering the formula with just the calculated values.

This is a useful technique, but it can be tedious in a long or complex formula, and there’s always a danger that you might accidentally confirm a partially evaluated formula and lose your work. For complex formulas, a better solution is to use the Evaluate Formula feature in Excel. It does the same thing as the F9 technique, but it’s easier and safer.

To evaluate a formula

1. Select the cell that contains the formula you want to evaluate.

2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula to open the Evaluate Formula dialog box.

3. The current term in the formula is underlined in the Evaluation box. At each step, select from one or more of the following buttons:

   • Evaluate Click this button to display the current value of the underlined term.

   • Step In Click this button to display the first dependent of the underlined term. If that dependent also has a dependent, click this button again to see it.

   • Step Out Click this button to hide a dependent and evaluate its precedent.

4. Repeat step 3 until you’ve completed your evaluation, then click Close.