Information System

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 8/29/17 Page 1

USING MICROSOFT EXCEL 2016 Independent Project 4-4

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

Skills Covered in This Project Import a text file. Use AutoFilters. Sort data by multiple columns. Create a PivotTable.

Format fields in a PivotTable. Use the Subtotal command. Format data in an Excel table. Sort data in an Excel table.

IMPORTANT: Download the resource file(s) needed for this project from the Resources link. Be sure to extract the

file(s) after downloading the resources zipped folder. Please visit SIMnet Instant Help for step-by-step instructions.

1. Open the EllerSoftware-04 start file. Click the Enable Editing button. 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. Import the EllerSoftware-04.txt file downloaded from the Resources link, beginning in cell A4 on the

Contracts sheet. The text file has a header row and is tab-delimited.

3. Format the values in column H as Currency with zero decimal places.

4. Click cell G4 and show AutoFilter arrows.

5. Use the AutoFilter arrow to sort by date with the earliest date first. Then use the AutoFilter arrow to

sort by product/service name in ascending order.

6. Filter the Date column to show only contracts for September using the All Dates in the Period

option.

7. Select cells A1:H2 and press Ctrl+1 to open the Format Cells dialog box. On the Alignment tab,

choose Center Across Selection.

8. Change the font size for cells A1:H2 to 20 pt (Figure 4-98).

9. Copy the Contracts sheet to the end and name the copy Data.

10. Clear the date filter and hide the AutoFilter arrows.

11. Select cell A5 on the Data worksheet and click the PivotTable button [Insert tab] to create a blank

PivotTable layout on its own sheet named PivotTable.

Download start file

Resources

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 8/29/17 Page 2

USING MICROSOFT EXCEL 2016 Independent Project 4-4

12. Show the Product/Service and Contract fields in the PivotTable.

13. Drag the Contract field from the Choose fields to add to report area below the Sum of Contract

field in the Values area so that it appears twice in the report layout and the pane (Figure 4-99).

14. Select cell C4 and click the Field Settings button [PivotTable Tools Analyze tab, Active Fields

group]. Type Average Contract as the Custom Name, choose Average as the calculation, and set the Number Format to Currency with zero decimal places.

15. Select cell B4 and set its Custom Name to Total Contracts and the number format to Currency with zero decimal places.

16. Apply Brown, Pivot Style Dark 3, or Pivot

Style Dark 3.

17. Select the Data sheet tab and copy cells

A1:A2. Paste them in cell A1 on the

PivotTable sheet. Set Align Left for both cells

and 16 pt as the font size (Figure 4-100).

18. Copy the Data sheet to the end and name

the copy Subtotals.

19. Select cell D5 and sort by City in A to Z order.

20. Use the Subtotal command to show a SUM

for the contract amounts for each city.

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

22. Click the Format as Table button [Home tab, Styles group], use Orange, Table Style Medium 10, or

Table Style Medium 10 and remove the data connections.

23. Type 5% Add On in cell E4 and press Enter.

24. Build a formula in cell E5 to multiply cell D5 by 105% and press Enter to fill the formula down the

column.

25. Select cells A1:E2 and center them across the selection.

26. Use the AutoFilter arrows to sort first by date in oldest to newest order and then by client name in

ascending order.

Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 8/29/17 Page 3

USING MICROSOFT EXCEL 2016 Independent Project 4-4

27. Save and close the workbook (Figure 4-

101).

28. Upload and save your project file.

29. Submit project for grading.

Upload &

Save

Grade my

Project