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
• Add, edit, and format chart elements.
• Size and position a chart object.
• Create a sunburst chart sheet.
• Name a cell range.
• Use VLOOKUPin a formula.
• Create a formula with multiple mathematical operations.
• Build a SUMPRODUCT
• Set document properties and page setup options.

1. Open the BlueLake_Project2-Excel-ACP-2Excel 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 txtfile and load it to cell A19 in the existing sheet. (Figure 1).

• Select cells A19:B32and cut the cells.
• Select cell C19and 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.

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

• Select cell A1:A3and set the font size to 14 pt.
• Set the width of each of columns A:Cto 15.00 (110 pixels).
• Select cells A1:C3, click the Formatmenu, and select Cells. Click the Alignment tab if necessary. Apply the Center Across Selection command.
• Pressfn+control+left arrow.
• Rename the worksheet as Transactions.

Copy and rename a worksheet.

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

• Name the copied sheet as Filtered.
• Make another copy of the Transactionssheet at the end and name it SunburstData.

Sort and filter data.

1. Select the Filteredworksheet tab.

• Sort the data in ascending order by Department.
• Show the Totalrow for the table.
• Filter the table to show only the Bike & Skatedata (Figure 2).

Create a PivotTable.

1. Select the Transactionsworksheet tab.

• Select cell A5and click the Recommended PivotTables button [Insert tab, Tables group].
• Show the Departmentfield 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 ColumnPivotChart.

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

• Position and size the chart object to start at cell D2and reach to cell M18.
• Select one of the columns in the PivotChartand click the Format Pane button [PivotChart Tools Format tab, Format group]. Click the Format Data Series tab in the pane.
• Find and expand the Fillcommand group and select the Vary colors by point box.
• Close the Format Data Seriesand the PivotChart Fields panes.
• Click the Totaltitle 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 B4to 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 A5and 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:B5and name the range TaxRates. Note that the first column of data is sorted in ascending order.
• Select the TransactionDatasheet and insert a column at column C.
• Select cell C5and type Tax Rate.

Build a VLOOKUP formula.

1. Select cell C6on the TransactionData 

• Build a VLOOKUPformula 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 VLOOKUPformula in column C and format the results as Percent Style with 2 decimals.

Use order of precedence in a formula.

1. Select cell D6on the TransactionData 

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 Yesto 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 A4and apply a Left Border.
• Apply a Right Borderto cell D4.
• Select cell A1and preview the worksheet again.

Use SUMPRODUCT to calculate fees by location.

15. 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 & Trigbutton [Formulas tab, Function Library group] and select SUMPRODUCT.
• Select cells C7:C10for the Array1 argument and make the references absolute ($C$7:$C$10).
• Click the Array2box, 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.

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

• Click the Math & Trigbutton [Formulas tab, Function Library group] and select SUM.
• Select cells D7:G7for the Number1 argument and click Done.
• Edit the formula to multiply the results by cell C7for 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:E25as Accounting Number Format with zero decimal places.
• Select cells D7:G10and apply Comma Style with zero decimal places.
• Select cell A1.

Finalize the workbook by setting page options and document properties.

1. Open the Propertiesdialog box [File menu].

• Select the Summarytab.
• Type Transactions Data in the Titlebox; type your name in the Author box.
• Click the Commentsbox, type First week of September, and return to the workbook (Figure 9).
• Select the PivotTablesheet tab. Change the page orientation to landscape and scale the sheet to fit a single page.
• Select the Transactionssheet tab and scale it to fit a single page.

Save and close the workbook (Figure 10).

FIGURE 10COMPLETED WORKSHEETS FOR ACP 2