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 aPivotTable, build aPivotChart, 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.8, 2.1, 2.2, 2.3, 2.6, 2.8, 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.xlsxandBlueLake_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.
• Create and resize an Excel table.
• Format data in a worksheet.
• Copy a worksheet and rename a tab.
• Sort and filter data in a table.
• Create aPivotTable.
• Create aPivotChartobject.
• Add, edit, and format chart elements.
• Size and position a chart object.
• Create a sunburst chart sheet.
• Name a cell range.
• UseXLOOKUPin a formula.
• Create a formula with multiple mathematical operations.
• Build aSUMPRODUCTformula.
• Set document properties and page setup options.

Steps to complete this project:

Open theBlueLake_Project2-Excel-ACP-2Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modifyit.

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

1. Import a text file.

Import the tab-delimitedBlueLake_Project2.txtfile and load it to cellA19inSheet1. Verify that theAdd this data to the Data Modelbox in theImport Datadialog box isnotselected before you clickOK. The data imports in an Excel table and repositions the existing data (Figure 1).

Select cellsA20:C33and copy them to theClipboard. Do not include the blank row.

Select cellD19and open thePastegallery [Hometab,Clipboardgroup]. ChooseFormulasto paste the data without formatting.

PressEscto cancel the moving border.

1. Format data as a table.

Insert a blank row at row4.

Format cellsD5:F75as an Excel table withBlue, Table Style Medium 2.

Select and delete columnsA:C.

Select cellsA1:A3and set the font size to14 pt.

Set the width of columnsA:Cto15.00.

Apply theCenter Across Selectioncommand to cellsA1:C3. PressCtrl+Home.

Rename the worksheet asTransactions.

1. Copy and rename a worksheet.

Make a copy of theTransactionssheet at the end of the tabs.

Name the copied sheet asFiltered.

Make another copy of the Transactions sheet at the end and name itSunburstData.

1. Sort and filter data.

Select theFilteredworksheet tab.

Sort the data in ascending order byDepartment.

Show theTotalrow for the table.

Filter the table to show only theBike & SkateandGame Roomdata (Figure 2).

1. Create aPivotTable.

Select theTransactionsworksheet tab.

Select cellA5and create a blankPivotTablelayout on a new worksheet.

Show theDepartment,City, andTransactionsfields in the PivotTable.

Arrange theCityfield in theFiltersarea, theDepartmentfield in theRowsarea, and theTransactionsfield in theValuesarea (Figure 3).

Rename thePivotTablesheet asPivotTable.

1. Create and format aClustered ColumnPivotChart.

Select cellA4in thePivotTableand insert aClustered ColumnPivotChart.

Position and size the chart object to start at cellD2and reach to cellM19.

Select one of the columns in thePivotChartand click theFormat Selectionbutton [PivotChart Formattab,Current Selectiongroup].

Find and expand theFillcommand group and select theVary colors bypointbox.

Close theFormat Data Seriestask pane.

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

Apply aBlack, Text 1outline with a weight of ptto the chart object.

Hide the display ofField Buttonsin thePivotChart.

Select cellA21(Figure 4).

1. Create and format a sunburst chart.

Select theSunburstDatatab name.

Select columnB, cut it, and insert it at columnAto rearrange the data so that theCitycolumn is columnA. The top level in a hierarchy chart should be leftmost in the data.

Select cellsB1:B3and move them to columnA.

Select cellsA1:C3and apply theCenter Across Selectioncommand.

Select cellA5and insert aSunburstchart on its own sheet namedSunburstChart.

Edit theChart Titleplaceholder text to display# of Transactions by Department. The city names are the inner ring of the chart, the top level in the hierarchy.

Return to theSunburstDatasheet and filter the table to show all departments exceptApparel,Footwear, andRunning.

Return to theSunburstChartsheet. When values are too small to scale, the slice displays as blank (Figure 5).

1. Name a cell range and insert a column.

Select theLookup_Datatab name.

Select cellsA2:A6and name the rangeCities.

Select cellsB2:B6and name the rangeTaxRates.

Select theTransactionDatasheet and insert a column at columnC.

Select cellC5and typeTax Rate.

1. Build aXLOOKUPformula.

Select cellC6on theTransactionDatatab.

Build aXLOOKUPformula to lookup the value in cellA6in theCitiesrange and display the tax rate. (Figure 6).

Copy theXLOOKUPformula in columnCand format the results asPercent Stylewith 2 decimals.

1. Use order of precedence in a formula.

Select cellD6on theTransactionDatatab.

Type=to start a formula and click cellB6.

Type*(to multiply and enter the opening parenthesis.

Type1+and click cellC6. The sales amount is multiplied by 1 plus the tax rate to calculate the total bill.

PressEnter. The missing parenthesis is noted.

ClickYesto accept the correction.

Copy the formula to complete the data.

1. Preview the data and complete borders.

Preview the worksheet for printing.

Select cellA4and apply aLeft Border.

Apply aRight Borderto cellD4.

PressCtrl+Home(Fn+Ctrl+Left) and preview the worksheet again (Figure 7).

1. UseSUMPRODUCTto calculate fees by location.

Select theCardFeestab name and format the values in columnCasPercent Stylewith four decimal places.

Select cellC9and type.0095. The correct percentage is .9500%.

Type.0075. in cellC10.

Select cellC15, click theMath & Trigbutton [Formulastab,Function Librarygroup] and selectSUMPRODUCT. The formula multiplies the fee rate times the number of transactions for each type of card.

Select cellsC7:C10for theArray1argument and pressF4 (FN+F4).

Click theArray2box, select cellsD7:D10for theAtlantavalues as a relative reference, and clickDone.

Copy the formula to cellsD15:G15.

Format cellsC15:G15asAccounting Number Formatwith zero decimal places. Results are rounded to the nearest dollar.

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

Select cellE22. The formula multiplies the sum of cells D7:H7 times the fee for the card.

Click theMath & Trigbutton [Formulastab,Function Librarygroup] and selectSUM.

Select cellsD7:H7for theNumber1argument and clickOK.

Edit the formula to multiply the results by cellC7for theWhite Cardfee (see Figure 8). The sum is calculated first, and the result is multiplied by the value in cell C7.

Copy the formula to cellsE23:E25.

Format cellsE22:E25and cellsD7:H10asAccounting Number Formatwith zero decimal places.

PressCtrl+Home(Fn+Ctrl+Left).

1. Finalize the workbook by setting page options and document properties.

Open thePropertiesdialog box [Filemenu].

Select theSummarytab.

TypeTransactions Datain theTitlebox; type your name in theAuthorbox.

Click theCommentsbox, typeFirst week of September, and return to the workbook (Figure 9).

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

Select theTransactionssheet tab and scale it to fit a single page.

Save and close the workbook (Figure 10).