Criminal Justice Statistics by Elias Nader is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.
Criminal Justice Statistics by Elias Nader is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.
In this lab, we will be working with entering, formatting, and analyzing data in Excel. Please note: this lab is written for a beginner in Excel and meant to introduce you to various capabilities of Excel. You may feel the need to skip through some steps if you are experienced with Excel, and that is fine. Just make sure you understand the content of the lab! Future work will build on these skills.
Part 1 of the lab will utilize data for the number of homicides in Baltimore between 2010 and 2019. All crime data is derived from The Baltimore Sun’s reporting of homicides in the city. The population estimates are derived from the U.S. Census and the American Community survey comes from the Bureau of Justice Statistics report. In this part of the lab, you will be computing the homicide rate for Baltimore.
1. Open up a new Excel worksheet. You will enter a new set of data into Excel in this lab.
a. I will refer to a cell by its name in Excel. For example, Cell B4 is in column B and row 4.
b. A range of cells selected together is defined by its “address,” which is the first cell and last cell in the rage of selected cells. So, if we select all the cells B4, B5, C4, and C5, the address would be “B4:C5” because it contains the cells from B4 to C5.
2. Click cell B4 to make it the active cell.
a. Type Homicide Rates in Baltimore, 2010–2019. Press Enter.
i. Note: The title does not fit in the cell B4 and spreads across several columns. If a label does not fit in a cell, it will display the remaining text in the next cell(s) as long as they are empty. Otherwise, the label is truncated or cut off.
b. Click cell B5 to make it the active cell and type Year.
c. Click cell C5 and type # of homicides.
d. Click cell D5 and type Estimated Population.
3. Notice that the labels truncated (or cut off). However, if you place the cursor on the cell, the full text appears in the formula bar. Don’t worry about the truncation for now; we will fix it later.
4. Enter the data from the table provided below into the worksheet in the appropriate cells of the table. The placement and formatting may not be exactly like the picture below—that is okay. We will format the table later.
a. Some of the population figures will be too large for the standard cell width, so they will be displayed with a series of number signs: ####. The data are there; it’s just that the column is too small to display all of the numbers.
5. You may notice in the picture that the title is separated from the data. To do this, we will insert a row.
a. To insert a row, move the cursor to row number 5. At the beginning of the row is the shaded box with the row number 5 inside. Click on the shaded box 5 and the whole row will become highlighted. Right click on the row and select Insert to add in a new row.
6. Now we will format the cells so that they display the data better. First, center the Year label and the data in those cells.
a. Select the range beginning with the Year label down to the cell containing the last year of 2019 (cell range B6:B16). Under the Home menu at the top of the page, with the range highlighted, select Center from the Alignment group in order to center the text in this group.
7. Now we want to make the titles for the homicides and population columns more readable and we want to see all of the population figures displayed in the cells.
a. First, we will extend the width of these two columns. To do this, select the range of cells from C6:D16. Select Format from the Cells group at the top of the page, and then select Column Width. A box appears with the default width. Press the delete key to clear the box and type 22 to make it the new column width, then select OK.
8. Next, we want to wrap the text of the labels for the homicide and population columns.
a. To do this, click on the cell which has the label for the # of homicides column. Select the Wrap Text button from the Alignment group at the top of the page. Do the same for the label for Estimated Population.
9. Now, make a new column for our calculation of the Homicide Rates.
a. In cell E6, type in Homicide Rate and with the cell active, select Wrap Text. Save your workbook again, and now we’ll move onto something slightly more complicated.
10. Formulas are used to perform numeric calculations such as adding, multiplying, and averaging. Formulas always begin with a prefix of an equal sign (=), and use arithmetic operations (+, -, *, /) to perform calculations: (+) performs addition, (-) performs subtraction, (*) performs multiplication, and (/) performs division.
a. Formulas can contain more than one arithmetic operator and allow for the use of parentheses. In these circumstances, Excel decides which operations to perform first. Multiplication and division are done first, based on a left-to-right flow. Addition and subtraction are performed after that.
11. Formulas often contain cell addresses and ranges. Using a cell address or range name in a formula is called cell referencing. Use cell references to keep your worksheet up-to-date and accurate. If you change a value in a cell, the formula containing that cell reference will automatically be recalculated using the new value.
12. Calculating Rates: Rates are one of the most common formulas in criminal justice. A rate is essentially the number of times something happens per X number of people in the population.
a. Example: 25 motor vehicle thefts per 100,000 people each year.
13. The formula for calculating rates is:
a. Note: this is for rates per 100,000 people, which we will be using in this lab. However, this can be changed to represent any other interval of people we want, like 1,000 or 100.
14. For the year 2010, how do you calculate the Homicide Rate?
homicides per 
people.
15. How would you construct the formula using cell referencing?
16. Now we want to have a rate for each year. Essentially, we want a fourth column of data which contains the rate for each year. This is our Homicide Rate column. Label cell E6 with this label and adjust the width as needed to make sure it can be visible.
a. Make E7 the active cell. In that cell, type: =(c7/d7)*100000
b. Then hit Enter.
c. Using cell referencing in formulas is convenient because instead of having to type a new formula for each additional year, Excel can now transfer the same formula down the rows for the same column, which saves you from writing a new formula for each year.
d. To use cell referencing in formulas, make E7 the active cell. Select Copy from the Clipboard group (the icon is right below the cut icon and looks like two overlapping pieces of paper). [Note: You can also 1) simultaneously hold down the CTRL and C buttons or 2) right click and select Copy].
e. Now select the range E8:E16. When the range is highlighted, select Paste from the Clipboard group.
f. The formulas for the rate for each year are now created, and the rates are displayed. Excel simply updated your original formula for each row, which saves you a lot of time and effort. Press ESC so that the cell you just copied is no longer active.
17. Let’s say we only want one decimal place for the rate.
a. To change the decimal place and round the numbers off, select the range E7:E17.
b. Under the Home category, in the Number group, find the button for decreasing the decimal place (called Decrease Decimal). Keep clicking on the button until only one decimal place is displayed. Save your work.
18. Your table should now look like this:
19. Functions are predetermined worksheet formulas that enable you to do complex calculations easily without writing formulas. Functions begin with the prefix (=). We will use the Sum function to add up the total number of deaths for all the years.
20. Make cell B19 active. Type Total into the cell in order to label this row. Now, make cell C19 active so we can use our first Function.
a. In the top ribbon, select the Formulas tab. In that tab, position the cursor over the Auto-Sum button, the box with the capital Greek letter E ( ∑ ), and click/select it.
b. Auto-Sum sets up the sum function to add values in the cells above the cell pointer.
c. Here the formula =SUM (C7:C18) appears in the cell, which is the full column under the label. Verify this range and press Enter. The results now appear in the cell: 2,743 is our total number of homicides from 2010–2019.
21. Another function Excel provides is the averaging function, which would allow you, for example, to calculate the average population for all the years. Let’s try that.
a. Make cell B20 active and type Average into the cell to label this row.
b. Instead of typing in a formula to do an average, you can use the Function Wizard for fast and accurate calculation.
c. To use the Function Wizard, make the D20 cell active (we’ll be replicating something similar what we just did for Sum).
d. Now, we will want to calculate the average for this column. To do this, go back to the Auto Sum box but now click on the little arrow next to it. You will get a few options for formulas. Select Average.
e. Here the formula =AVERAGE(D7:D19) appears in the cell, which is the full column under the label. Verify this range and press Enter. The results now appear in the cell: 615,536 is our average estimated population per year.
22. We’re also interested in finding the percent change in the homicide rate from 2010 to 2019 (note that this is for the entire time frame, not from year to year). What is the formula for calculating the percent change from 2010 to 2019?
How would you construct the formula using the Excel cell reference method?
23. Fortunately, Excel can perform complex operations involving numerous steps; in this case, subtraction, multiplication, and division to calculate the percent change.
a. Click cell B21 to make it active and type % Change in order to label the row.
b. Select cell E21. Based on the percent change formula, we need to subtract the most recent date (2019, time 2) and the first date (2010, time 1) and then divide it from the first data point we have (2010, time 1). We also have to make sure to follow order of operations; if we do not use parentheses, Excel will give us a meaningless answer.
c. As such, we need to type in cell E21: =(E16-E7)/E7*100
d. Then hit enter. This calculates the percent change in the homicide rate from 2010 to 2019.
e. Here, this answer is 63.3%, thus we had a 63.3 % increase in homicide rate from 2010–2019.
24. The final results should look like the table below. Please check your work to make sure the numbers and equations are the same!
In Lab 1, Part 2 we will work with formatting and analyzing data in Excel. Part 2 of the lab will compare the number of violent crimes in Baltimore between 2010 and 2016. All crime data is derived from the Uniform Crime Report (UCR) and the 2018 Baltimore Police Department Staffing Study Report.
1. Download and open up the following Microsoft Excel worksheet: Lab 1 Part 2 Worksheet
2. Let’s examine how the amount of violent crime in total during the 2010–2016 timeframe and what percentage of the total was within each category.
3. To do this, first we will calculate the sum for each crime type across the 2010–2016 timeframe. Label cell B15 with the name Total per Crime.
a. To calculate the totals, we will create a formula in excel. These always start with an equal sign (=).
b. For a total, we will need to sum (or add) all of the numbers.
c. The formula is: =Sum(firstcell : last cell)—this formula will sum all of the numbers between the two cells in the column or row.
d. Type this formula into the Total cell for each government entity column.
e. For example, under Murder in cell C15, we will type: =Sum(C5 : C14).
f. Repeat this for Rape, Robbery, and Agg. Assault.
4. Next, create a new column title in G6 and call it Yearly Totals. Now, we will sum the totals for each year across the four crime types.
a. So, in cell G7, we will sum the four values for 2010 (be sure not to include the cell with the year in the formula!).
b. In cell G7, you should type: =Sum(C7 : F7)
c. You should then repeat this for each year. This may seem tedious, so shortcuts are available. When you copy a formula and paste it in a different cell, Excel applies this formula to equivalent cells in relation to the cell you are pasting in.
d. So, if we copy the formula from cell G7 (which is summing all the values in our Row 7), and paste it into G8, it will transfer the formula to equal the cells in row 8. Try the copy and paste shortcut to fill in the values for the rest of the years.
e. Finally, we want to calculate an overall total for the number of violent crimes in the entire 2010–2016 time span. Thus, you will be calculating the sum of the rows for each year. In cell G15, sum all of the yearly totals of G7–G13 to get a total value.
i. This gives us the total of the yearly totals.
5. At this point, your worksheet should look like this:
6. Now, let’s calculate the percentage of the total number of violent crimes for each of the four crime types.
a. In row 16, cells C16–F16, use a formula to calculate the percentage from each column from the grand total of crime over the 2010–2016 time span. Thus, label cell B16 with the name % of total crime. The formula will be:
b. Use the cell reference in constructing the formula (example: =c15/g15*100) for each percentage you calculate.
i. **NOTE**You will not be able to cut and paste this formula across the rows, since G15 will be your denominator in each one and Excel will automatically replace it if you cut and paste the equation.
c. Thus, you must retype the formula for each percentage. Once you have calculated these percentages, decrease the number of decimals so you have one decimal place.
i. You can do this by going to the Number group under Home, find the button for decreasing the decimal place (called Decrease Decimal). Keep clicking on the button until no decimal place is displayed.
d. Below is what the final result of the table should look like. Check your numbers against the table to make sure it is accurate.
For this portion of the lab, you will start with a blank Excel sheet and enter the data as instructed in the Lab 1 Part 1 Instructions.
For this portion of the lab, you will start with the Excel sheet below and complete it as instructed in the Lab 1 Part 2 instructions.
Math and Statistics Guides from UBalt’s Math & Statistics Center