In Practice Excel 365: Application Capstone Project 1 (Chs. 1-4) Advanced with XLOOKUP
SIMnet Excel project instructions
· Excel Projects Help
In Practice Excel 365: Application Capstone Steps to complete this project:
Mark the steps as checked when you complete them.
1.Open the BlueLake_Project1-Excel-ACP-1-ADVX Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modify it.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor.
2.Import the tab-delimited BlueLake_Project1-ADVX.txt file and load it to cell A19 in Sheet1. 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.
3.Copy cells A20:C33 and paste as Formulas to cell D19 without formatting.
4.Delete columns A:C and close the Queries and Connections pane.
5.Complete formatting in Sheet1.
6.Select cells A4:C74 and format the cells as an Excel table with Dark Teal, Table Style Medium 2.
7.Insert a blank row at row 4.
8.Set the font size for cells A1:A3 to 14 pt.
9.Set the width of columns A:C to 15.00.
10.Select cells A1:C3 and Center Across Selection.
Center the page horizontally.
11.Add print titles to repeat rows 1:5.
12.Rename the worksheet as SeptTransactions (Figure 1).
Text file imported as table
Figure 1 Completed SeptTransactions worksheet
13.Copy and rename a worksheet.
Make a copy of the SeptTransactions sheet at the end of the tabs.
Name the copied sheet Filtered.
Make a copy of the Filtered sheet at the end and name it SunburstData.
14.Sort and filter data.
15.Select the Filtered worksheet tab.
16.Sort the data in ascending order by Department.
Show the Total row for the table.
Filter the table to show only the Bike & Skate and Lacrosse data (Figure 2).
Filtered data and total row
Figure 2 Completed Filtered worksheet
17.Create a PivotTable.
18.Display the SeptTransactions worksheet.
19.Select cell A6 and create a blank PivotTable layout on a new worksheet.
Show the Department, City, and Transactions fields in the PivotTable.
20.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.
21.Rename the PivotTable sheet as PivotTable.
22.Create and format a Clustered ColumnPivotChart.
23.Use the data in the PivotTable to insert a Clustered Column PivotChart.
Position and size the chart object to start at cell D2 and reach to cell N20.
24.Select one of the columns in the PivotChart and modify the Fill to Vary colors by point.
25.Edit the chart title to display # of Transactions by Department and make it bold.
Hide the Field Buttons on the PivotChart [PivotChart Analyze tab].
26.Add a 1 pt Black, Text 1 outline to the chart.
27.Delete the legend.
28.Edit the labels in the PivotTable to Category and # of Transactions.
29.Apply White, Pivot Style Light 8 to the PivotTable.
30.Set the page orientation to Landscape and scale the worksheet to fit a single page.
31.Select cell A22 (Figure 3).
32.Complete PivotTable worksheet
Figure 3 Completed PivotTable worksheet
33.Create and format a sunburst chart.
34.Display the SunburstData worksheet.
Rearrange the columns so that the City column is column A. The top level in a hierarchy chart should be leftmost in the data.
Left align the labels in cells A1:A3.
35.Use the data in the table to insert a Sunburst chart and move it to its own sheet named SunburstChart. NOTE: To ensure accurate grading, be sure to move the chart to a New sheet using the Move Chart button.
36.Edit the Chart Title placeholder text to display # of Transactions by City and Department. The city names are the inner ring of the chart, the top level in the hierarchy.
37.Apply Style 4 and a 1 pt Black, Text 1 outline to the chart.
38.Delete the legend (Figure 4).
Sunburst chart
Figure 4 Completed SunburstChart worksheet
Name a cell range and insert a column.
39.Display the Lookup_Data worksheet.
40.Select cells A2:A6 and name the range Cities. Note that the names are sorted in ascending order.
41.Display the TransactionData worksheet and insert a column before column C.
42.Select cell C5 and type Tax Rate.
Build a XLOOKUP formula.
43.Select cell C6 on the TransactionData tab.
Build a XLOOKUP formula to lookup the city in the Cities range and display the tax rate.
44.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.
45.Select cell D6 on the TransactionData tab.
46.Create a formula to calculate the amount of the sales ticket including tax.
47.Copy the formula to complete the data.
48.Complete borders.
AutoFit the columns and preview the worksheet.
49.Select cell A4 and apply a Left Border.
50.Apply a Right Border to cell D4.
Preview the worksheet again (Figure 5).
TransactionData sheet completed
Figure 5 Completed TransactionData worksheet
51.Use SUMPRODUCT to calculate fees by location.
52.Display the CardFees worksheet and select cell C15.
53.Use SUMPRODUCT to build a function that multiplies the fee per transaction times the number of transactions for Atlanta.
54.Use an absolute reference for the fees per transaction so the range does not change when the function is copied.
55.Use relative references for the number of transactions in Atlanta so the range updates when the function is copied.
56.Copy the formula to cells D15:G15.
57.Format cells C15:G15 as Accounting Number Format with zero decimal places.
Combine a SUM function with a mathematical operator to calculate total fees by per card type.
58.Select cell E21.
Start a SUM function to add the number of transactions per card type and multiply the SUM result by the fee per transaction to calculate the total fees by card type.
59.Copy the formula to cells E22:E24.
60.Format cells E21:E24 as Accounting Number Format with zero decimal places.
61.Select cells D7:H10 and apply Comma Style with zero decimal places.
62.Edit the values in cells C9:C10 to .0095 and .0075.
63.Format cells C7:C10 as Percent Style with 2 decimal places.
64.Press Ctrl+Home (Figure 6).
Finalize the workbook by setting page options and document properties.
65.Open the Properties dialog box.
66.Type Transactions Data in the Title box; type your name in the Author box.
67.Click the Comments box, type First week of September, and return to the workbook.
68.Save and close the workbook.
69.Upload and save your project file.
70.Submit project for grading.
Project 1 Advanced with XLOOKUP