Excel Mod 7 MindTap

Refresh PivotTable data. —> Refresh the data in the PivotTable located in the Sales PivotTable worksheet.

1. At the bottom of the Excel window, click the Sales PivotTable worksheet tab. 2. In the Sales PivotTable worksheet, click cell A3 (first cell in the table). 3. Click the PivotTable Tools Analyze tab (in green text). 4. In the Data group (dropdown), click the Refresh button (the symbol button above the dropdown).

Insert a Line chart. —> Insert a 2-D Line chart in the worksheet based on range A5:G14.

1. Click and drag to select range A5:G14 (the entire table). 2. Click the Insert tab on the ribbon. 3. In the Charts group, click the Insert Line or Area Chart button (1st option in the 2nd row). 4. In the 2-D Line section of the gallery, click the Line chart option, the first option in the section.

Create custom number format for a range of cells. —> Create and apply a custom format code to cells in range B3:D3 using the "yyyy" format.

1. Click and drag to select range B3:D3. 2. In the Cells group (dropdown) on the Home tab, click the Format button (dropdown). 3. On the menu, click Format Cells. 4. In the Category box, click Custom. 5. Select the current text in the Type box and type "yyyy" in the box. 6. Click OK.

Consolidate data from multiple ranges. —> In range B5:D7, consolidate data from range B5:D7 in the New York, Dallas, San Diego, and Deerfield worksheets using the SUM function.

1. Click and drag to select range B5:D7. 2. Click the Data tab on the ribbon. 3. In the Data Tools group (dropdown), click the Consolidate button. 4. Click the New York sheet tab (worksheet tab at the bottom). 5. Select range B5:D7. (The Reference text box should read: "'New York'!$B$5:$D$7".) 6. In the dialog box, click Add. 7. Click the Dallas sheet tab. (The Reference text box should read: "Dallas!$B$5:$D$7".) 8. In the dialog box, click Add. 9. Click the San Diego sheet tab. (The Reference text box should read: "'San Diego'!$B$5:$D$7") 10. In the dialog box, click Add. 11. Click the Deerfield sheet tab. (The Reference text box should read: "Deerfield!$B$5:$D$7") 12. In the dialog box, click Add. 13. Click OK.

Add a field to the ROWS area of the PivotTable Fields task pane. —> Add the Region field to the Rows area of the PivotTable Fields pane.

1. Click cell A3 (top left cell of table). 2. In the PivotTable Fields pane, in the Choose fields to add to report area, click the Region check box to select it.

Move fields between areas of the PivotTable Fields task pane. —> In the PivotTable Fields pane, move the Project Category field from the Columns area to the Rows area, placing it above the Branch field. Move the System Type field from the Columns area to the Rows area, placing it below the Branch field.

1. Click cell A4 (a category cell). 2. In the PivotTable Fields pane, click and drag the Project Category field from the Columns area to the Rows area, placing the Project Category field above the Branch field in the Rows area. Release the mouse button. 3. In the PivotTable Fields pane, click and drag the System Type field from the Columns area to the Rows area, placing the field below the Branch field in the Rows area. Release the mouse button.

Insert a slicer into a PivotTable. —> Add a slicer to the Regional Sales PivotTable to filter the data by Region.

1. Click cell A4 (first category cell in the table). 2. Click the PivotTable Tools Analyze tab on the ribbon (in green text). 3. In the Filter group (dropdown), click the Insert Slicer button. 4. In the Insert Slicers dialog box, click the Region check box to select it. 5. Click OK.

Insert a PivotTable. —> Use the PivotTable command on the Insert tab to insert a PivotTable in a new worksheet using the BranchSales table as the source range.

1. Click cell A4 (first category cell of table). 2. Click the Insert tab on the ribbon. 3. In the Tables group (dropdown), click the PivotTable button. 4. Click OK.

Apply a style to a PivotTable. —> Apply the Light Blue, Pivot Style Medium 6 style to the Client Projects PivotTable (Medium section, row 1, 6th option).

1. Click cell A4 (first category cell of table). 2. Click the PivotTable Tools Design tab (in green text). 3. In the PivotTable Styles group (dropdown), click the More button (the last arrow with the line above it). 4. In the gallery, in the Medium section, click the Light Blue, Pivot Style Medium 6 option, the sixth option in the first row of the section.

Display and hide the PivotTable Field list. —> Show and then hide the PivotTable Fields pane.

1. Click cell A4 (first cell in table underneath first category cell). 2. Click the PivotTable Tools Analyze tab (in green text). 3. In the Show group (dropdown), click the Field List button (to open the PivotTable Fields pane). 4. In the Show group (dropdown), click the Field List button again (to hide the PivotTable Fields pane).

Insert a Recommended PivotTable. —> Use the Recommended PivotTables command to insert a blank PivotTable in a new worksheet using the MonthlySales table as the source range.

1. Click cell A4 (the first cell in the table underneath the category cell; must be a cell within the range that you want to create a PivotTable for). 2. Click the Insert tab on the ribbon. 3. In the Tables group (dropdown), click the Recommended PivotTables button. 4. At the bottom of the dialog box, click the Blank PivotTable button.

Add a field to the FILTERS area of the PivotTable Fields pane. —> Add the Region field to the Filters area of the PivotTable Fields pane.

1. Click cell A4 (top left cell of table). 2. In the PivotTable Fields pane, in the Choose fields to add to report area, click and drag the Region field to the Filters area. Release the mouse button.

Add a field to the COLUMNS area of the PivotTable Fields task pane. —> Add the Branch field to the Client Contacts PivotTable in the Columns area of the PivotTable Fields pane.

1. Click cell A4 (top left cell of the table). 2. Click the PivotTable Tools Analyze tab (in green text). 3. In the Show group (dropdown), click the Field List button. 4. In the PivotTable Fields pane, in the Choose fields to add to a report area, move the pointer over the Branch field button. Press and hold the left mouse button, and then drag the Branch field to the Columns area. Release the mouse button.

Change the number format of a PivotTable VALUE field. —> In the PivotTable, change the number format of the Sum of January field to the Accounting number format with "0" (zero) decimal places.

1. Click cell A5 (any cell within the table). 2. In the PivotTable Fields pane, in the Values area, click the Sum of January field button (dropdown). 3. On the menu, click Value Field Settings. 4. At the bottom of the dialog box, click the Number Format button. 5. In the Category list, click Accounting. 6. In the Decimal places box, replace the current value with "0" (zero). 7. Click OK. 8. Click OK.

Add a field to the VALUES area of the PivotTable Fields task pane. —> In the PivotTable Fields pane, add the February and March fields to the PivotTable, placing the fields in the VALUES area of the pane.

1. Click cell A5 (random cell in table). 2. In the PivotTable Fields pane, in the Choose fields to add to report area, click the February check box to select it. 3. In the PivotTable Fields pane, in the Choose fields to add to report area, click the March check box to select it.

Create a nested formula using the INDEX and MATCH functions. —> In cell B20, create a nested formula using the INDEX and MATCH functions that returns the phone number for Manager ID M-07

1. Click cell B20. 2. In cell B20, type "=INDEX(A5:J18,MATCH("M-07",C5:C18,0),6)" and press ENTER. Notes: • A5:J18 is the [array] — aka the entire table range (not including top/category row). • "M-07" is the [lookup_value]. • C5:C18 is the [lookup_array] — aka the range of the Manager ID column (within which M-07 is listed) (the range does not include the top/category cell). • 0 is the [match_type]. • 6 is the phone number column number (column F = column 6).

Add a timeline slicer to a PivotTable. —> Add a timeline slicer to the PivotTable based on the Date field in the 2020 Client Contracts table.

1. Click cell B4 (any category cell). 2. Click the PivotTable Tools Analyze tab (in green text). 3. In the Filter group (dropdown), click the Insert Timeline button. 4. In the Insert Timelines dialog box, click the Date check box to select it. 5. Click OK.

Change the layout of a PivotTable. —> Change the layout of the Client Projects PivotTable to the Outline layout.

1. Click cell B5 (1st cell in the 2nd column of the table underneath the category cell). 2. Click the PivotTable Tools Design tab (in green text). 3. In the Layout group, click the Report Layout button (dropdown). 4. On the Report Layout menu, click Show in Outline Form.

Drill down into a PivotTable. —> Starting in cell B5 in the PivotTable, Drill Down one level to display data for System Types, then expand the Limited label to show the data outline. Drill Down another level to display data for Project Teams, then Drill Up two levels.

1. Click cell B5 (the "installation" cell in the table) 2. Click the PivotTable Tools Analyze tab (in green text). 3. In the Active Field group, click the Drill Down button. 4. To the left of the Limited label (the cell now says "Limited"), click the Expand (+) button. 5. With the Limited label still selected, click the Drill Down button again. 6. In the Active Field group, click the Drill Up button (not the dropdown arrow, but the UP arrow symbol above it). 7. In the Active Field group, click the Drill Up button. (Now the cell says "installation" again.)

Create a formula using the RATE function. —> In cell C4, enter a formula using the "RATE" function to calculate the interest rate given the investment parameters in Scenario A. Function arguments should include cell "C6" as the per argument, cell "C7" as the pmt argument, cell "C3" as the PV argument, and cell "C8" as the FV argument.

1. Click cell C4. 2. Type "=RATE(" in cell C4. 3. Click cell C6. 4. Type a "," (comma) after the cell reference. 5. Click cell C7. 6. Type a "," (comma) after the cell reference. 7. Click cell C3. 8. Type a "," (comma) after the cell reference. 9. Click cell C8. 10. Type ")" to complete the formula. 11. Press Enter. format: "=RATE(nper, pmt, pv, fv...)" formula: "=RATE(C6,C7,C3,C8)"

Create a formula using the IFS function. —> In cell C5, enter a formula using the "IFS" function that returns a value of "M1" if cell "G5" is greater than "500,000", a value of "M2" if cell "G5" is greater than "250,000", and a value of "M3" for all other results.

1. Click cell C5. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the Logical button (dropdown). 4. On the menu, click IFS. 5. Type "G5>500000" in the Logical_test1 box. 6. Press TAB. Type ""M1"" in the Value_if_true1 box. 7. Press TAB. Type "G5>250000" in the Logical_test2 box. 8. Press TAB. Type ""M2"" in the Value_if_true2 box. 9. Press TAB. Type "TRUE" in the Logical_test3 box. 10. Press TAB. Type ""M3"" in the Value_if_true3 box. 11. Click OK.

Create a formula using the MATCH function. —> In cell D1, create a formula using the MATCH function that returns the location of the company name of "RELICO" in cells "B5:B18".

1. Click cell D1. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the Lookup & Reference button (dropdown). 4. On the Lookup & Reference menu, click MATCH. 5. In the Function Arguments dialog box, type "RELICO" in the Lookup_value box. 6. Press TAB. 7. Type "B5:B18" in the Lookup_array box. 8. Press TAB. 9. Type the number "0" in the Match_type box. 10. In the Function Arguments dialog box, click OK.

Create a formula using the HLOOKUP function. —> In cell E4, create a formula using the HLOOKUP function to determine the project staffing needs for client EnergyPro based on the project type in cell "C4". Use range "H3:K4" as the lookup table, and the staffing needs value listed in row "2" of the Project Staffing Lookup Table. Do not enter a value for the optional range_lookup argument.

1. Click cell E4. 2. Click the Formulas tab on the ribbon. 3. Click the Insert Function Button (in the Function Library group/dropdown). 4. Click the Or select a category arrow. 5. Click Lookup & Reference. 6. In the Select a function box, click HLOOKUP. 7. Click OK. 8. Type "C4" in the Lookup_value text box. 9. Press TAB. 10. Type "H3:K4" in the Table_array text box. 11. Press TAB. 12. Type "2" in the Row_index_num text box. 13. Click OK.

Change the custom name of a PivotTable VALUE field. —> In the PivotTable, change the custom name of the VALUE field Sum of Q1 to "1st Quarter Sum" as the new field name.

1. Click cell E5 ("Sum of Q1" category cell). 2. Click the PivotTable Tools Analyze tab (in green text). 3. In the Active Field group, click the Field Settings button. 4. Type "1st Quarter Sum" in the Custom Name text box to rename the field. 5. Click OK.

Create a formula using the OFFSET function. —> In cell F1, create a formula using the OFFSET function that returns the value in row "2" and column "3" from the starting point of cell "A5".

1. Click cell F1. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the Lookup & Reference button (dropdown). 4. On the Lookup & Reference menu, click OFFSET. 5. In the Function Arguments dialog box, type "A5" in the Reference text box. 6. Press TAB. 7. Type "2" in the Rows box. 8. Press TAB. 9. Type "3" in the Cols box. 10. In the Function Arguments dialog box, click OK.

Add a calculated field to a PivotTable. —> Create a calculated field in the Sales PivotTable, naming the field "Q1", that totals the values in the January, February, and March fields.

1. Click cell F5 (top right cell of table). 2. Click the PivotTable Tools Analyze tab (green text at top). 3. In the Calculations group (dropdown), click the Fields, Items, & Sets button (dropdown). 4. Click Calculated Field. 5. Type "Q1" in the Name text box. 6. In the Fields list, double-click January. (Now the Formula text box says "= January".) 7. Type "+" (plus) in the Formula text box (after the "= January"). 8. In the Fields list, double-click February. 9. Type "+" (plus) in the Formula text box. 10. In the Fields list, double-click March. (The Formula text box should look like: "= January+ February+ March".) 11. Click OK.

Create a formula using the OR function. —> In cell F5, enter a formula using the OR function that returns a value of TRUE if either the condition "E5>25000" or the condition "D5>=3" is met.

1. Click cell F5. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the Insert Function button. 4. In the Insert Function dialog box, click the Or select a category arrow. 5. In the list of function categories, click Logical. 6. In the Select a function box, click OR. 7. Click OK. 8. In the Function Arguments dialog box, type "E5>25000" in the Logical1 text box. 9. Press TAB. 10. Type "D5>=3" in the Logical2 text box. 11. In the Function Arguments dialog box, click OK.

Create a nested formula. —> In cell F5, enter a formula, using a nested IF function, to calculate the Bonus Amount using the following function arguments: type "D5=3" in the Logical_test text box, type "E5*.1" in the Value_if_true text box, and type "IF(D5=2, E5*.05, 0)" in the Value_if_false text box.

1. Click cell F5. 2. To the left of the formula bar, click the Insert Function (fx) button. 3. Click the Or select a category arrow. 4. Click Logical. 5. In the Select a function box, click IF. 6. Click OK. 7. Type "D5=3" in the Logical_test box. 8. Press TAB. 9. Type "E5*.1" in the Value_if_true box. 10. Press TAB. 11. Type "IF(D5=2, E5*.05, 0)" in the Value_if_false box. 12. Click OK.

Create a formula using the MAXIFS function. —> In cell H3, enter a formula using the MAXIFS function to find the highest salary for a "Construction" position with a Performance Rating of "2".

1. Click cell H3. 2. Type "=MAXIFS(E4:E21,C4:C21,"Construction",D4:D21,2)" in cell H3, and press ENTER. Notes: • E4:E21 is the [max_range] — aka the range of the Salary column (not including the top/category cell). • C4:C21 is the [criteria_range1] — aka the range of the Position column (not including the top/category cell). • "Construction" is the [critera1]. • D4:D21 is the [criteria_range2] — aka the range of the Performance Rating column (not including the top/category cell). • 2 is the [criteria2].

Create a formula using the MINIFS function. —> Create a formula using the MINIFS function that finds the lowest salary for a "Construction" position with a Performance Rating of "2".

1. Click cell H3. 2. Type "=MINIFS(E4:E21,C4:C21,"Construction",D4:D21,2)" in cell H3, and press ENTER. Notes: • E4:E21 is the [min_range] — aka the range of the Salary column (not including the top/category cell). • C4:C21 is the [criteria_range1] — aka the range of the Position column (not including the top/category cell). • "Construction" is the [critera1]. • D4:D21 is the [criteria_range2] — aka the range of the Performance Rating column (not including the top/category cell). • 2 is the [criteria2].

Create a formula using the AND function. —> In cell H4, enter a formula using the AND function, with "F4>3" as the Logical1 condition, and "G4>4" as the Logical2 condition. Excel will return a value of TRUE if both conditions are met and a value of FALSE if one or both of the conditions are not met.

1. Click cell H4. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the Logical button (dropdown). 4. Click AND. 5. Type "F4>3" in the Logical1 text box. 6. Press TAB. 7. Type "G4>4" in the Logical2 text box. 8. Click OK.

Create a formula using the AVERAGEIF function. —> In cell I4, enter a formula using the AVERAGEIF function to calculate the average Contract Amount values found in range "E4:E18" for records with a Project Type value of "Retainer" in range "D4:D18".

1. Click cell I4. 2. Click the Formulas tab on the ribbon. 3. In the Function Library group (dropdown), click the More Functions button (dropdown). 4. Point to (hover over) Statistical. 5. Click AVERAGEIF. 6. Type "D4:D18" in the Range text box. 7. Press TAB. 8. Type "Retainer" in the Criteria text box. 9. Press TAB. 10. Type "E4:E18" in the Average_range text box. 11. Click OK.

Create a formula using the AVERAGEIFS function. —> In cell I6, enter a formula using the "AVERAGEIFS" function to calculate the average Fee values found in range E5:E18 for records with a Project Team value of "Blue" in range C5:C18 and a System Type value of "FIRE" in range D5:D18.

1. Click cell I6. 2. Type "=AVERAGEIFS(" in cell I6. 3. Click and drag to select range E5:E18. 4. Type a "," (comma) in cell I6. 5. Click and drag to select range C5:C18. 6. Type ","BLUE"," in cell I6. 7. Click and drag to select range D5:D18. 8. Type ","FIRE")" in cell I6 and then press ENTER. format: "=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2..." formula: "=AVERAGEIFS(E5:E18,C5:C18,"Blue",D5:D18,"FIRE")

Create a formula using the COUNTIF function. —> In cell J5, enter a formula using the COUNTIF function to count the number of cells in range "G5:G18" with a value of "TX".

1. Click cell J5. 2. Type "=COUNTIF(G5:G18,"TX")" in cell J5, and press ENTER.

Create a formula using the SUMIF function. —> In cell J5, enter a formula using the SUMIF function to search range "D5:D22" for associates with a Performance Rating of "3", then total the Bonus values listed in range "F5:F22" for the associates that meet the criteria.

1. Click cell J5. 2. Type "=SUMIF(D5:D22,3,F5:F22)" in cell J5, and press ENTER.

Create a formula using the INDEX function. —> In cell J7, enter a formula using the INDEX function to find the value in row 15, column 3 of range A3:G17.

1. Click cell J7. 2. Type "=INDEX(A3:G17,15,3)" in cell J7, and press ENTER.

Apply a PivotChart Layout. —> Apply Layout 2 to the PivotChart (Quick Layout gallery, first row, second option).

1. Click the PivotChart. 2. Click the PivotChart Tools Design tab on the ribbon (in green text). 3. In the Chart Layouts group, click the Quick Layout button (dropdown). 4. In the Quick Layout gallery, click Layout 2 (first row, second option).

Change the chart type of a PivotChart. —> Change the PivotChart type to a 3-D Pie chart.

1. Click the PivotChart. 2. Click the PivotChart Tools Design tab on the ribbon (in green text). 3. In the Type group (dropdown), click the Change Chart Type button. 4. In the left pane of the dialog box, click Pie. 5. In the list of Pie chart options, click the 3-D Pie option, the second option in the list. 4. Click OK.

Edit the number format of data labels in a chart. —> Apply the Currency format with "0" (zero) decimal places to the Column chart data labels, and then close the pane.

1. Click the chart. 2. Click the Chart Elements button (the green + symbol to the right of the chart). 3. Point to (hover over) Data Labels, and then click the arrow that appears. 4. Click More Options on the fly-out menu. 5. In the task pane, click the Label Options arrow (to collapse the section). 6. Click the Number arrow to expand the Number section. 7. Click the Category arrow. 8. Click Currency. 9. In the Decimal box, replace the existing text by typing "0" (zero) in the box. 10. Press ENTER. 11. In the pane, click the Close button (X symbol at the top right).

Add a data series to a chart. —> Add the San Diego data series to the chart (using cells A5:D8).

1. Click the chart. 2. Click the Chart Tools Design tab on the ribbon (in green text). 3. On the Chart Tools Design tab, in the Data group (dropdown), click the Select Data button. 4. Click and drag to select the cell range A5:D8, so that the San Diego data row is included in the chart (the Chart data range text box should read "=Sales!$A$5:$D$8"). 5. In the Select Data Source dialog box, click OK.

Move a slicer on a worksheet. —> Move the slicer in the worksheet so that the upper-left corner is in cell F4.

1. Click the title bar of the slicer (top area of the slicer). 2. Position the mouse pointer over the title bar of the slicer, then click and drag the slicer so that the upper-left corner is in cell F4. Release the mouse button.

Filter a PivotTable using a Report filter. —> Filter the Sales by Region PivotTable using the Report filter for the Region field to display only those records for the Southern Region.

1. In the PivotTable, click the Region filter arrow (the arrow that appears in the cell to the right of the Region cell, in a row above the table). 2. On the filter menu, click Southern. 3. Click OK.

Filter data in a PivotTable using a Slicer. —> Use the Region slicer to filter the PivotTable to display only data for the Southern and Western regions.

1. In the Region slicer, click the Southern button. 2. Press and hold the CTRL key, click the Western button, then release the CTRL key (might have to use SHIFT key to select them both).

Filter a table based on a single field. —> Filter the Client Contacts table to display only records with a value of Chicago in the City field.

1. In the table, click the filter arrow for the City column (the arrow on the City category cell). 2. On the menu, click the (Select All) check box to deselect all values. 3. Click the Chicago check box. 4. Click OK.

Filter a PivotChart. —> Filter the PivotChart on the Branch field to display only records with a value of New York.

1. On the PivotChart, click the Branch arrow at the lower-left corner of the chart. 2. On the Branch AutoFilter menu, click the (Select All) check box to deselect it. 3. On the menu, click the New York check box to select it. 4. Click OK.

Change the summary function used by a PivotTable VALUE field. —> In the PivotTable, change the summary function used by the January Values field from Sum to Average.

1. Right-click cell B5 (the cell underneath the "January" category cell). 2. On the shortcut menu, point to (hover over) Summarize Values By. 3. On the submenu, click Average.

Add alt text to an object. —> Add the alternative text "Sales forecast for years 2020 through 2022" to the chart.

1. Right-click the chart. 2. On the shortcut menu, click Edit Alt Text. 3. In the Alt Text pane, type "Sales forecast for years 2020 through 2022" as the alternative text. 4. In the Alt Text pane, click the Close button (X symbol at the top right).