In Practice Excel $365$: Application Capstone Project $($Chs$.1-5)$

Steps to complete this project:

Mark the steps as checked when you complete them.

Open the BlueLake$\_$Project$2-$Excel$-$ACP$-2-$ADVX Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modifyit.

Text file imported as table

figure $1$ text 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.

Import the tab$-$delimited BlueLake$\_$Project$2.$txt file and load it to cell A$19$ in Sheet$1.$ Verify that the Add this data to the Data Model box in the Import Data dialog box is not selected before you click OK$.$ The data imports in an Excel table and repositions the existing data.

Copy cells A$20$:C$33$ and paste as Formulas to cell D$19$ without formatting.

Select and delete columns A:C and close the Queries and Connections pane.

Apply formats to cells in Sheet$1.$

Select cells A$4$:C$74$ and format the cells as an Excel table with Blue, Table Style Medium $2.$

Filtered data and total row

Figure $2$ Filtered data and total row

Select cells A$1$:A$3$ and set the font size to $14$ pt$.$

Set the width of columns A:C to $15\mathrm{.}00.$

Select cells A$1$:C$3$ and Center Across Selection.

Scale the sheet to fit a single page.

Select cell A$1$ or press CTRL$+$Home.

Rename the worksheet as Transactions $($Figure $1).$

Copy and rename a worksheet.

Make a copy of the Transactions 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.

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.

Display the Transactions worksheet.

Select cell A$5$ and create a blank PivotTable layout on a new worksheet.

Show the Department, City, and Transactions fields in the PivotTable.

In the PivotTable Fields task pane, arrange the City field in the Filters area, the Department field in the Rows area, and the Transactions field in the Values area.

Rename the PivotTable sheet as PivotTable.

Create and format a Clustered Column PivotChart.

Use the data in the PivotTable to insert a Clustered Column PivotChart.

Position and size the chart object to start at cell D$2$ and reach to cell M$18.$

Select one of the columns in the PivotChart to format the data point.

Modify the Fill to Vary colors by point.

Close the Format Data Point task pane.

Edit the chart title to display # of Transactions by Department.

Hide the Field Buttons on the PivotChart $[$PivotChart Analyze tab$].$

Set the page orientation to Landscape and scale the worksheet to fit a single page.

Select cell A$20($Figure $3).$

Complete PivotTable worksheet

figure $3$ completed pivottable worksheet

Create and format a sunburst chart.

Display the SunburstData worksheet.

Select cell B$4$ and drag cell B$4$ to cell A$4$ to rearrange the data so that the City column is column A$.$ The top level in a hierarchy chart should be leftmost in the data.

Use the data in the table to insert a Sunburst chart and move it to 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 $($Figure $4).$

Sunburst chart

Figure $4$ sunburst chart

Name a cell range and insert a column.

Display the Lookup$\_$Data worksheet.

Select cells A$2$:A$6$ and name the range City, and select cells B$2$:B$6$ and name the range TaxRates.

Display the TransactionData worksheet and insert a column before column C$.$

Select cell C$5$ and type Tax Rate

Build a XLOOKUP formula.

Select cell C$6$ on the TransactionData tab.

Build a XLOOKUP formula to lookup the value in cell A$6$ in the City range and display the tax rate.

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

Write a formula to calculate the amount of the sales ticket with tax.

Select cell D$6$ on the TransactionData tab.

Create a formula to calculate the amount of the sales ticket including tax.

TransactionData sheet completed

Figure $5$ TransactionData sheet completed

Copy the formula to complete the data.

Complete borders.

AutoFit the columns and preview the worksheet.

Select cell A$4$ and apply a Left Border.

Apply a Right Border to cell D$4.$

Press Ctrl$+$Home and preview the worksheet again $($Figure $5).$

Use SUMPRODUCT to calculate fees by location.

Display the CardFees worksheet and select cell C$15.$

Use SUMPRODUCT to build a function that multiplies the fee per transaction times the number of transactions for each type of card and totals the fees in a city.

Use an absolute reference for the fees per transaction so the range does not change when the funct