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:002: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,
