help
Shopaisha
excelch5.pdf
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 1/11
In Practice Excel 365: Application Capstone Project 2
COURSE NAME Fall 20231 CGS 1060 MASTER | SStankovic
CGS1060C COMP LITERACY ONLINE LIVE 715796
In Practice Excel 365: Application Capstone Project 2 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.
Start Date: 09/28/2212:00 AM US/Eastern
Due Date: 10/12/2211:59 PM US/Eastern
End Date: 10/12/2211:59 PM US/Eastern
Print Info
Student Name:
Trias Yilo, Clementina Coromoto
Student ID: triac3
Username: triac3
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 2/11
[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 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.
1. Open the BlueLake_Project2-Excel-ACP-2 Excel workbook start file. If the document opens in Protected View, click the Enable Editing button so you can modify it.
2. 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.
3. Import the tab-delimited BlueLake_Project2.txt file and load it to cell A19 in the existing sheet. (Figure 1).
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 3/11
4. Select cells A19:B32 and cut the cells.
5. Select cell C19 and open the Paste gallery [Home tab, Clipboard group]. Choose Paste to paste the data.
6. Select and delete columns A:B. 7. Apply formats to cells in Sheet1.
a. Select cells A4:C60. Format the cells as an Excel table with Blue, Table Style Medium 2 and remove the external connections.
b. Select cell A1:A3 and set the font size to 14 pt.
c. Set the width of each of columns A:C to 15.00 (110 pixels).
d. Select cells A1:C3, click the Format menu, and select Cells. Click the Alignment tab if necessary. Apply the Center Across Selection command.
e. Press fn+control+left arrow. 8. Rename the worksheet as
Transactions . 9. Copy and rename a worksheet.
a. Right-click the Transactions tab name make a copy of the sheet at the end of the tabs.
b. Name the copied sheet as
Filtered . c. Make another copy
of the Transactions
FIGURE 1 TEXT FILE IMPORTED AS TABLE
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 4/11
sheet at the end and name it
SunburstData . 10. Sort and filter data.
a. Select the Filtered worksheet tab. b. Sort the data in ascending order by Department. c. Show the Total row for the table. d. Filter the table to show only the Bike & Skate data (Figure 2).
11. Create a PivotTable. a. Select the Transactions worksheet tab. b. Select cell A5 and click the Recommended PivotTables button [Insert
tab, Tables group]. c. Show the Department field in the Rows area, the City field in the
Filters area, and Transactions field in the Values area (Figure 3).
d. Rename the PivotTable sheet as PivotTable .
FIGURE 2 DATA IS FILTERED AND DISPLAYS A TOTAL
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 5/11
12. Create and format a Clustered Column PivotChart. a. Select cell A4 in the PivotTable and insert a Clustered Column
PivotChart. b. Position and size the chart object to start at cell D2 and reach to cell
M18. c. Select one of the columns in the PivotChart and click the Format Pane
button [PivotChart Tools Format tab, Format group]. Click the Format Data Series tab in the pane.
d. Find and expand the Fill command group and select the Vary colors by point box.
e. Close the Format Data Series and the PivotChart Fields panes. f. Click the Total title box in the chart and edit the text to display # of
Transactions by Department. g. Select cell A20 (Figure 4).
13. Create and format a sunburst chart. a. Select the SunburstData tab name. b. Select cell B4. With the move pointer, 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.
c. Select cell A5 and insert a Sunburst chart on its own sheet named
SunburstChart .
FIGURE 3 CITY FIELD IS A FILTER
FIGURE 4 PIVOTTABLE AND ITS CHART
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 6/11
d. 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).
14. Name a cell range and insert a column. a. Select the Lookup_Data tab name.
b. Select cells A2:B5 and name the range TaxRates . Note that the first column of data is sorted in ascending order.
c. Select the TransactionData sheet and insert a column at column C. d. Select cell C5 and type Tax Rate.
15. Build a VLOOKUP formula. a. Select cell C6 on the TransactionData tab. b. 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 (Figure 6).
c. Copy the VLOOKUP formula in column C and format the results as Percent Style with 2 decimals.
16. Use order of precedence in a formula.
FIGURE 5 SUNBURST CHART
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 7/11
a. Select cell D6 on the
TransactionData tab.
b. Type = to start a formula and click cell B6.
c. Type *( to multiply and enter the opening parenthesis.
d. Type 1+ and click cell C6. The sales amount is multiplied by 1 plus the tax rate to calculate the total bill.
e. Press Enter. The missing parenthesis is noted. f. Click Yes to accept the correction (Figure 7). g. Copy the formula to complete the data.
17. Complete borders. a. Preview the worksheet. b. Select cell A4 and apply a Left Border. c. Apply a Right Border to cell D4. d. Select cell A1 and preview the worksheet again.
FIGURE 6 VLOOKUP ARGUMENTS
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 8/11
18. Use
FIGURE 7 MISSING PARENTHESIS IS FOUND
FIGURE 8 EDITED FORMULA TO CALCULATE TOTAL BY CARD TYPE
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 9/11
SUMPRODUCT to calculate fees by location. a. Select the CardFees tab name and select cell C15. The formula
multiplies the fee times the number of transactions for each type of card. b. Click the Math & Trig button [Formulas tab, Function Library group]
and select SUMPRODUCT. c. Select cells C7:C10 for the Array1 argument and make the references
absolute ($C$7:$C$10). d. Click the Array2 box, select cells D7:D10 for the Atlanta values as a
relative reference, and click Done. e. Copy the formula to cells D15:F15. f. Format cells C15:F15 as Accounting Number Format with zero
decimal places. 19. Use order of
precedence and relative references in a formula. a. Select cell
E22. The formula multiplies the sum of cells D7:G7 times the fee for the card.
b. Click the Math & Trig button [Formulas tab, Function Library group] and select SUM.
c. Select cells D7:G7 for the Number1 argument and click Done.
d. Edit the formula to multiply the results by cell C7 for the White Card fee (see Figure 8). The sum is calculated first, and the result is multiplied by the value in cell C7.
FIGURE 9 PROPERTIES DIALOG BOX
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 10/11
e. Copy the formula to cells E23:E25. f. Format cells E22:E25 as Accounting Number Format with zero
decimal places. g. Select cells D7:G10 and apply Comma Style with zero decimal places. h. Select cell A1.
20. Finalize the workbook by setting page options and document properties. a. Open the Properties dialog box [File menu]. b. Select the Summary tab.
c. Type Transactions Data in the Title box; type your name in the Author box.
d. Click the Comments box, type First week of
September , and return to the workbook (Figure 9). e. Select the PivotTable sheet tab. Change the page orientation to
landscape and scale the sheet to fit a single page. f. Select the Transactions sheet tab and scale it to fit a single page.
21. Save and close the workbook (Figure 10).
10/11/22, 9:58 AM SIMnet - In Practice Excel 365: Application Capstone Project 2
https://bconline.broward.edu/d2l/le/content/565904/viewContent/15628675/View 11/11
FIGURE 10 COMPLETED WORKSHEETS FOR ACP 2
