Excel Application Project

In Practice Excel 365: Application Capstone Project 2 Alternate 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.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: [login to view URL] and [login to view URL] Completed Project File Name: [your name][login to view URL] 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. Steps to complete this project: Mark the steps as checked when you complete them. Open the BlueLake_Project2-Excel-ACP-2-ALT Excel workbook start file. If the workbook 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. 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. Import the tab-delimited [login to view URL] file using the From Text (Legacy) command in to cell A19 in Sheet1. The imported data repositions the existing data (Figure 1).Imported data from text file Figure 1 Imported text file Copy cells A19:C32 and paste as Formulas to cell D19 without formatting. Select and delete columns A:C. Apply formats to cells in Sheet1. Select cells A4:C74 and format the cells as an Excel table with Blue, Table Style Medium 2. IMPORTANT NOTE: Due to a bug in Mac Excel, you may have trouble formatting the data as a table. If you have trouble completing this step do the following: Copy the Sheet1 worksheet and place the copy before the original Sheet1 worksheet. Delete the original Sheet1 worksheet. Complete this step and the following steps on the copy Sheet1 (2) worksheet. You will rename the worksheet later on in the instructions. Select cells A1:A3 and set the font size to 14 pt. Set the width of columns A:C to 15.00. Select cells A1:C3 and Center Across Selection. Scale the sheet to fit a single page. Select cell A1 or press fn+control+left arrow. 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 [login to view URL] is filtered and displays a total Figure 2 Filtered data and total row Show the Total row for the table. Filter the table to show only the Bike & Skate data (Figure 2). Create aPivotTable. Display the Transactions worksheet. Select cell A5 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 aClustered ColumnPivotChart. 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 M18. Select one of the columns in the PivotChart and format the data series. Modify the Fill to Vary colors by point box. Close the Format Data Series task pane. Edit the chart title to display # of Transactions by Department. If necessary, 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 A20 (Figure 3).PivotTable and its chart Figure 3 pivottable and its chart Create and format a sunburst chart. Display the SunburstData worksheet. Select cell B4 and 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. 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 A2:B6 and name the range TaxRates. Note that the first column of data is sorted in ascending order. Display the TransactionData worksheet and insert a column before column C. Select cell C5 and type Tax Rate. Build a VLOOKUPformula. 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. Copy the VLOOKUP formula in column C and format the results as Percent Style with 2 decimals. Write a formula to calculate the amount of sales ticket with tax. Select cell D6 on the TransactionData tab. Create a formula to calculate the amount of the sales ticket including tax. Copy the formula to complete the [login to view URL] TransactionData sheet figure 5 transactiondata sheet completed Complete borders. AutoFit the columns and preview the worksheet. Select cell A4 and apply a Left Border. Apply a Right Border to cell D4. Press Fn+control+left arrow and preview the worksheet again. UseSUMPRODUCTto calculate fees by location. Display the CardFees worksheet and select cell C15. 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 function is copied. Use relative references for the number of transactions in Atlanta so the range updates when the function is copied. Copy the formula to cells D15:G15. 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. Select cell E22. 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. Copy the formula to cells E23:E25. Format cells E22:E25 Accounting Number Format with zero decimal places. Select cells D7:H10 and apply Comma Style with zero decimals. Edit the values in cells C9:C10 to .0095 and .0075. Format cells C7:C10 as Percent Style with 2 decimal places. Press fn+control+left arrow. Finalize the workbook by setting page options and document properties. Open the Properties dialog box. 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. Save and close the workbook. Upload and save your project file. Submit project for grading. Completed worksheets for AP 2

Project ID: 40429249