In Practice Excel 365: Application Capstone
Project 2

Windows   Mac

For this project, you complete a workbook for Blue Lake Sports by importing a text file and formatting data as an Excel table. You filter and sort data, create a PivotTable, build a PivotChart, and insert a sunburst chart. In addition, you name ranges and build formulas.

[Student Learning Outcomes: [1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.8, 2.1, 2.2, 2.3, 2.6, 2.7, 3.1, 3.3, 3.4, 3.7, 4.1, 4.3, 4.4, 4.6, 4.8]

Files Needed: BlueLake_Project2-Excel-ACP-2.xlsx and BlueLake_Project2.txt
Completed Project File Name: [your name]-BlueLake_Project2-Excel-ACP-2.xlsx

Skills Covered in this Project

• Open, rename, and save a workbook.
• Import a text file.
• Format data in a worksheet.
• Copy a worksheet and rename a tab.
• Create an Excel table.
• Sort and filter data in a table.
• Create a PivotTable.
• Create a PivotChart object.
• Add, edit, and format chart elements.
• Size and position a chart object.
• Create a sunburst chart sheet.
• Name a cell range.
• Use VLOOKUP in a formula.
• Create a formula with multiple mathematical operations.
• Build a SUMPRODUCT formula.
• Set document properties and page setup options.
• Open the BlueLake_Project2-Excel-ACP-2 Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modify it.

· 

FIGURE 1TEXT FILE IMPORTED AS TABLE

·  The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.

FIGURE 2DATA IS FILTERED AND DISPLAYS A TOTAL

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. Import the tab-delimited BlueLake_Project2.txt file and load it to cell A19 in the existing sheet. (Figure 1).

·  Select cells A19:B32 and cut the cells.

·  Select cell C19 and open the Paste gallery [Home tab, Clipboard group]. Choose Paste to paste the data.

·  Select and delete columns A:B.

Apply formats to cells in Sheet1.

1. Select cells A4:C60. Format the cells as an Excel table with Blue, Table Style Medium 2 and remove the external connections.

·  Select cell A1:A3 and set the font size to 14 pt.

·  Set the width of each of columns A:C to 15.00 (110 pixels).

·  Select cells A1:C3, click the Format menu, and select Cells. Click the Alignment tab if necessary. Apply the Center Across Selection command.

·  Press fn+control+left arrow.

·  Rename the worksheet as Transactions.

Copy and rename a worksheet.

1. Right-click the Transactions tab name make a copy of the sheet at the end of the tabs.

·  Name the copied sheet as Filtered.

·  Make another copy of the Transactions sheet at the end and name it SunburstData.

Sort and filter data.

1. Select the Filtered worksheet tab.

·  Sort the data in ascending order by Department.

·  Show the Total row for the table.

·  Filter the table to show only the Bike & Skate data (Figure 2).

Create a PivotTable.

1. Select the Transactions worksheet tab.

·  Select cell A5 and click the Recommended PivotTables button [Insert tab, Tables group].

·  Show the Department field in the Rows area, the City field in the Filters area, and Transactions field in the Values area (Figure 3).

Rename the PivotTable sheet as PivotTable.

• · 

FIGURE 3CITY FIELD IS A FILTER

·  Create and format a Clustered Column PivotChart.

1. Select cell A4 in the PivotTable and insert a Clustered Column PivotChart.

·  Position and size the chart object to start at cell D2 and reach to cell M18.

·  Select one of the columns in the PivotChart and click the Format Pane button [PivotChart Tools Format tab, Format group]. Click the Format Data Series tab in the pane.

·  Find and expand the Fill command group and select the Vary colors by point box.

·  Close the Format Data Series and the PivotChart Fields panes.

·  Click the Total title box in the chart and edit the text to display # of Transactions by Department.

Select cell A20 (Figure 4).

• · 

FIGURE 4PIVOTTABLE AND ITS CHART

·  Create and format a sunburst chart.

1. Select the SunburstData tab name.

·  Select cell B4. With the move pointer, drag cell B4 to cell A4 to rearrange the columns so that the City column is column A. The top level in a hierarchy chart should be leftmost in the data.

·  Select cell A5 and insert a Sunburst chart on its own sheet named SunburstChart.

Edit the Chart Title placeholder text to display # of Transactions by Department. The city names are the inner ring of the chart, the top level in the hierarchy (see Figure 5).

• · 

FIGURE 5SUNBURST CHART

·  Name a cell range and insert a column.

1. Select the Lookup_Data tab name.

· 

FIGURE 6VLOOKUP ARGUMENTS

·  Select cells A2:B5 and name the range TaxRates. Note that the first column of data is sorted in ascending order.

·  Select the TransactionData sheet and insert a column at column C.

·  Select cell C5 and type Tax Rate.

Build a VLOOKUP formula.

1. Select cell C6 on the TransactionData tab.

·  Build a VLOOKUP formula to lookup the value in cell A6 in the TaxRates range and display the tax rate. The Range_lookup argument is not necessary because the data in the Table_array is sorted (Figure 6).

·  Copy the VLOOKUP formula in column C and format the results as Percent Style with 2 decimals.

Use order of precedence in a formula.

1. Select cell D6 on the TransactionData tab.

· 

FIGURE 7MISSING PARENTHESIS IS FOUND

·  Type = to start a formula and click cell B6.

·  Type *( to multiply and enter the opening parenthesis.

·  Type 1+ and click cell C6. The sales amount is multiplied by 1 plus the tax rate to calculate the total bill.

·  Press Enter. The missing parenthesis is noted.

·  Click Yes to accept the correction (Figure 7).

Copy the formula to complete the data.

• · 

FIGURE 8EDITED FORMULA TO CALCULATE TOTAL BY CARD TYPE

·  Complete borders.

1. Preview the worksheet.

·  Select cell A4 and apply a Left Border.

·  Apply a Right Border to cell D4.

·  Select cell A1 and preview the worksheet again.

Use SUMPRODUCT to calculate fees by location.

1. Select the CardFees tab name and select cell C15. The formula multiplies the fee times the number of transactions for each type of card.

·  Click the Math & Trig button [Formulas tab, Function Library group] and select SUMPRODUCT.

·  Select cells C7:C10 for the Array1 argument and make the references absolute ($C$7:$C$10).

·  Click the Array2 box, select cells D7:D10 for the Atlanta values as a relative reference, and click Done.

·  Copy the formula to cells D15:F15.

Format cells C15:F15 as Accounting Number Format with zero decimal places.

• · 

FIGURE 9PROPERTIES DIALOG BOX

·  Use order of precedence and relative references in a formula.

1. Select cell E22. The formula multiplies the sum of cells D7:G7 times the fee for the card.

·  Click the Math & Trig button [Formulas tab, Function Library group] and select SUM.

·  Select cells D7:G7 for the Number1 argument and click Done.

·  Edit the formula to multiply the results by cell C7 for the White Card fee (see Figure 8). The sum is calculated first, and the result is multiplied by the value in cell C7.

·  Copy the formula to cells E23:E25.

·  Format cells E22:E25 as Accounting Number Format with zero decimal places.

·  Select cells D7:G10 and apply Comma Style with zero decimal places.

·  Select cell A1.

Finalize the workbook by setting page options and document properties.

1. Open the Properties dialog box [File menu].

·  Select the Summary tab.

·  Type Transactions Data in the Title box; type your name in the Author box.

·  Click the Comments box, type First week of September, and return to the workbook (Figure 9).

·  Select the PivotTable sheet tab. Change the page orientation to landscape and scale the sheet to fit a single page.

·  Select the Transactions sheet tab and scale it to fit a single page.

Save and close the workbook (Figure 10).

FIGURE 10COMPLETED WORKSHEETS FOR ACP 2