Business Finance Homework Help

Skills needed to complete this project:

• Converting Data into Tables (Skill 5.11)
• Adding Total Rows to Tables (Skill 5.13)
• Sorting Data (Skill 5.15)
• Filtering Data (Skill 5.16)
• Inserting a Line Chart (Skill 5.3)
• Resizing and Moving Charts (Skill 5.4)
• Showing and Hiding Chart Elements (Skill 5.6)
• Applying Quick Styles and Colors to Charts (Skill 5.7)
• Creating PivotTables Using Recommended PivotTables (Skill 5.19)
• Inserting Sparklines (Skill 5.10)
• Creating a PivotChart from a PivotTable (Skill 5.20)
• Analyzing Data with Data Tables (Skill 5.21)
• Analyzing Data with Goal Seek (Skill 5.22)

This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

1. OPEN THE START FILE EX2019-SKILLREVIEW-5-2. THE FILE WILL BE RENAMED AUTOMATICALLY TO INCLUDE YOUR NAME. CHANGE THE PROJECT FILE NAME IF DIRECTED TO DO SO BY YOUR INSTRUCTOR, AND SAVE IT.
2. IF THE WORKBOOK OPENS IN PROTECTED VIEW, CLICK THE ENABLE EDITING BUTTON IN THE MESSAGE BAR AT THE TOP OF THE WORKBOOK SO YOU CAN MODIFY THE WORKBOOK.

NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box.

1. Format the data on the Sales Data worksheet as a table using the Blue, Table Style Medium 2 table style:
   1. SELECT ANY CELL IN THE DATA.
   2. ON THE HOME TAB, IN THE STYLES GROUP, CLICK THE FORMAT AS TABLE BUTTON TO DISPLAY THE TABLE STYLES GALLERY.
   3. CLICK THE BLUE, TABLE STYLE MEDIUM 2 QUICK STYLE. IT IS THE SECOND OPTION IN THE MEDIUM SECTION OF THE GALLERY.
   4. VERIFY THAT THE MY TABLE HAS HEADERS CHECK BOX IS CHECKED AND THAT THE CORRECT DATA RANGE IS SELECTED.
   5. CLICK OK.
2. Add a Total row to the table to display the number of buyers; the average number of bedrooms and bathrooms for each sale; and the average purchase price, interest rate, and mortgage length.
   1. ON THE TABLE TAB, IN THE TABLE STYLE OPTIONS GROUP, CLICK THE TOTAL ROW CHECK BOX.
   2. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BUYERS COLUMN, CLICK THE ARROW, AND SELECT THE COUNT FUNCTION.
   3. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BEDROOMS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
   4. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BATHROOMS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
   5. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE PURCHASE PRICE COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGEFUNCTION.
   6. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE RATE COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
   7. IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE MORTGAGE YEARS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGEFUNCTION.
3. Sort the data so the newest purchases appear at the top.
   1. CLICK ANYWHERE IN THE DATE OF PURCHASE COLUMN.
   2. ON THE DATA TAB, IN THE SORT & FILTER GROUP, CLICK THE Z-A BUTTON.
4. Filter the data to show only houses sold by owner with three or four bedrooms.
   1. CLICK THE ARROW AT THE TOP OF THE AGENT COLUMN.
   2. CLICK THE (SELECT ALL) CHECK BOX TO REMOVE ALL OF THE CHECKMARKS.
   3. CLICK THE CHECK BOX IN FRONT OF BY OWNER.
   4. CLICK OK.
   5. CLICK THE ARROW AT THE TOP OF THE BEDROOMS COLUMN.
   6. CLICK THE (SELECT ALL) CHECK BOX TO REMOVE ALL OF THE CHECKMARKS.
   7. CLICK THE CHECK BOXES IN FRONT OF 3 AND 4.
   8. CLOSE THE DIALOG.
5. Create a line chart showing the purchase prices for houses by date.
   1. SELECT THE DATE OF PURCHASE DATA CELLS. BE CAREFUL NOT TO INCLUDE THE COLUMN HEADING. PRESS AND HOLD THE COMMAND KEY AND CLICK AND DRAG TO SELECT THE PURCHASE PRICE DATA CELLS, AGAIN BEING CAREFUL NOT TO INCLUDE THE COLUMN HEADING.
   2. ON THE INSERT TAB, IN THE CHARTS GROUP, CLICK THE INSERT LINE OR AREA CHART BUTTON.
   3. SELECT THE FIRST LINE CHART TYPE SHOWN.
   4. THE LINE CHART IS INSERTED IN THE WORKSHEET.
6. Move the chart to its own sheet named Purchase Prices.
   1. IF NECESSARY, SELECT THE CHART. ON THE CHART DESIGN TAB, IN THE LOCATION GROUP, CLICK THE MOVE CHART BUTTON.
   2. IN THE MOVE CHART DIALOG, CLICK THE NEW SHEET RADIO BUTTON.
   3. IN THE BOX TYPE: PURCHASE PRICES
   4. CLICK OK.
7. Update the chart title and display the data labels as callouts.
   1. CHANGE THE CHART TITLE TO: HOMES FOR SALE BY OWNER
   2. CLICK THE CHART DESIGN TAB IF NECESSARY.
   3. IN THE CHART LAYOUTS GROUP, CLICK THE ADD CHART ELEMENT BUTTON.
   4. POINT TO DATA LABELS AND SELECT DATA CALLOUT.
8. Apply the Style 2 Quick Style to the chart.
   1. SELECT THE CHART DESIGN TAB, IF IT IS NOT SELECTED ALREADY.
   2. IN THE CHART STYLES GROUP SELECT STYLE 2.
9. Create a PivotTable to summarize the average purchase price of different house types for each agent.
   1. RETURN TO THE SALES DATA WORKSHEET AND CLICK ANYWHERE IN THE TABLE.
   2. CLICK THE INSERT TAB. IN THE TABLES GROUP, CLICK THE PIVOTTABLE BUTTON. VERIFY THAT THE TABLE/RANGE: RANGE IS TABLE1 AND CLICK OK.
   3. ADD THE AGENT FIELD BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
   4. ADD THE PURCHASE PRICE FIELD BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
   5. SELECT ANY CELL IN THE SUM OF PURCHASE PRICE COLUMN.
   6. ON THE PIVOTTABLE ANALYZE TAB, IN THE ACTIVE FIELD GROUP, CLICK THE FIELD SETTINGS BUTTON.
   7. IN THE SUMMARIZE BY AREA, SELECT AVERAGE.
   8. CLICK OK.
   9. ADD THE HOUSE TYPE FIELD TO THE PIVOTTABLE BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
   10. TO SUMMARIZE THE HOUSE TYPE DATA FOR EACH AGENT, USE THE HOUSE TYPE FIELD AS COLUMNS IN THE PIVOTTABLE. CLICK AND DRAG THE HOUSE TYPE FIELD FROM THE VALUES BOX TO THE COLUMNS BOX IN THE PIVOTTABLE FIELDS PANE.
10. Add column Sparklines to the right of the PivotTable.
    1. SELECT CELLS B5:E9 TO USE AS THE DATA FOR THE SPARKLINES. YOU DO NOT WANT TO INCLUDE THE GRAND TOTAL COLUMN OR ROW.
    2. ON THE INSERT TAB, IN THE SPARKLINES GROUP, CLICK THE SPARKLINES BUTTON AND SELECT COLUMN.
    3. IN THE CREATE SPARKLINES DIALOG, VERIFY THAT THE CELL RANGE B5:E9 IS LISTED IN THE DATA RANGE BOX.
    4. ADD THE RANGE G5:G9 TO THE LOCATION RANGE BOX EITHER BY TYPING THE CELL RANGE OR BY CLICKING AND DRAGGING TO SELECT IT IN THE WORKSHEET.
    5. CLICK OK.
11. Create a PivotChart from the PivotTable.
    1. SELECT ANY CELL IN THE PIVOTTABLE.
    2. ON THE PIVOTTABLE ANALYZE TAB, IN THE TOOLS GROUP, CLICK THE PIVOTCHART BUTTON.
    3. RIGHT-CLICK THE CHART AND POINT TO CHANGE CHART TYPE. THEN POINT TO COLUMN AND SELECT CLUSTERED BAR FROM THE 2-D BARSECTION.
    4. IF NECESSARY, MOVE THE PIVOTCHART TO ANOTHER PART OF THE WORKSHEET SO IT DOES NOT COVER THE PIVOTTABLE DATA.
12. Use the data in the Loan Worksheet sheet to run a what-if scenario for a client to show loan payments for a variety of interest rates and loan lengths. This what-if scenario requires a two-variable data table.
    1. GO TO THE LOAN WORKSHEET SHEET AND FAMILIARIZE YOURSELF WITH THE FORMULA IN CELL B5. PAY CLOSE ATTENTION TO THE CELL REFERENCES.
    2. SELECT CELLS B5:E25 TO USE THE PAYMENT FORMULA IN B5 AND THE VARIOUS YEARS AND RATES AS THE DATA TABLE.
    3. ON THE DATA TAB, IN THE FORECAST GROUP, CLICK THE WHAT-IF ANALYSIS BUTTON, AND CLICK DATA TABLE.
    4. IN THE ROW INPUT CELL BOX, ENTER THE CELL REFERENCE FOR THE LENGTH OF THE LOAN—THE NPER ARGUMENT FROM THE FORMULA IN CELL B5: C2.
    5. IN THE COLUMN INPUT CELL BOX, ENTER THE CELL REFERENCE FOR THE LOAN INTEREST RATE—THE INTEREST ARGUMENT FROM THE FORMULA IN CELL B5: A2.
    6. CLICK OK. (DATA TABLE VALUES ARE INSERTED)
13. Use Goal Seek to determine the most you can afford to borrow, on a $950 per month budget:
    1. ON THE LOAN WORKSHEET SHEET, SELECT THE OUTCOME FORMULA CELL, H4.
    2. ON THE DATA TAB, IN THE FORECAST GROUP, CLICK THE WHAT-IF ANALYSIS BUTTON AND CLICK GOAL SEEK…
    3. VERIFY THAT THE OUTCOME CELL H4 IS REFERENCED IN THE SET CELL BOX.
    4. ENTER THE OUTCOME VALUE OF 950 IN THE TO VALUE BOX.
    5. ENTER THE INPUT CELL H9 IN THE BY CHANGING CELL BOX.
    6. CLICK OK.
    7. CLICK OK AGAIN TO ACCEPT THE GOAL SEEK SOLUTION.
    8. NOTICE THE LOAN PAYMENT CHANGED TO $950 AND THE AMOUNT TO BORROW WAS COMPUTED TO BE $206,395.
14. SAVE AND CLOSE THE WORKBOOK.
15. UPLOAD AND SAVE YOUR PROJECT FILE.
16. SUBMIT PROJECT FOR GRADING.

Project II

In this project, you will analyze sales data using consolidation, subtotals, outlines, conditional formatting, advanced filters, PivotTables, and PivotCharts.

Skills needed to complete this project:

• Using GETPIVOTDATA (Skill 8.21)
• Using Consolidate to Create Subtotals (Skill 8.8)
• Sorting Data on Multiple Criteria (Skill 8.4)
• Adding Subtotals (Skill 8.7)
• Creating an Outline (Skill 8.9)
• Creating New Conditional Formatting Rules (Skill 8.1)
• Filtering and Sorting Using Cell Attributes (Skill 8.3)
• Managing Conditional Formatting Rules (Skill 8.2)
• Using Advanced Filter (Skill 8.6)
• Refreshing Data in a PivotTable (Skill 8.16)
• Adding a Calculated Field to a PivotTable (Skill 8.17)
• Applying a Quick Style to a PivotTable (Skill 8.22)
• Changing the Layout of a PivotTable (Skill 8.23)
• Filtering PivotTable Data (Skill 8.19)
• Filtering Data in a PivotChart (Skill 8.24)
• Changing the Look of a PivotChart (Skill 8.25)

This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.

1. OPEN THE START FILE EX2019-SKILLREVIEW-8-1. THE FILE WILL BE RENAMED AUTOMATICALLY TO INCLUDE YOUR NAME. CHANGE THE PROJECT FILE NAME IF DIRECTED TO DO SO BY YOUR INSTRUCTOR, AND SAVE IT.
2. IF THE WORKBOOK OPENS IN PROTECTED VIEW, CLICK THE ENABLE EDITING BUTTON IN THE MESSAGE BAR AT THE TOP OF THE WORKBOOK SO YOU CAN MODIFY THE WORKBOOK.

NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box.

1. Use GETPIVOTDATA to extract data from a PivotTable. In cell D1 on the Analysis worksheet, display the total annual sales for Ambulatory Care of TX, Inc.
   1. IF NECESSARY, GO TO THE ANALYSIS WORKSHEET.
   2. SELECT CELL D1.
   3. TYPE =
   4. CLICK THE PIVOTTABLE WORKSHEET TAB.
   5. CLICK CELL B4.
   6. PRESS ENTER.
2. Use consolidate to create a summary of the sales data by region. The summary will be located on the Analysis worksheet.
   1. REMAIN ON THE ANALYSIS WORKSHEET AND SELECT CELL A4.
   2. ON THE DATA TAB, IN THE DATA TOOLS GROUP, CLICK THE CONSOLIDATE BUTTON.
   3. VERIFY THAT SUM IS SELECTED IN THE FUNCTION BOX.
   4. IF THERE ARE ANY REFERENCES IN THE ALL REFERENCES BOX, CLICK EACH AND THEN CLICK THE – BUTTON TO REMOVE THEM.
   5. CLICK IN THE REFERENCE BOX, AND THEN CLICK THE SALES DATA WORKSHEET TAB. CLICK AND DRAG TO SELECT CELLS C3:E67.
   6. CLICK BOTH THE TOP ROW AND LEFT COLUMN CHECK BOXES.
   7. CLICK OK.
   8. ON THE ANALYSIS WORKSHEET, DELETE CELLS B4:B12, ALLOWING THE OTHER CELLS TO SHIFT LEFT.
3. Sort the sales data alphabetically by region and then by last name.
   1. GO TO THE SALES DATA WORKSHEET, AND CLICK ANY CELL IN THE DATA SET.
   2. ON THE DATA TAB, IN THE SORT & FILTER GROUP, CLICK THE SORT BUTTON.
   3. IN THE SORT DIALOG, EXPAND THE SORT BY, COLUMN LIST, AND SELECT REGION.
   4. CLICK THE + BUTTON TO ADD A LEVEL.
   5. EXPAND THE THEN BY, COLUMN LIST, AND SELECT LAST NAME.
   6. CLICK OK.
4. Add subtotals to the data to calculate the total commission earned for each sales associate.
   1. ON THE DATA TAB, IN THE OUTLINE GROUP, CLICK THE SUBTOTAL BUTTON.
   2. EXPAND THE AT EACH CHANGE IN LIST, AND SELECT LAST NAME.
   3. VERIFY THAT SUM IS SELECTED IN THE USE FUNCTION BOX.
   4. VERIFY THAT THERE IS A CHECKMARK NEXT TO COMMISSION EARNED IN THE ADD SUBTOTAL TOBOX.
   5. CLICK OK.
5. Copy the subtotal data to the Analysis worksheet.
   1. ON THE SALES DATA WORKSHEET, CLICK THE OUTLINE LEVEL 2 BUTTON TO COLLAPSE THE LIST SO ONLY THE TOTAL COMMISSION EARNED FOR EACH SALES ASSOCIATE IS VISIBLE. THIS WILL MAKE IT EASIER TO COPY THE DATA.
   2. SELECT CELLS A3:G77 AND COPY THEM TO THE CLIPBOARD. DO NOT INCLUDE THE GRAND TOTALROW (ROW 78).
   3. GO TO THE ANALYSIS WORKSHEET AND PASTE THE COPIED DATA BEGINNING IN CELL A15.
6. Notice that when you paste the subtotaled data, Excel removes the subtotal grouping, but keeps the subtotal rows and formulas. You can use the Outline command to re-create groups based on the subtotal formulas.
   1. CLICK ANY CELL IN THE DATA RANGE.
   2. ON THE DATA TAB, IN THE OUTLINE GROUP, CLICK THE GROUP BUTTON ARROW, AND SELECT AUTO OUTLINE.
   3. CLICK THE OUTLINE LEVEL 1 BUTTON TO COLLAPSE THE LIST SO ONLY THE TOTAL COMMISSION EARNED FOR EACH SALES ASSOCIATE IS VISIBLE.
7. On the Commissions worksheet, create a new conditional formatting rule to apply an icon set to the values in the Rating column.
   1. GO TO THE COMMISSIONS WORKSHEET AND SELECT CELLS H3:H66.
   2. ON THE HOME TAB, IN THE STYLES GROUP, CLICK THE CONDITIONAL FORMATTING BUTTON. CLICK NEW RULE…
   3. THE NEW FORMATTING RULE DIALOG OPENS.
   4. IN THE STYLE BOX, EXPAND THE LIST, AND SELECT ICON SETS.
   5. EXPAND THE ICONS LIST, AND SELECT THREE STARS.
   6. CLICK THE SHOW ICON ONLY CHECKBOX TO HIDE THE VALUES IN THE SELECTED CELLS.
   7. IN THE DISPLAY SECTION, EXPAND THE FIRST TYPE LIST AND SELECT NUMBER.
   8. IN THE FIRST VALUE BOX TYPE 9500 TO DISPLAY A COMPLETELY FILLED GOLD STAR WHEN THE CELL VALUE IS GREATER THAN OR EQUAL TO 9,500.
   9. EXPAND THE SECOND TYPE LIST AND SELECT NUMBER.
   10. IN THE SECOND VALUE BOX, TYPE 5000 TO DISPLAY A PARTIALLY FILLED GOLD STAR WHEN THE CELL VALUE IS LESS THAN 9,500 AND GREATER THAN OR EQUAL TO 5,000.
   11. CLICK OK.
8. Sort the sales data so cells with the completely filled gold star icon appear first.
   1. SCROLL DOWN AND FIND A CELL WITH A COMPLETELY FILLED GOLD STAR.
   2. RIGHT-CLICK A CELL WITH A COMPLETELY FILLED GOLD STAR, POINT TO SORT, AND SELECT PUT SELECTED FORMATTING ICON ON TOP.
9. On the Sales Data Filter worksheet, delete the conditional formatting rule that applies a font format to cells where the value is above average.
   1. GO TO THE SALES DATA FILTER WORKSHEET.
   2. ON THE HOME TAB, IN THE STYLES GROUP, CLICK THE CONDITIONAL FORMATTING BUTTON. CLICK MANAGE RULES… TO OPEN THE CONDITIONAL FORMATTING RULES MANAGER.
   3. IF NECESSARY, EXPAND THE SHOW FORMATTING RULES FOR LIST AND SELECT THIS WORKSHEET.
   4. SELECT THE ABOVE AVERAGE RULE AND CLICK THE – BUTTON TO DELETE THE RULE.
   5. CLICK OK.
10. Use the Advanced Filter feature to find sales greater than $120,000 for George Anderson or Mia Zhu.
    1. REMAIN ON THE SALES DATA FILTER WORKSHEET, AND INSERT FOUR NEW ROWS ABOVE ROW 3, SO THE COLUMN LABEL ROW IS NOW ROW 7 AND THERE ARE FIVE BLANK ROWS BETWEEN THE TITLE AND THE COLUMN LABELS. IF NECESSARY, CLEAR ALL FORMATTING FROM THE NEW ROWS.
    2. COPY THE RANGE A7:G7 (THE COLUMN LABELS) AND PASTE IN A3:G3.
    3. Enter the filter criteria in A4:G5 as follows:
       1. IN A4, TYPE: GEORGE
       2. IN B4, TYPE: ANDERSON
       3. IN E4, TYPE: >120000
       4. IN A5, TYPE: MIA
       5. IN B5, TYPE: ZHU
       6. IN E5, TYPE: >120000
    4. CLICK ANY CELL IN THE DATA SET.
    5. ON THE DATA TAB, IN THE SORT & FILTER GROUP, CLICK THE ADVANCED BUTTON.
    6. VERIFY THAT THE FILTER THE LIST, IN PLACE RADIO BUTTON IS SELECTED.
    7. THE LIST RANGE BOX SHOULD DISPLAY $A$7:$G$71. IF IT DOES NOT, CLICK IN THE LIST RANGE BOX, AND THEN CLICK AND DRAG TO SELECT CELLS A7:G71.
    8. CLICK IN THE CRITERIA RANGE BOX. CLICK AND DRAG TO SELECT THE RANGE A3:G5 AS THE CRITERIA RANGE.
    9. CLICK OK.
11. The data in the Sales Data worksheet has changed since the PivotTable was created. Refresh the PivotTable to reflect the changes.
    1. GO TO THE PIVOTTABLE WORKSHEET AND CLICK ANYWHERE IN THE PIVOTTABLE.
    2. ON THE PIVOTTABLE ANALYZE TAB, IN THE DATA GROUP, CLICK THE REFRESH BUTTON.
    3. IN THE PIVOTTABLE FIELDS PANE, CLICK LAST NAME TO ADD THE LAST NAME FIELD TO THE ROWS BOX.
    4. IN THE ROWS BOX, CLICK LAST NAME AND MOVE IT ABOVE CLIENT.
12. Add a calculated field to determine the average commission rate for each row in the PivotTable.
    1. ON THE PIVOTTABLE ANALYZE TAB, IN THE CALCULATIONS GROUP, CLICK THE FIELDS, ITEMS, & SETS BUTTON.
    2. CLICK CALCULATED FIELD…
    3. IN THE NAME BOX, TYPE: AVERAGE COMMISSION CALCULATED
    4. IN THE FIELDS BOX, CLICK COMMISSION EARNED, AND THEN CLICK THE INSERT FIELD BUTTON.
    5. TYPE: /
    6. IN THE FIELDS BOX, DOUBLE-CLICK ANNUAL SALES.
    7. THE FINAL FORMULA IN THE FORMULA BOX SHOULD LOOK LIKE THIS:
       ='COMMISSION EARNED'/'ANNUAL SALES'
    8. CLICK OK TO ADD THE CALCULATED FIELD TO THE PIVOTTABLE.
    9. CLICK ANY CELL IN THE NEW CALCULATED COLUMN.
    10. ON THE PIVOTTABLE ANALYZE TAB, IN THE ACTIVE FIELD GROUP, CLICK THE FIELD SETTINGS BUTTON TO OPEN THE PIVOTTABLE FIELD DIALOG.
    11. IN THE CUSTOM NAME BOX, TYPE: AVERAGE COMMISSION
    12. CLICK THE NUMBER… BUTTON AT THE BOTTOM LEFT CORNER OF THE DIALOG. IN THE CATEGORYLIST, CLICK PERCENTAGE. IF NECESSARY, TYPE 2IN THE DECIMAL PLACES BOX.
    13. CLICK OK.
    14. CLICK OK AGAIN TO CLOSE THE PIVOTTABLE FIELD DIALOG.
13. Apply a Quick Style to the PivotTable.
    1. CLICK ANYWHERE IN THE PIVOTTABLE.
    2. ON THE PIVOTTABLE DESIGN TAB, IN THE PIVOTTABLE STYLES GROUP, CLICK THE MORE BUTTON TO EXPAND THE GALLERY.
    3. CLICK LIGHT BLUE, PIVOT STYLE MEDIUM 2.
14. Change the layout of the PivotTable to Tabular and repeat the item labels on every row.
    1. ON THE PIVOTTABLE DESIGN TAB, IN THE LAYOUT GROUP, CLICK THE REPORT LAYOUT BUTTON AND SELECT SHOW IN TABULAR FORM.
       ON THE PIVOTTABLE TOOLS DESIGN TAB, IN THE LAYOUT GROUP, CLICK THE REPORT LAYOUT BUTTON AND SELECT SHOW IN TABULAR FORM.
    2. ON THE PIVOTTABLE DESIGN TAB, IN THE LAYOUT GROUP, CLICK THE REPORT LAYOUT BUTTON AND SELECT REPEAT ALL ITEM LABELS.
       ON THE PIVOTTABLE TOOLS DESIGN TAB, IN THE LAYOUT GROUP, CLICK THE REPORT LAYOUT BUTTON AND SELECT REPEAT ALL ITEM LABELS.
15. Use slicers to filter the PivotTable by region to show only the SW2 region.
    1. ON THE PIVOTTABLE ANALYZE TAB, IN THE FILTER GROUP, CLICK THE INSERT SLICER BUTTON.
    2. IN THE INSERT SLICERS DIALOG, CLICK THE REGION CHECK BOX. CLICK OK.
    3. IN THE REGION SLICER, CLICK THE SW2 BUTTON.
16. Filter the PivotChart data to show only the NW1 and NW2 regions.
    1. GO TO THE PIVOTCHART WORKSHEET AND SELECT THE CHART.
    2. ON THE PIVOTCHART ANALYZE TAB, IN THE FILTER GROUP, CLICK THE INSERT SLICER BUTTON.
    3. IN THE INSERT SLICERS DIALOG, CLICK THE REGION CHECK BOX. CLICK OK.
    4. IN THE REGION SLICER, CLICK THE NW1 BUTTON. PRESS THE [COMMAND] KEY AND CLICK THE NW2 BUTTON.
17. Apply a Quick Style to the PivotChart.
    1. SELECT THE PIVOTCHART.
    2. ON THE PIVOTCHART DESIGN TAB, IN THE CHART STYLES GROUP, CLICK THE MORE BUTTON TO EXPAND THE GALLERY IF NECESSARY.
    3. CLICK STYLE 8.
18. SAVE AND CLOSE THE WORKBOOK.
19. 
    

Project III