10/15/23, 10:14 PM Excel 2021 In Practice – Ch 4 Independent Project 4-4 – SIMnet

https://lonestarup.simnetonline.com/sp/assignments/projects/details/8403929 1/3

Excel 2021 In Practice – Ch 4 Independent
Project 4-4

COURSE NAME Fine BCIS 1305 6003 Fall 2023 | Fine BCIS 1305 6003 Fall 2023

Independent Project 4-4
Eller Systems has received contract data in a text file. You transform and import, sort, and filter the data. You also create a
PivotTable, prepare a worksheet with subtotals, and format related data as an Excel table.

[Student Learning Outcomes 4.1, 4.3, 4.5, 4.6, 4.8]

File Needed: Eller-04.xlsx (Available from the Start File link.) and EllerText-04.txt (Available from the Resources link.)

Completed Project File Name: [your name]-Eller-04.xlsx

Skills Covered in This Project
Format data in an Excel table.

Sort data in an Excel table.

Import a text file.

Transform data in Power Query.

Sort data by multiple columns.

Add a calculated field in a table.

Create a PivotTable.

Format fields in a PivotTable.

Use the Subtotal command.

Steps to complete This Project
Mark the steps as checked when you complete them.

1. Open the Eller-04 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, and save it.

2. Click the Billable Hours sheet tab and select cell A4.

3. Click the Format as Table button [Home tab, Styles group], use Light Gray, Table Style Medium 11.

4. Select cell C6 and type the formula =C5+1 to add one to the date in the previous cell.

5. AutoFill the formula to copy it to row 51.

6. Type .5 Add On in cell E4 and press Enter.

7. Build a formula in cell E5 to add .5 to cell D5 and press Enter to copy the formula. Format results to display two decimal
places and apply All Borders.

8. Copy dates as values.

a. Select cell C5. Cell C5 is a reference to the TODAY function in cell F2.

b. Sort the column by date in newest to oldest order. Because the dates are calculated, the formulas are wrong. The
date that was originally in cell C5 sorted to row 51 and now has a relative reference to cell F48. (Figure 4-113).

Start Date:08/28/202312:00 AMUS/Central Due Date:10/15/202311:59 PMUS/Central End Date:10/15/202311:59 PMUS/Central

Print Info

Student Name: Mohiuddin, Syed

Student
ID:

[email protected]

Username: [email protected]

10/15/23, 10:14 PM Excel 2021 In Practice – Ch 4 Independent Project 4-4 – SIMnet

https://lonestarup.simnetonline.com/sp/assignments/projects/details/8403929 2/3

Figure 4-113 Errors result when
calculated dates are sorted

Figure 4-114 Dates are copied and pasted as values

c. Click the Undo button [Undo group, Home tab] and select cell C5.
The formulas are restored.

d. Select cells C5:C51. You can copy and paste to replace the
formulas with the actual dates.

e. Press Ctrl+C to copy the range to the Clipboard.

f. Select cell C5 and click the arrow with the Paste button [Home tab,
Clipboard group].

g. Choose Values (Figure 4-114).

h. Press Esc to remove the moving border and click cell C5. Formulas
are replaced with the date. Your dates will be different from figures in
this text.

9. Sort the table by date in newest to oldest order. Then sort by
product/service in ascending order.

10. Use Power Query to transform data.

a. Select the Contracts sheet tab and select cell A4.

b. Use the From Text/CSV command to preview the
EllerText-04.txt file downloaded from the Resources link in
the Import Data window.

c. Click Transform Data in the preview window to open
Power Query.

d. Click the Address column label, press Ctrl, and click the
Phone Number label.

e. Click the Remove Columns button [Home tab, Manage
Columns group].

f. Select and remove these columns: Zip, ID, and First
Contact (Figure 4-115).

Figure 4-115 Columns removed in Power Query

g. Scroll the window as needed to see rows 28:43. There are 16 blank rows incorporated in the data. The null text
string in the Hours to Date column is a clue.

h. Click the arrow with the Remove Rows button [Home tab, Reduce Rows group] and choose Remove Bottom
Rows.

i. Type 16 in the Number of rows box and click OK. There are now 27 rows.

j. Click the arrow with the Close & Load button [Home tab, Close group] and load the data to cell A4 in the existing
worksheet.

11. Place a fomula in a table.

a. Select cell F4, type Fees as the header, and press Enter.

b. Select cell F5 and build a formula to multiply the value in cell E5 by 225.

c. Format the values in column F as Currency with zero decimal places.

d. Close the Queries & Connections pane.

12. Sort the data by Fees and then by City, both in ascending order.

10/15/23, 10:14 PM Excel 2021 In Practice – Ch 4 Independent Project 4-4 – SIMnet

https://lonestarup.simnetonline.com/sp/assignments/projects/details/8403929 3/3

Figure 4-116 Completed PivotTable

13. Change the font size for cells A1:A2 to 20 pt. and set the font style to bold.

14. Select cells A1:F2 and center them across the selection.

15. Copy the Billable Hours sheet to the end and name the copy Data.

16. Create a PivotTable.

a. Select cell A5 and click the Table Name box [Table Design tab, Properties group].

b. Name the table tblHours.

c. Click the Summarize with PivotTable button [Table Design tab, Tools group]. The range is identified as tblHours.

d. Verify that New Worksheet is selected and click OK.

e. Name the sheet PivotTable.

17. Manage fields in a PivotTable.

a. Show the Product/Service and Billable fields in the PivotTable.

b. Drag the Billable field from the Choose fields to add to report area below the Sum of Billable field in the Values
area so that it appears twice in the report layout and the pane.

c. Select cell C4 and open the Value Field Settings dialog box.

d. Type Average Hours as the Custom Name, choose Average as the calculation, and set the Number Format to
Number with two decimal places.

e. Open the Value Field Settings dialog box for cell B4 and set its Custom Name to Total Hours and the number
format to Number with two decimal places.

f. Apply Dark Gray, Pivot Style Dark 11 with banded rows and columns.

18. Select the Data sheet tab and copy cells A1:A2 to cell A1 on the PivotTable sheet. Left align cells A1:A2 on the
PivotTable sheet (Figure 4-116).

19. Create subtotals.

a. Copy the Data sheet to the end
and name the copy Subtotals.

b. Select cell A5 and convert the
table to a range. You cannot use
the Subtotal command in a table.

c. Select cells A5:E51 and apply
No Fill [Home tab, Font group].

d. Select cells A4:E4 and change
the font color to Black, Text 1.

e. Use the Subtotal command to
show a SUM for billable and add
on amounts for each
product/service.

f. Apply All Borders to cells
A56:E57.

g. Display only the product/service and grand totals.

20. Save and close the workbook (Figure 4-117).

Figure 4-117 Excel 4-4 completed

21. Upload and save your project file.

22. Submit file for grading.