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)
Alternate Instruction for Microsoft 365 Apps icon 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.
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.
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.
Format the data on the Sales Data worksheet as a table using the Blue, Table Style Medium 2 table style:
SELECT ANY CELL IN THE DATA.
ON THE HOME TAB, IN THE STYLES GROUP, CLICK THE FORMAT AS TABLE BUTTON TO DISPLAY THE TABLE STYLES GALLERY.
CLICK THE BLUE, TABLE STYLE MEDIUM 2 QUICK STYLE. IT IS THE SECOND OPTION IN THE MEDIUM SECTION OF THE GALLERY.
VERIFY THAT THE MY TABLE HAS HEADERS CHECK BOX IS CHECKED AND THAT THE CORRECT DATA RANGE IS SELECTED.
CLICK OK.
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.
ON THE TABLE TAB, IN THE TABLE STYLE OPTIONS GROUP, CLICK THE TOTAL ROW CHECK BOX.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BUYERS COLUMN, CLICK THE ARROW, AND SELECT THE COUNT FUNCTION.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BEDROOMS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE BATHROOMS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE PURCHASE PRICE COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE RATE COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
IN THE TOTAL ROW AT THE BOTTOM OF THE TABLE, CLICK IN THE MORTGAGE YEARS COLUMN, CLICK THE ARROW, AND SELECT THE AVERAGE FUNCTION.
Sort the data so the newest purchases appear at the top.
CLICK ANYWHERE IN THE DATE OF PURCHASE COLUMN.
ON THE DATA TAB, IN THE SORT & FILTER GROUP, CLICK THE Z-A BUTTON.
Filter the data to show only houses sold by owner with three or four bedrooms.
CLICK THE ARROW AT THE TOP OF THE AGENT COLUMN.
CLICK THE (SELECT ALL) CHECK BOX TO REMOVE ALL OF THE CHECKMARKS.
CLICK THE CHECK BOX IN FRONT OF BY OWNER.
CLICK OK.
CLICK THE ARROW AT THE TOP OF THE BEDROOMS COLUMN.
CLICK THE (SELECT ALL) CHECK BOX TO REMOVE ALL OF THE CHECKMARKS.
CLICK THE CHECK BOXES IN FRONT OF 3 AND 4.
CLOSE THE DIALOG.
Create a line chart showing the purchase prices for houses by date.
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.
ON THE INSERT TAB, IN THE CHARTS GROUP, CLICK THE INSERT LINE OR AREA CHART BUTTON.
SELECT THE FIRST LINE CHART TYPE SHOWN.
THE LINE CHART IS INSERTED IN THE WORKSHEET.
Move the chart to its own sheet named Purchase Prices.
IF NECESSARY, SELECT THE CHART. ON THE CHART DESIGN TAB, IN THE LOCATION GROUP, CLICK THE MOVE CHART BUTTON.
IN THE MOVE CHART DIALOG, CLICK THE NEW SHEET RADIO BUTTON.
IN THE BOX TYPE: PURCHASE PRICES
CLICK OK.
Update the chart title and display the data labels as callouts.
CHANGE THE CHART TITLE TO: HOMES FOR SALE BY OWNER
CLICK THE CHART DESIGN TAB IF NECESSARY.
IN THE CHART LAYOUTS GROUP, CLICK THE ADD CHART ELEMENT BUTTON.
POINT TO DATA LABELS AND SELECT DATA CALLOUT.
Apply the Style 2 Quick Style to the chart.
SELECT THE CHART DESIGN TAB, IF IT IS NOT SELECTED ALREADY.
IN THE CHART STYLES GROUP SELECT STYLE 2.
Create a PivotTable to summarize the average purchase price of different house types for each agent.
RETURN TO THE SALES DATA WORKSHEET AND CLICK ANYWHERE IN THE TABLE.
CLICK THE INSERT TAB. IN THE TABLES GROUP, CLICK THE PIVOTTABLE BUTTON. VERIFY THAT THE TABLE/RANGE: RANGE IS TABLE1 AND CLICK OK.
ADD THE AGENT FIELD BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
ADD THE PURCHASE PRICE FIELD BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
SELECT ANY CELL IN THE SUM OF PURCHASE PRICE COLUMN.
ON THE PIVOTTABLE ANALYZE TAB, IN THE ACTIVE FIELD GROUP, CLICK THE FIELD SETTINGS BUTTON.
IN THE SUMMARIZE BY AREA, SELECT AVERAGE.
CLICK OK.
ADD THE HOUSE TYPE FIELD TO THE PIVOTTABLE BY CLICKING THE CHECK BOX IN THE FIELDS LIST PANE.
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.
Add column Sparklines to the right of the PivotTable.
SELECT CELLS B5:E9 TO USE AS THE DATA FOR THE SPARKLINES. YOU DO NOT WANT TO INCLUDE THE GRAND TOTAL COLUMN OR ROW.
ON THE INSERT TAB, IN THE SPARKLINES GROUP, CLICK THE SPARKLINES BUTTON AND SELECT COLUMN.
IN THE CREATE SPARKLINES DIALOG, VERIFY THAT THE CELL RANGE B5:E9 IS LISTED IN THE DATA RANGE BOX.
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.
CLICK OK.
Create a PivotChart from the PivotTable.