Microsoft Excel Step by Step (Office 2021 and Microsoft 365).pdf

Perform calculations on data 3 Excel workbooks provide an easy interface for storing In this chapter and organizing data, but Excel can do so much more than that. U...

Perform calculations on data 3 Excel workbooks provide an easy interface for storing In this chapter and organizing data, but Excel can do so much more than that. Using the built-in functions, you can easily Name data ranges perform a variety of calculations—from simple tasks Create formulas to calculate values such as calculating totals to complex financial calcula- tions. Excel can report information such as the current Summarize data that meets date and time, the maximum value or number of blank specific conditions cells in a data set, and the cells that meet specific condi- Copy and move formulas tions, and it can use this information when performing Create array formulas calculations. To simplify the process of referencing cells or data ranges in your calculations, you can name them. Find and correct errors in Excel provides guidance for creating formulas to perform calculations calculations and for identifying and fixing any errors in Configure automatic and iterative the calculations. calculation options This chapter guides you through procedures related to naming data ranges, creating formulas to calculate values, summarizing data in one or more cells, copy- ing and moving formulas, creating array formulas, troubleshooting issues with formula calculations, and configuring automatic and iterative calculation options. 71 Chapter 3: Perform calculations on data Name data ranges When you work with large amounts of data, it’s often useful to identify groups of cells that contain related data. For example, you might have a worksheet for a delivery service in which: Each column of data summarizes the number of packages handled during one hour of the day. Each row of data represents a region that handled packages. Worksheets often contain logical groups of data Instead of specifying a cell or range of cells individually every time you want to refer- ence the data they contain, you can name the cell or cells—in other words, create a named range. For example, you could group the packages handled in the Northeast region during all time periods into a range named Northeast. Whenever you want to use the contents of that range in a calculation, you can reference Northeast instead of $C$3:$I$3. That way, you don’t need to remember the cell range or even the work- sheet it’s on. Select a group of cells to create a named range TIP Range names can be simple or complex. In a workbook that contains different kinds of data, a more descriptive name such as NortheastVolume can help you remem- ber the data the range includes. 72  Name data ranges If you have a range of data with consistent row or column headings, you can create a series of ranges at one time instead of having to create each individually. By default, when you create a named range, its scope is the entire workbook. This means that you can reference the name in a formula on any worksheet in the work- 3 book. If a workbook contains a series of worksheets with the same content—for example, sales data worksheets for each month of a year—you might want to set the scope of ranges on those worksheets to the worksheet instead of to the workbook. After you create a named range, you can edit the name, the cells the range includes, or the scope in which the range exists, or delete a range you no longer need, in the Name Manager. Manage named ranges in the Name Manager TIP If your workbook contains a lot of named ranges, tables, or other objects, you can filter the Name Manager list to locate objects more easily. To create a named range 1. Select the cells you want to include in the named range. 2. In the Name Box, next to the formula bar, enter a name for your named range. Or 1. Select the cells you want to include in the named range. 2. On the Formulas tab, in the Defined Names group, select Define Name. 73 Chapter 3: Perform calculations on data 3. In the New Name dialog, do the following: a. In the Name box, enter a name for the range. The name must begin with a letter or underscore and may not contain spaces. b. If you want to restrict the range to use on a specific worksheet, select that worksheet in the Scope list. c. If you want to provide additional information to help workbook users identify the range, enter a description of up to 255 characters in the Comment box. d. Verify that the Refers to box includes the cells you want to include in the range. e. Select OK. To create a series of named ranges from data with headings 1. Select the cells that contain the headings and data you want to include in the named ranges. 2. On the Formulas tab, in the Defined Names group, select Create from Selection. 3. In the Create Names from Selection dialog, select the checkbox next to the location of the heading text from which you want to create the range names. Name ranges by any outer row or column in the selection 4. Select OK. To open the Name Manager On the Formulas tab, in the Defined Names group, select Name Manager. 74  Name data ranges To change the name of a named range 1. Open the Name Manager. 2. Select the range you want to rename, and then select Edit. 3. In the Edit Name dialog, in the Name box, change the range name, and then select OK. 3 To change the cells in a named range 1. Open the Name Manager. 2. Select the range you want to edit, and then do either of the following: In the Refers to box, change the cell range. Select Edit. In the Edit Name dialog, in the Refers to box, change the cell range, and then select OK. To change the scope of a named range 1. Select the range you want to change the scope of and note the range name shown in the Name Box. 2. On the Formulas tab, in the Defined Names group, select Define Name. 3. In the New Name dialog, do the following: a. In the Name box, enter the existing range name that you noted in step 1. b. In the Scope list, select the new scope. c. If you want to provide additional information to help workbook users iden- tify the range, enter a description of up to 255 characters in the Comment box. d. Verify that the Refers to box includes the cells you want to include in the range. e. Select OK. To delete a named range 1. Open the Name Manager. 2. Select the range you want to delete, and then select Delete. 3. In the Microsoft Excel dialog prompting you to confirm the deletion, select OK. 75 Chapter 3: Perform calculations on data Create formulas to calculate values After you enter data on a worksheet and, optionally, define ranges to simplify data references, you can create formulas to performs calculations on your data. For exam- ple, you can calculate the total cost of a customer’s shipments, figure the average number of packages for all Wednesdays in the month of January, or find the highest and lowest daily package volumes for a week, month, or year. You can enter a formula directly into a cell or into the formula bar located between the ribbon and the worksheet area. Every formula begins with an equal sign (=), which tells Excel to interpret the expres- sion after the equal sign as a calculation instead of as text. The formula that you enter after the equal sign can include simple references and mathematical operators, or it can begin with an Excel function. For example, you can find the sum of the numbers in cells C2 and C3 by using the formula =C2+C3. You can edit formulas by selecting the cell and then editing the formula in the cell or in the formula bar. Operators and precedence When you create an Excel formula, you use the built-in functions and arith- metic operators that define operations such as addition and multiplication. The following table displays the order in which Excel evaluates mathematical operations. Precedence Operator Description 1 - Negation 2 % Percentage 3 ^ Exponentiation 4 * and / Multiplication and division 5 + and – Addition and subtraction 6 & Concatenation 76  Create formulas to calculate values If two operators at the same level, such as + and –, occur in the same equa- tion, Excel evaluates them from left to right. For example, Excel evaluates the operations in the formula = 4 + 8 * 3 – 6 in this order: 3 1. 8 * 3 = 24 2. 4 + 24, with a result of 28 3. 28 – 6, with a final result of 22 You can control the order in which Excel evaluates operations by using paren- theses. Excel always evaluates operations in parentheses first. For example, if the previous equation were rewritten as = (4 + 8) * 3 – 6, Excel would evaluate the operations in this order: 1. (4 + 8), with a result of 12 2. 12 * 3, with a result of 36 3. 36 – 6, with a final result of 30 In a formula that has multiple levels of parentheses, Excel evaluates the expressions within the innermost set of parentheses first and works its way out. As with operations on the same level, expressions at the same parenthet- ical level are evaluated from left to right. For example, Excel evaluates the formula = 4 + (3 + 8 * (2 + 5)) – 7 in this order: 1. (2 + 5), with a result of 7 2. 7 * 8, with a result of 56 3. 56 + 3, with a result of 59 4. 4 + 59, with a result of 63 5. 63 – 7, with a final result of 56 77 Chapter 3: Perform calculations on data You can perform mathematical operations on numbers by using the mathematical operators for addition (+), subtraction (–), multiplication (*), division (/), negation (-), and exponentiation (^). You can perform other operations on a range of numbers by using the following Excel functions: SUM Returns the sum of the numbers. AVERAGE Returns the average of the numbers. COUNT Returns the number of entries in the cell range. MAX Returns the largest number. MIN Returns the smallest number. These functions are available from the AutoSum list, which is in the Editing group on the Home tab of the ribbon and in the Function Library group on the Formulas tab. The Function Library is also where you’ll find the rest of the Excel functions, organized into categories. Excel includes a wide variety of functions The Formula AutoComplete feature simplifies the process of referencing functions, named ranges, and tables in formulas. It provides a template for you to follow and suggests entries for each function argument. Here’s how it works: 1. As you begin to enter a function name after the equal sign, Excel displays a list of functions matching the characters you’ve entered. You can select a function from the list and then press Tab to enter the function name and the opening parenthesis in the cell or formula bar. 78  Create formulas to calculate values 3 Select a function from the list 2. After the opening parenthesis, Excel displays the arguments that the selected function accepts. Bold indicates required arguments and square brackets enclose optional arguments. You can simply follow the prompts to enter or select the necessary information, and then enter a closing parenthesis to finish the formula. Excel prompts you for required and optional information 3. To reference a named range, table, or table element, start entering the name (or an opening square bracket to indicate a table element) and Excel displays a list of options to choose from. TIP You can reference a series of contiguous cells in a formula by entering the cell range or by dragging through the cells. If the cells aren’t contiguous, hold down the Ctrl key and select each cell. 79 Chapter 3: Perform calculations on data Excel displays the available table elements SEE ALSO For information about using keyboard shortcuts to select cell ranges, see the appendix, “Keyboard shortcuts.” If you’re creating a more complex formula and want extra guidance, you can assemble the formula in the Insert Function dialog. All the Excel functions are available from within the dialog. Create formulas in the Insert Function dialog If you’re uncertain which function to use, you can search for one by entering a simple description of what you’d like to accomplish. Selecting any function displays the func- tion’s arguments and description. 80  Create formulas to calculate values 3 Activate any field to display a description of the argument After you select a function, Excel displays an interface in which you can enter all the function arguments. The complexity of the interface depends on the function. Whether you enter a formula directly or assemble it in the Insert Function dialog, you can reference data in cells (A3) or cell ranges (A3:J12), in named ranges (Northeast), or in table columns (TableName[ColumnName]). For example, if the Northeast range refers to cells C3:I3, you can calculate the average of cells C3:I3 by using the formula =AVERAGE(Northeast). To create a formula manually 1. Select the cell in which you want to create the formula. 2. In the cell or in the formula bar, enter an equal sign (=). 3. If the formula will call a function, enter the function name and an open- ing parenthesis to begin the formula and display the required and optional arguments. 4. Enter the remainder of the formula: Reference cells by entering the cell reference or selecting the cell. Reference cell ranges by entering the cell range or dragging across the range. Reference named ranges and tables by entering the range or table name. Reference table elements by entering [ after the table name, selecting the element from the list, and then entering ]. 81 Chapter 3: Perform calculations on data 5. If the formula includes a function, enter the closing parenthesis to end it. 6. Press Enter to enter the formula in the cell and return the results. To open the Insert Function dialog On the formula bar, to the left of the text entry box, select the Insert Function button (fx). On the Formulas tab, in the Function Library group, select Insert Function. Press Shift+F3. To locate a function in the Insert Function dialog In the Search for a function box, enter a brief description of the operation you want to perform, and then select Go. Or 1. In the Or select a category list, select the function category. 2. Scroll down the Select a function list to the function. To create a formula in the Insert Function dialog 1. Open the Insert Function dialog. 2. Select the function you want to use in the formula, and then select OK. 3. In the Function Arguments dialog, enter the function’s arguments, and then select OK. To reference a named range in a formula Enter the range name in place of the cell range. To reference an Excel table column in a formula Enter the table name followed by an opening bracket ([), the column name, and a closing bracket (]). 82  Summarize data that meets specific conditions Summarize data that meets specific conditions Another use for formulas is to display messages when certain conditions are met. This kind of formula is called a conditional formula. One way to create a conditional formula in Excel is to use the IF function. Selecting the Insert Function button next to 3 the formula bar and then choosing the IF function displays the Function Arguments dialog with the fields required to create an IF formula. The Function Arguments dialog for an IF formula When you work with an IF function, the Function Arguments dialog displays three input boxes: Logical_test The condition you want to check. Value_if_true The value to display if the condition is met. This could be a cell reference, or a number or text enclosed in quotes. Value_if_false The value to display if the condition is not met. 83 Chapter 3: Perform calculations on data The following table displays other conditional functions you can use to summarize data. Function Description AVERAGEIF Finds the average of values within a cell range that meet a specified criterion AVERAGEIFS Finds the average of values within a cell range that meet multiple criteria COUNT Counts the cells in a range that contain numerical values COUNTA Counts the cells in a range that are not empty COUNTBLANK Counts the cells in a range that are empty COUNTIF Counts the cells in a range that meet a specified criterion COUNTIFS Counts the cells in a range that meet multiple criteria IFERROR Displays one value if a formula results in an error and another if it doesn’t SUMIF Adds the values in a range that meet a single criterion SUMIFS Adds the values in a range that meet multiple criteria To create a formula that uses the AVERAGEIF function, you define the range to be examined for the criterion, the criterion, and, if required, the range from which to draw the values. As an example, consider a worksheet that lists each customer’s ID number, name, state, and total monthly shipping bill. If you want to find the average order of customers from the state of Washington (abbreviated in the worksheet as WA), you can create the formula =AVERAGEIF(C3:C6, “WA”, D3:D6). Sample data that illustrates the preceding example The AVERAGEIFS, SUMIFS, and COUNTIFS functions extend the capabilities of the AVERAGEIF, SUMIF, and COUNTIF functions to allow for multiple criteria. For example, if you want to find the sum of all orders of at least $100,000 placed by companies in Washington, you can create the formula =SUMIFS(D2:D5, C2:C5, “=WA”, D2:D5, “>=100000”). 84  Summarize data that meets specific conditions The AVERAGEIFS and SUMIFS functions start with a data range that contains values that the formula summarizes. You then list the data ranges and the criteria to apply to that range. In generic terms, the syntax is =AVERAGEIFS(data_range, criteria_range1, criteria1[,criteria_range2, criteria2...]). The part of the syntax in brackets (which aren’t used when you create the formula) is optional, so an AVERAGEIFS or SUMIFS formula that contains a single criterion will work. The COUNTIFS function, which doesn’t per- form any calculations, doesn’t need a data range; you just provide the criteria ranges 3 and criteria. For example, you could find the number of customers from Washington who were billed at least $100,000 by using the formula =COUNTIFS(C2:C5, “=WA”, D2:D5, “>=100000”). You can use the IFERROR function to display a custom error message instead of relying on the default Excel error messages to explain what happened. For example, you could create this type of formula to employ the VLOOKUP function to look up a customer’s name in the second column of a table named Customers based on the customer identification number entered into cell G8. That formula might look like this: =IFERROR(VLOOKUP(G8,Customers,2,FALSE),”Customer not found”). If the function finds a match for the customer ID in cell G8, it displays the customer’s name; if not, it displays the text “Customer not found.” TIP The last two arguments in the VLOOKUP function tell the formula to look in the Customers table’s second column and to require an exact match. For more information about the VLOOKUP function, see “Look up data from other locations” in Chapter 7, “Combine data from multiple sources.” To summarize data by using the IF function Use the syntax =IF(logical_test, value_if_true, value_if_false) where: logical_test is the logical test to be performed. value_if_true is the value the formula returns if the test is true. value_if_false is the value the formula returns if the test is false. To count cells that contain numbers in a range Use the syntax =COUNT(range), where range is the cell range in which you want to count cells. 85 Chapter 3: Perform calculations on data To count cells that are non-blank Use the syntax =COUNTA(range), where range is the cell range in which you want to count cells. To count cells that contain a blank value Use the syntax =COUNTBLANK(range), where range is the cell range in which you want to count cells. To count cells that meet one condition Use the syntax =COUNTIF(range, criteria) where: range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to count the cell. To count cells that meet multiple conditions Use the syntax =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,…) where for each criteria_range and criteria pair: criteria_range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to count the cell. To find the sum of data that meets one condition Use the syntax =SUMIF(range, criteria, sum_range) where: range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to include the cell. sum_range is the range that contains the values to be included if the range cell in the same row meets the criterion. To find the sum of data that meets multiple conditions Use the syntax =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,…) where: sum_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria. criteria_range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to include the cell. 86  Copy and move formulas To find the average of data that meets one condition Use the syntax =AVERAGEIF(range, criteria, average_range) where: range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to include the cell. average_range is the range that contains the values to be included if the 3 range cell in the same row meets the criterion. To find the average of data that meets multiple conditions Use the syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2,…) where: average_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria. criteria_range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to include the cell. To display a custom message if a cell contains an error Use the syntax =IFERROR(value, value_if_error) where: value is a cell reference or formula. value_if_error is the value to be displayed if the value argument returns an error. Copy and move formulas After you create a formula, you can copy it and paste it into another cell. When you do, Excel changes the formula to work in the new cells. For instance, suppose you have a worksheet in which cell C7 contains the formula =SUM(C2:C6). If you copy cell C7 and paste the copied formula into cell D7, Excel enters =SUM(D2:D6). Excel knows to change the cells used in the formula because the formula uses a relative reference—a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column—for example, C14. 87 Chapter 3: Perform calculations on data Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled Sale Price and Rate, and you want to calculate a sales representative’s commission by multiplying the two values in a row. To calculate the commission for the first sale, you would enter the formula =A2*B2 in cell C2. Use formulas to calculate values such as commissions Selecting cell C2 and dragging the fill handle down through cell C7 copies the formula from cell C2 into each of the other cells. Because you created the formula by using relative references, Excel updates each cell’s formula to reflect its position relative to the starting cell (in this case, cell C2). The formula in cell C7, for example, is =A7*B7. Copying formulas to other cells to summarize additional data When you enter a formula in a cell of an Excel table column, Excel automatically copies the formula to the rest of the column and updates any relative references in the formula. 88  Copy and move formulas If you want a cell reference to remain constant when you copy a formula to another cell, use an absolute reference by inserting a dollar sign ($) before the column letter and row number or a mixed reference by inserting a dollar sign before either the column letter or row number. TIP In addition to using an absolute reference, another way to ensure that your cell ref- 3 erences don’t change when you copy a formula to another cell is to select the cell that contains the formula, copy the formula’s text in the formula bar, press the Esc key to exit cut- and-copy mode, select the cell where you want to paste the formula, and press Ctrl+V. Excel doesn’t change the cell references when you copy your formula to another cell in this manner. One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference through the four possible types of references: Relative columns and rows (for example, C4) Absolute columns and rows (for example, $C$4) Relative columns and absolute rows (for example, C$4) Absolute columns and relative rows (for example, $C4) To copy a formula without changing its cell references 1. Select the cell that contains the formula you want to copy. 2. In the formula bar, select the formula text. 3. Press Ctrl+C. 4. Select the cell in which you want to paste the formula. 5. Press Ctrl+V. 6. Press Enter. To move a formula without changing its cell references 1. Select the cell that contains the formula you want to copy. 2. Point to the edge of the selected cell until the pointer changes to a black four- headed arrow. 3. Drag the outline to the cell where you want to move the formula. 89 Chapter 3: Perform calculations on data To copy a formula and change its cell references 1. Select the cell that contains the formula you want to copy. 2. Press Ctrl+C. 3. Select the cell in which you want to paste the formula. 4. Press Ctrl+V. To create relative and absolute cell references 1. Enter a cell reference into a formula. 2. Do either of the following: Enter a $ in front of a row or column reference you want to make absolute. Select within the cell reference, and then press F4 to advance through the four possible combinations of relative and absolute row and column references. Create array formulas Most Excel formulas calculate values to be displayed in a single cell. For example, you could add the formulas =B1*B4, =B1*B5, and =B1*B6 to consecutive worksheet cells to calculate shipping insurance costs based on the value of a package’s contents. A worksheet with data to be summarized by an array formula Instead of entering the same formula in multiple cells one cell at a time, you can enter a formula in every cell in the target range at the same time by creating an array for- mula. To calculate package insurance rates by multiplying the values in the cell range B4:B6 by the insurance rate in cell B1, you select the target cells (C4:C6) and enter 90  Create array formulas the formula =B1*B4:B6. Note that you must select a range of the same shape as the values you’re using in the calculation. (For example, if the value range is three columns wide by one row high, the target range must also be three columns wide by one row high.) If you enter the array formula into a range of the wrong shape, Excel displays duplicate results, incomplete results, or error messages, depending on how the target range differs from the value range. 3 When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells. The formula appears within a pair of braces to indicate that it is an array formula. An array formula calculates multiple results IMPORTANT You can’t add braces to a formula to make it an array formula. You must press Ctrl+Shift+Enter to create it. To create an array formula 1. Select the cells in which you want to display the formula results. 2. In the formula bar, enter the array formula. 3. Press Ctrl+Shift+Enter. To edit an array formula 1. Select every cell that contains the array formula. 2. In the formula bar, edit the array formula. 3. Press Ctrl+Shift+Enter to re-enter the formula as an array formula. 91 Chapter 3: Perform calculations on data Find and correct errors in calculations Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it’s possible for errors to creep into your formu- las. With Excel, you can find the source of errors in your formulas by identifying the cells used in a specific calculation and describing any errors that have occurred. The process of examining a worksheet for errors is referred to as auditing. Excel identifies errors in several ways. The first way is to display an error code in the cell that contains the formula generating the error. A warning triangle and pound sign indicate an error When the active cell generates an error, Excel displays an Error button next to it. Pointing to the button displays information about the error, and selecting the button displays a menu of options for handling the error. The following table explains the most common error codes. Error code Description ##### The column isn’t wide enough to display the value. #VALUE! The formula has the wrong type of argument, such as text in a cell where a numerical value is required. #NAME? The formula contains text that Excel doesn’t recognize, such as an unknown named range. #REF! The formula refers to a cell that doesn’t exist, which can happen whenever cells are deleted. #DIV/0! The formula attempts to divide by zero. 92  Find and correct errors in calculations Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. You can identify the source of an error by having Excel trace a cell’s precedents, which are the cells with values used in the active cell’s formula. You can also audit your worksheet by identifying cells with formulas that use a value from a particular cell. Cells that use another cell’s value in their calculations are known as dependents, meaning that they depend on the value in the other cell to derive their own value. They are identified in Excel by tracer arrows. 3 If the cells identified by the tracer arrows aren’t the correct cells, you can hide the arrows and correct the formula. Tracing a cell’s dependents If you prefer to have the elements of a formula error presented as text in a dialog, you can use the Error Checking tool to locate errors one after the other. You can choose to ignore the selected error or move to the next or the previous error. Identify and manage errors from the Error Checking window TIP You can have the Error Checking tool ignore formulas that don’t use every cell in a region (such as a row or column). To do so, on the Formulas tab of the Excel Options dialog, clear the Formulas Which Omit Cells In A Region checkbox. Excel will no longer mark these cells as an error. 93 Chapter 3: Perform calculations on data When you just want to display the results of each step of a formula and don’t need the full power of the Error Checking tool, you can use the Evaluate Formula dialog to move through each element of the formula. The Evaluate Formula dialog is particu- larly useful for examining formulas that don’t produce an error but aren’t generating the result you expect. Step through formulas in the Evaluate Formula window Finally, you can monitor the value in a cell regardless of where you are in your work- book by opening a Watch Window that displays the value in the cell. For example, if one of your formulas uses values from cells in other worksheets or even other work- books, you can set a watch on the cell that contains the formula, and then change the values in the other cells. As you change the precedent values, the formula result changes in the Watch Window. When you’re done watching the formula, you can delete the watch and close the Watch Window. Monitor formula results in the Watch Window 94  Find and correct errors in calculations To display information about a formula error 1. Select the cell that contains the error. 2. Point to the error indicator next to the cell to display information about the error. 3. Select the error indicator to display options for correcting or learning more about the error. 3 To identify the cells that a formula references 1. Select the cell that contains the formula. 2. On the Formulas tab, in the Formula Auditing group, select Trace Precedents. To identify formulas that reference a specific cell 1. Select the cell. 2. In the Formula Auditing group, select Trace Dependents. To remove tracer arrows In the Formula Auditing group, do one of the following: To remove all the arrows, select the Remove Arrows button (not its arrow). To remove only precedent or dependent arrows, select the Remove Arrows arrow, and then select Remove Precedent Arrows or Remove Dependent Arrows. To evaluate a formula one calculation at a time 1. Select the cell that contains the formula you want to evaluate. 2. In the Formula Auditing group, select Evaluate Formula. 3. In the Evaluate Formula dialog, select Evaluate. Excel replaces the underlined calculation with its result. 4. Do either of the following: Select Step In to move forward by one calculation. Select Step Out to move backward by one calculation. 5. When you finish, select Close. 95 Chapter 3: Perform calculations on data To change error display options 1. Display the Formulas page of the Excel Options dialog. 2. In the Error Checking section, select or clear the Enable background error checking checkbox. 3. Select the Indicate errors using this color button and select a color. 4. Select Reset Ignored Errors to return Excel to its default error indicators. 5. In the Error checking rules section, select or clear the checkboxes next to errors you want to indicate or ignore, respectively. To watch the values in a cell range 1. Select the cell range you want to watch. 2. In the Formula Auditing group, select the Watch Window button. 3. In the Watch Window dialog, select Add Watch. 4. In the Add Watch dialog, confirm the cell range, and then select Add. To delete a watch 1. Select the Watch Window button. 2. In the Watch Window dialog, select the watch you want to delete. 3. Select Delete Watch. Configure automatic and iterative calculation options Excel formulas use values in other cells to calculate their results. If you create a for- mula that refers to the cell that contains the formula, the result is a circular reference. Under most circumstances, Excel treats a circular reference as a mistake for two rea- sons. First, most Excel formulas don’t refer to their own cell, so a circular reference is unusual enough to be identified as an error. The second, more serious consideration is that a formula with a circular reference can slow down your workbook. Because Excel repeats, or iterates, the calculation, you must set limits on how many times the app repeats the operation. 96  Configure automatic and iterative calculation options You can control how often Excel recalculates formulas. Three calculation options are avail- able from the Formulas tab and from the Formulas page of the Excel Options dialog. 3 You can modify the iterative calculation options Excel uses The calculation options work as follows: Automatic recalculates a worksheet whenever a value that affects a formula changes. This is the default setting. Automatic Except for Data Tables recalculates a worksheet whenever a value changes but doesn’t recalculate data tables. Manual recalculates formulas only when you tell Excel to do so. You can also use options in the Calculation Options section to allow or disallow itera- tive calculations (repeating calculations of formulas that contain circular references). The default values (a maximum of 100 iterations and a maximum change per iteration of 0.001) are appropriate for all but the most unusual circumstances. To manually recalculate the active workbook On the Formulas tab, in the Calculation group, select Calculate Now. Press F9. To manually recalculate the active worksheet In the Calculation group, select the Calculate Sheet button. 97 Chapter 3: Perform calculations on data To set worksheet calculation options Display the worksheet whose calculation options you want to set. On the Formulas tab, in the Calculation group, select Calculation Options, and then select Automatic, Automatic Except for Data Tables, or Manual. To enable iterative calculations 1. Open the Excel Options dialog and display the Formulas page. 2. In the Calculation options section, select the Enable iterative calculation checkbox. 3. In the Maximum Iterations box, enter the maximum iterations allowed for a calculation. 4. In the Maximum Change box, enter the maximum change allowed for each iteration. 5. Select OK. 98  Skills review Skills review In this chapter, you learned how to: Name data ranges Create formulas to calculate values 3 Summarize data that meets specific conditions Copy and move formulas Create array formulas Find and correct errors in calculations Configure automatic and iterative calculation options 99  Chapter 3 Practice tasks Before you can complete these tasks, you must copy the book’s practice files to your computer. The practice files for these tasks are in the Excel365SBS\Ch03 folder. You can save the results of the tasks in the same folder. Name data ranges Open the NameRanges workbook in Excel, and then perform the following tasks: 1. Create a named range named Monday for the V_101 through V_109 values (found in cells C4:C12) for that weekday. 2. Edit the Monday named range to include the V_110 value for that column. 3. Select cells B4:H13 and create a batch of named ranges for V_101 through V_110, using the row headings as the range names. 4. Delete the Monday named range. Create formulas to calculate values Open the BuildFormulas workbook in Excel, and then perform the following tasks: 1. On the Summary worksheet, in cell F9, create a formula that displays the value from cell C4. 2. Edit the formula in cell F9 so it uses the SUM function to find the total of values in cells C3:C8. 3. In cell F10, create a formula that finds the total expenses for desktop software and server software. 4. Edit the formula in F10 so the cell references are absolute references. 5. On the JuneLabor worksheet, in cell F13, create a SUM formula that finds the total of values in the JuneSummary table’s Labor Expense column. 100  Practice tasks Summarize data that meets specific conditions Open the CreateConditionalFormulas workbook in Excel, and then perform the fol- lowing tasks: 1. In cell G3, create an IF formula that tests whether the value in F3 is greater than or equal to 35,000. If it is, display Request discount; if not, display No discount available. 2. Copy the formula from cell G3 to the range G4:G14. 3. In cell I3, create a formula that finds the average cost of all expenses in cells F3:F14 where the Type column contains the value Box. 4. In cell I6, create a formula that finds the sum of all expenses in cells F3:F14 where the Type column contains the value Envelope, and the Destination column contains the value International. Create array formulas Open the CreateArrayFormulas workbook in Excel, and then perform the following tasks: 1. On the Fuel worksheet, in cells C11:F11, enter the array formula =C3*C9:F9. 2. Edit the array formula you just created to read =C3*C10:F10. 3. On the Volume worksheet, in cells D4:D7, create the array formula =B4:B7*C4:C7. Find and correct errors in calculations Open the AuditFormulas workbook in Excel, and then perform the following tasks: 1. Set a watch on the value in cell C19. 2. Display the precedents for the formula in cell C7. 3. Hide the tracer arrows. 4. Use the Error Checking dialog to identify the error in cell C20. 5. Show the tracer arrows for the error. 6. Hide the arrows, and then change the formula in cell C20 to =C12/D20. 101  Chapter 3 7. Use the Evaluate Formula dialog to step through the formula in cell C20. 8. Delete the watch you created in step 1. Configure automatic and iterative calculation options Open the SetIterativeOptions workbook in Excel, and then perform the following tasks: 1. On the Formulas tab, in the Calculation group, select the Calculation Options button, and then select Manual. 2. In cell B6, enter the formula =B7*B9, and then press Enter. 3. Note that this result is incorrect because the Gross Savings value minus the Savings Incentive value should equal the Net Savings value, which it does not. 4. Press F9 to recalculate the workbook and read the message box indicating that you have created a circular reference. 5. Select OK. 6. Use options in the Excel Options dialog to enable iterative calculation. 7. Close the Excel Options dialog and recalculate the worksheet. 8. Change the workbook’s calculation options back to Automatic. 102