excel assignment

CGS1030

Practical Exercise

Read through the instructions before starting the exercise.

1. Save the document as yourname_P4

2. Enter the data below into an Excel Spreadsheet. Be sure to keep the same columns and rows. DO NOT ENTER THE ROW NUMBERS OR COLUMN LETTERS.

A B C

1 Weekday Weekend

2 Time of Day Number Number

3 12:00–2:59 AM 148 486

4 3:00-5:59 AM 124 102

5 6:00-8:59 AM 246 130

6 9:00-11:59 AM 312 95

7 12:00-2:59 PM 405 123

8 3:00-5:59 PM 815 195

9 6:00-8:59 PM 711 782

10 9:00-11:59 PM 585 495

3. Enter the row title Total in cell A11

4. Insert a column between column B and C

5. In cell C2 enter the column title Percent

6. In cell E2 enter the column title Percent

7. In cell F1 enter the column title Total

8. In cell F2 enter the column title Number

9. In cell G2 enter the column title Percent

CALCULATIONS

10. In Row 11 calculate the following:

 the total number of accidents that occurred during weekdays and

on weekends

 Include the total in the TOTALS row

11. In columns C and E calculate the following:

 Calculate the percent of accidents during the week and on

weekends

 You MUST use ABSOLUTE CELL REFERENCE for these

calculations

Continued on Next page

12. In columns F and G calculate the following:

 Calculate the TOTAL amount of accidents and the percentage

during the specified times

 You MUST use ABSOLUTE CELL REFERENCE for the

calculations of the percentage

i. You CANNOT add percentages for a total

 Include the total in the TOTALS row

13. Format cells in columns C, E and G so that they show the

percent sign and only one decimal place.

14. Insert 2 rows above the table.

15. At cell A1 enter the title: Car Accidents at the Intersection of 5th

and Grand

16. At call A2 enter the subtitle: Day of Week

CHARTING

17. Create a 3-D Column Chart showing the percentages of ALL accidents by time of day

18. Move the Graph to a new sheet  Call the new sheet Accident Percent

 Change the color of your columns to Orange  Add a title to the Chart

i. You create one  Add Axis Titles to both Axis’

 Show data Labels  Color the tab BLUE

FORMATTING

19. Center the Title and Subtitle across the table

20. Merge and Center the following:

 Cells B3 and C3

 Cells D3 and E3

 Cells F3 and G3

21. Use borders to complete the rest of the formatting to the table

CONDITIONAL FORMATTING

22. Use Conditional formatting to highlight the total Percentage that

is greater than 10%

 Color the cells Dark Blue with a Bold White colored font

23. Change the Worksheet Name to Car Accidents

24. Color the tab: RED

Continued on Next page

FINISHING TOUCHES

25. Add your name and student ID number to the header

26. Change the document properties - Author, Subject and TAGS (keywords – use a minimum of 3

words separated by commas). o Tags should NOT be your initials, Excel, exercise, …