For this project, you complete a workbook for Blue Lake Sports by importing a text file and formatting data as an Excel table.
management
Description
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.
1. Open the BlueLake_Project1 workbook from your student data files and save it as [your initials] Excel ACP 1.
2. Apply the Facet theme to the workbook.
3. Select cell A19 on Sheet1. Import the tab‐delimited BlueLake_Project1.txt file at cell A19 to complete the data. Verify that the Add this data to the Data Model box in the Import Data dialog box is not selected before you click OK.
4. Select cells A4:C60 and format the cells as an Excel table with Table Style Medium 4. Select Yes in the message box to remove data connections because the selection has an external data range.
5. Apply formats to cells in Sheet1. a. Select cell A1:A3 and set the font size to 14 pt. b. Select cells A1:C3 and click the Alignment launcher [Home tab]. c. Select the Horizontal drop‐down list and choose Center Across Selection. d. Click OK and press Ctrl+Home. 6. Rename the worksheet Transactions.
7. Copy and rename a worksheet. a. Right‐click the Transactions worksheet tab and select Move or Copy. b. Copy the sheet to the end. c. Name the copied worksheet Filtered. d. Make another copy of the Transactions worksheet, move to the end, and name the copy SunburstData.
8. Sort and filter data.
a. Select the Transactions worksheet tab.
b. Click the Filter arrow for the Department column and sort the data in ascending order.
c. Select the Total Row box [Table Tools Design tab, Table Style Options group]
