Use this exercise to practice your skills and to learn what to expect on the Spreadsheet component of the CIL exam. The format of this exercise and the types of skills you'll be asked to demonstrate are very similar to the exam. Use the detailed scoring rubric to see how points are assigned for each task you'll be asked to complete.
During the exam, you'll be given a printed set of instructions with checkboxes. Above, we've provided a sample that you can print out. Use the checkboxes to help you remember to complete each step.
Always take the time to read through the instructions before you begin. Doing so can save you time and prevent you from making some mistakes.
This exercise is not timed. When you take this component of the CIL exam, you will be given 2 hours to finish. You may want to time yourself as you do this exercise to find out if you can complete it within 2 hours.
During the exam, complete as many of the listed tasks as you can. If you can't complete a task, skip it and move on to the next instruction. If time permits, return to uncompleted tasks later.
Save your work often.
During the exam, you will be allowed to use the spreadsheet program's help menus, but you will not be permitted to get assistance from another person in completing the exam.
You have been asked by your employer to create a spreadsheet and a chart to show the number of serious traffic accidents in the state of Hawaii over a period of years. You've found the raw statistics at the State of Hawaii Government website, but now you need to work with the data. In particular, your boss wants you to create a chart to show:
For accidents resulting in fatalities, what percentage involved alcohol and what percentage did not
For accidents resulting in injuries, what percentage involved alcohol and what percentage did not
For accidents resulting only in property damage, what percentage involved alcohol and what percentage did not
Opening the Program and File
Click Here to download the file AutoAccidents.xls. Use your spreadsheet program (such as Microsoft Excel) to open the file.
(Note: during the exam, you won't need to download any files from the Internet. Instead, you'll open the required files from a thumb drive on the testing computer.)
Use the "Save As" command to save the file onto a thumb drive or your Desktop adding your First and Last Name to the beginning of the file name, like this:
(For example: Harry Potter's file would be saved as "G:/HarryPotteraccidents.xls")
Working with Table 1
Delete columns F (blank) andG (year data) in order to move the two sets of statistics together.
Type the word Total in cell A21.
Now you need to get to totals for each year.
Insert a function in cell B21 to get the sum of the number of crashes that occurred in the reported years.
Copy and paste the function from B21 in order to find totals in each remaining column of data in the table.
To avoid confusing the number of accidents in each year with the total of accidents over the whole range of years, change the labels in cells B4 and F4 from Total to Numbers
To make the numbers easier to read, format all the numbers in the range B5:I20 so that they have a comma separator at the thousand's place. (Example: 3,789 instead of 3789).
The data in this table has different sections. To make it easier to tell what each section is about, Merge and Center the following ranges so that the labels are centered over the data they correspond to:
B2:I2 B3:E3 F3:I3
Now, you'll give the entire table a title: Edit the text in B2:I2 to make it read:
MAJOR TRAFFIC CRASHES IN HAWAII
In order to visually separate data on all crashes from those that were alcohol-related:
create an outline border around the cells in the range B3:E21, and
create another outline border around cells in the range F3:I21
To increase readability, make all of the section labels in the B2:I4 range Bold.
To make the table look neat and tidy, adjust the width of column A through column I to fit to their contents.
Working with Table 2
You'll use Table 2 (the yellow shaded table to the right of table 1) to look more closely at crashes that involved alcohol vs. crashes that did not involve alcohol. First, you'll need to determine what information should go into the cells of this chart.
To begin, use Cell References to fill in the numbers for the alcohol related crashes. These figures already exist in Table 1. For example, the number of fatal crashes involving alcohol should be found in cell G21. Use a Cell Reference to display this figure in cell L3.
Also reference the figures for the number of alcohol-related crashes
with injuries, and
with only damage.
Table 1 gives figures for ALL crashes (whether or not they involved alcohol) and for Alcohol related crashes. It doesn't tell you about the number of crashes that did NOT involve alcohol. You can figure that out in Table 2 using cell references and a formula
Create a formula in cell L4 that takes the total number of ALL fatal crashes (found in C21) and subtracts the number of those involving alcohol (found in G21). The resulting number is the number of fatal crashes that did not involve alcohol.
Using the same method, fill in the remaining two cells in Table 2 to show:
the number of crashes with injuries that did not involve alcohol
the number of crashes with only damage that did not involve alcohol
Creating a Chart
Now make a chart using data from the range K2:N4:
Make a 100% Stacked Column chart with a 3D effect.
Title the chart "Alcohol vs Non Alcohol Related Crashes", and
place the legend at the bottom.
For Excel 2007 users format the chart with Chart Style 1. The chart won’t print out correctly if any of the other styles are used.
Place the chart:
directly under Table 2 and directly to the right of Table 1
resize the chart to fit inside the empty blue box
Change the Page Setup so that:
it has a landscape orientation
it is scaled to fit on one page by one page
it has 1 inch margins all around (left, right, top and bottom)
Customize the Header so that it has:
your full name in the left section, and
the current date in the right section
Use print preview to verify that everything is correct.
Your document should look something like this:
Print the worksheet.
Resave your file one last time onto the thumb drive or Desktop.
Exit the spreadsheet program.
Congratulations! You're finished with the exercise.