Key Topics & Core Concepts

Assignment Overview & Deliverables

Eller Systems needs contract and consulting hours organized from multiple sources. You will format billable hours as an Excel table, paste dates as values to prevent sort errors, transform external contract records via Power Query, build a summary PivotTable with dual value metrics, and generate an outline subtotal report.

Step-by-Step Instructions

Step 1: Open Starter & Format Billable Hours Table

  1. Open Eller-04.xlsx and save it as [your initials] EllerSystems.xlsx.
  2. Click the Billable Hours sheet tab and select cell A4.
  3. On the Home tab, in the Styles group, click Format as Table and select Light Gray, Table Style Medium 11. Ensure My table has headers is checked.
  4. In cell C6, enter the formula =C5+1 to increment the date, and AutoFill the formula down through row 51.
  5. Type .5 Add On in cell E4 as the column header.
  6. In cell E5, build a formula to add 0.5 to cell D5 (e.g., =D5+0.5) and press Enter to populate the calculated column. Format column E to display two decimal places and apply All Borders.

Step 2: Copy Dates as Values & Multi-Level Sort

  1. Select cell C5. Note that cell C5 dynamically references the volatile TODAY() function in cell F2. If sorted directly, relative references break into #VALUE! errors.
  2. Select cells C5:C51 and press Ctrl + C to copy the range.
  3. Click the arrow below the Paste button on the Home tab and choose Values (V) to replace the dynamic formulas with static dates. Press Esc to clear the clipboard.
  4. Sort the table by Date in Newest to Oldest order.
  5. Add a second sort level to sort by Product/Service in Ascending (A to Z) order.

Step 3: Power Query ETL on Contracts Data

  1. Switch to the Contracts sheet tab and select cell A4.
  2. On the Data tab, select From Text/CSV and browse to select EllerText-04.txt.
  3. In the preview window, click Transform Data to launch the Power Query Editor.
  4. Hold Ctrl to select the Address and Phone Number columns, then click Remove Columns.
  5. Select and remove the Zip, ID, and First Contact columns. Five columns should remain: First Name, Last Name, City, System/Service, and Hours to Date.
  6. Inspect rows 28:43; notice the 16 trailing blank rows with null values. Click Remove Rows > Remove Bottom Rows, enter 16, and click OK (leaving exactly 27 records).
  7. Click Close & Load To... and load the transformed table into cell A4 of the existing Contracts worksheet.
  8. In cell F4, type Fees as the header. In cell F5, build a formula multiplying Hours to Date by 225 (e.g., =[@[Hours to Date]]*225). Format column F as Currency with zero decimal places.
  9. Sort the Contracts table by Fees (ascending), then by City (ascending). Format cells A1:A2 to 20 pt bold, and center across selection A1:F2.

Step 4: Build & Format the PivotTable

  1. Create a backup copy of the Billable Hours sheet, move it to the end, and name the sheet Data.
  2. Return to the Billable Hours sheet, select any cell in the table, and on the Table Design tab, rename the table to tblHours.
  3. Click Summarize with PivotTable. Verify the table range is tblHours, choose New Worksheet, and name the resulting tab PivotTable.
  4. Drag Product/Service to the Rows area. Drag Billable to the Values area.
  5. Drag the Billable field a second time into the Values area so it appears twice.
  6. Click cell B4 (Sum of Billable), open Value Field Settings, set the Custom Name to Total Hours, and format as Number with two decimal places.
  7. Click cell C4 (second field), open Value Field Settings, change the calculation to Average, set the Custom Name to Average Hours, and format as Number with two decimal places.
  8. Apply Dark Gray, Pivot Style Dark 11 to the PivotTable with Banded Rows and Banded Columns checked.
  9. Copy title cells A1:A2 from the Data sheet to cell A1 of the PivotTable sheet and left align them.

Step 5: Generate Outline Subtotals & Submit

  1. Create another copy of the Data sheet, move it to the end, and name the sheet Subtotals.
  2. Select cell A5 and on the Table Design tab, click Convert to Range. (Subtotals cannot be applied directly inside active Excel tables).
  3. Select cells A5:E51 and set fill to No Fill. Change header text color in A4:E4 to Black, Text 1.
  4. On the Data tab, click Subtotal. At each change in Product/service, use function Sum, and add subtotals to both Billable and .5 Add On.
  5. Apply All Borders to cells A56:E57 (the grand total summary block).
  6. Click outline level 2 on the top-left margin to collapse details and display only the category subtotals and grand totals.
  7. Save the workbook as [your initials] EllerSystems.xlsx. Test via the autograder pre-flight tool at /teaching/ISMG-2050/submit.html, then submit the finalized workbook to Canvas before the cutoff.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated