Blog

Practice: Week 3 Excel Basics

It’s now time to assess your progress in the class. Download the file below and open it in Excel. Click on the “Enable Content” button to make the assignment tools available. Navigate to the “Assignment” tab to locate the assignment options. Click on “Instruction Sheet” to see a description of the assignment. The “Task Guide” button will display one task at a time as you work though the assignment. The “Submit” button will apply the grading engine to your assignment and record your score. Finally, the “View Report” button will display feedback from the last time you submitted the assignment.

Test Your Skills assignments are designed to gauge your progress in the class. Unlike practice problems, they are not accompanied by step-by-step instructions beyond the task descriptions provided in the task guide. Everything you need to know to complete each task can be found in the readings for this unit or in prior units of the textbook. Good luck and have fun!

Answer the following questions:

1. Insert a new column between columns B and C. Label the new column (cell C3) “Record ID”
2. Use the “Fill” feature of Excel to insert the numbers 1 to 47 in cells C4:C50.
3. Make the table headers (range B3:J3) bold
4. Center the table headers (range B3:J3) horizontally
5. Make cell B2 bold
6. Italizice cell B2
7. Make cell G2 bold
8. Italizice cell G2
9. Merge and center cell G2 across columns G:J
10. Change the format of the date range (G4:G73) to “Short Date”
11. Resize column B to a width of 28.0 (2.4 inches)
12. Resize column C to 8.71 (.79 inches)
13. Resize column D to 16.57 (1.44 inches)
14. Resize column E to 29.43 (2.53 inches) -notice this is smaller than needed to fit all cell contents
15. Resize column F to 15.14 (1.33 inches)
16. Resize column G to 11.0 (.99 inches)
17. Resize column H to 25.86 (2.22 inches)
18. Resize column I to 29.14 (2.5 inches)
19. Resize column J to 13.71 (1.21 inches)
20. Center the cells in the table (range B4:J50) horizontally
21. Notice that both the “Place” and “Country” fields display the country where the record was established. Hide column I (“Place”) to avoid redundency in the table.
22. Place a “Thick Bottom Border” (from the border drop-down menu) under the data labels (range B3:J3)
23. Place a dotted line border (the option directly under “None” in the “Border” tab of the “Format Cells” dialog window) under each subsequent row in the table.
24. Fill rows 2 and 3 of the table (range B2:J3) using any color (not “No Fill”) from the “Fill Color” drop-down menu.
25. Use the “Wrap Text” feature in Excel to display all of the contents of cells E46:E50. Resize the rows if necessary to fit the contents of the cells.
26. Set the print area of the worksheet to include only the area of the data table (Range B2:J50)
27. Set the left and right print margins of the worksheet to be .5 inches
28. Set column B as “Columns to repeat at left” page titles
29. Notice that there is a blank worksheet in the workbook named “World Records Copied”. Navigate to that worksheet and select cell A1 if it is not already selected.
30. Copy the records table without row 2 (range B3:J50 from the “World Records” worksheet) to the “World Records Copied” worksheet using the “Copy” and “Paste” features of Excel. Paste the range starting in cell B3 of the “World Records Copied” worksheet.
31. Now, sort the table on the “World Records Sorted” worksheet by Country in ascending order (A to Z), then sort the table by ‘Record Date’ in descending order (Newest to Oldest).
32. Filter the table on the “World Records Sorted” worksheet to display only athletes whose “Nationality” listed as “Kenya” or “Jamaica”