MIS 180 SIMNET ASSIGNMENTS

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 1

USING MICROSOFT EXCEL 2013 Independent Project 4-4

Independent Project 4-4 Eller Software Services has received updated client information with sales numbers. You import the data into the worksheet, sort and filter it, and apply conditional formatting. You also format the data as an Excel table and create a PivotTable.

Skills Covered in This Project  Import a text file.

 Sort data

 Use an AutoFilter.

 Filter data by cell color.

 Copy, name, and move a worksheet.

 Use the Subtotal command.

 Apply Conditional Formatting.

 Clear filters and conditional formatting.

 Create an Excel table.

 Create a PivotTable.

 Protect a worksheet with a password.

1. Open the EllerSales-04 start file. If the workbook opens in Protected View, click the Enable Editing button

in the Message Bar at the top of the workbook 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, and save it.

3. Import the ClientInfo-04.txt file in cell A4. The text file is tab-delimited.

4. Unhide row 10. Fix the phone number in cell C12.

5. Set Conditional Formatting to show cells I5:I13 with Yellow Fill with Dark Yellow Text for values that

are less than 1500.

6. Sort the data first by Product/Service in A to Z order and next by Client Name in A to Z order

(Figure 4-98).

7. Display the AutoFilter buttons and filter the Gross Sales data by color to show only those cells with yellow fill.

8. Copy the MN Clients sheet, name the copied sheet Subtotals, and move it to the right of the MN Clients

sheet.

9. Clear the filter and the conditional formatting on the Subtotals sheet.

10. Sort by City in A to Z order if necessary.

4-98 Rows sorted by "Product/Service" and then by "Client Name"

Step 1

Download start file

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 2

USING MICROSOFT EXCEL 2013 Independent Project 4-4

11. Use the Subtotal command to show a SUM function in the Gross Sales column at each change

in City (Figure 4-99).

12. Copy the Subtotals worksheet, name the copied sheet Table Data, and move it to the right of the Subtotals

sheet.

13. Remove all subtotals on the Table Data sheet.

14. Format cells A4:I13 as an Excel table with Table Style Medium 9. The external connection is from the imported

data; click Yes in the message box.

15. Display a Total Row in the table (Figure 4-100).

16. Create a PivotTable based on cells A4:I13 in the Table Data sheet on its own sheet named PivotTable.

17. Arrange the fields in the PivotTable like this: City (FILTERS area), Product/Service (ROWS area), and Gross Sales

(VALUES area).

18. Filter the PivotTable to display only the data for Bemidji and Brainerd.

19. Apply Currency style to B4:B7.

20. Insert a Clustered Column PivotChart on the PivotTable sheet.

4-100 Table with total row

4-99 Subtotal results for gross sales at each change in city

Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 2/4/15 Page 3

USING MICROSOFT EXCEL 2013 Independent Project 4-4

21. Delete the legend from the PivotChart and insert the following chart title: Bemidji & Brainerd Gross Sales

(Figure 4-101).

22. Protect the Table Data sheet with the password PasswordC.

23. Save and close the workbook.

24. Upload and save your project file.

25. Submit project for grading.

4-101 PivotTable with PivotChart results

Step 2

Upload & Save

Step 3

Grade my Project