Excel

1 Lab 1: Using Excel for Criminal Justice Time Series Data In this lab, you will design a spreadsheet that includes information about the number of murder and nonnegligent manslaughter cases in the U.S. from 1994 through 2014. Part I: Entering Labels and Data Labels are used to identify the data in the rows and columns of a worksheet. They are also used to make your worksheet readable. Labels can contain text and numerical information not used in calculations, such as dates, times, or address numbers. Labels are aligned to the left by default. 1. Start Excel and make sure you have an empty workbook in the Excel worksheet window. 2. Click cell B4 to make it the active cell. 3. Type Murder and Nonnegligent Manslaughter in the U.S. from 1994- 2014. Press Enter. 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) if they are empty. Otherwise, the label is truncated or cut off. 4. Click cell A5 to make it the active cell. 5. Type Year, then press Enter. 6. Click cell B5 and type Murder and Nonnegligent Manslaughter, then press Enter. 7. Click cell C5 and type Population in the U.S., then press Enter. 8. Notice the label for cell B5 is truncated. However, if you place the cursor on the cell, the full text appears in the formula bar. Do not worry about the truncation for now, we will fix it later. 9. Enter the data from the table provided on the next page into the worksheet in the appropriate cells. 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: ####. Do not panic when you see this, you can fix it later! The data are there. Also, make sure you press Enter after typing the contents of each cell. 10.When you are done, save your workbook. 2 Murder and Nonnegligent Manslaughter in the U.S. from 1994-2014 Year Murder and Nonnegligent Manslaughter Population in the U.S. 1994 23,326 260,327,021 1995 21,606 262,803,276 1996 19,645 265,228,572 1997 18,208 267,783,607 1998 16,974 270,248,003 1999 15,522 272,690,813 2000 15,586 281,421,906 2001 16,037 285,317,559 2002 16,229 287,973,924 2003 16,528 290,788,976 2004 16,148 293,656,842 2005 16,740 296,507,061 2006 17,309 299,398,484 2007 17,128 301,621,157 2008 16,465 304,059,724 2009 15,399 307,006,550 2010 14,722 309,330,219 2011 14,661 311,587,816 2012 14,866 313,873,685 2013 14,319 316,497,531 2014 14,249 318,857,056 *Note: The 168 murders and nonnegligent homicides that occurred because of the bombing of the Alfred P. Murrah Federal Building in Oklahoma City in 1995 (i.e., the Oklahoma City bombing) are included in the national estimate. The 2,823 murders and nonnegligent homicides that occurred because of the events of September 11, 2001 (i.e., 9/11), are not included in the national estimates. Part II: Formatting the Worksheet The way the spreadsheet first appears is confusing because the labels are truncated and some of the numbers are too large for the standard cell width. We can format our worksheet to fix these minor problems, and we can also make the title stand out more. 11.To make the title stand out, click on cell A4 to make the title cell active. Move the cursor to the Font group. Calibri is the name of the default font for Excel worksheets. A box in this group has the number 11 displayed; this is the font size. Click the down arrow next to the font size box and 3 select 12-point size font. One of the boxes below the font size is the Bold button B. Click the bold button and the title will now appear in boldface. (You can also simultaneously hold down the CONTROL (Ctrl) and B button on your keyboard to active the bold feature). Change the font size to 12 and select the bold option for each column label. 12.To separate the title from the worksheet data, we will insert a row. To do this, move the cursor to row number 5. At the beginning of the row is a shaded box with the number 5 inside. Click on the shaded box 5 and the whole row will become highlighted. Move the cursor to the Cells group and select Insert, and then select Insert Sheet Rows. A new row will be inserted. (You can also right click on box 5 and select Insert). 13.Next, we want to move the data so that it falls under the title. To do this, we need to move the data over by one cell. Before we can move the data, we must select the range we want to move. Ranges are any group of more than one cell. To select a range, left-click the first cell (containing the label Year) and drag the cursor (while holding down the left mouse button) down to the last year and over to include the murder and population cells. All the data cells and labels should be highlighted, so you can now release the mouse button. The range is defined by “addresses,” which are the first and last cells in the range. Here the address is A6:C27. Remember this address concept—it will be important later! 14.With the range still highlighted, from the Clipboard group select Cut (the scissors icon). (You can also simultaneously hold down the CONTROL and X buttons on your keyboard). The range will be surrounded by moving lines to indicate that it is active. Click on the cell B6, which is where you want the data to move (note: you only need to specify the first cell of the area or range where you want the section to be pasted). Select Paste (looks like a clipboard and piece of paper) from the Clipboard group. (You can also simultaneously hold down the CONTROL and V buttons on your keyboard). The data should have moved so it all appears under the title. Should you make a mistake, select the Undo option (note: you can use Undo if you use a formatting command, but make sure you utilize this option right after you make a mistake, since Excel only remembers the last procedure you did). The Undo option is the leftcurving arrow in the Quick Access Toolbar located in the upper-left corner of the window (all the way at the top of the screen in the green menu bar). (You can also simultaneously hold down the CONTROL and Z buttons on your keyboard). 15.Now we will format the cells, so they display the data better. First, we want to center the Year label and the years. Select the range beginning with the year label down to the cell containing the last year 2014 (cell 4 range B6:B27). With the range highlighted, select Center Text from the Alignment group. 16.Now we want to make the titles for the murder and population columns more legible and we want to see all the population figures displayed in the cells. First, we will extend the width of these two columns. To do this, select the range of cells from C6:D27. Select Format from the Cells group, and then select Column Width. A box appears and type 15 to make it the new column width, then select OK. Note: If your program does not allow you to change the column width to 15 (e.g., if you are using a Mac), it is not a big deal. This step is strictly to make sure the full population values are visible. Do what works! You can also increase the width of a column but hovering over the column headers at the top of the spreadsheet (labeled alphabetically). When you hover in between two columns (over the thin line that separates them) (e.g., between headers ‘C’ and ‘D’), your cursor should change to a vertical line with an arrow pointing off of it in both directions (left and right). If you left-click and drag your mouse to the left or right, you can increase/decrease the width of the column. 17.Next, we want to wrap the text of the labels for the murder and population columns. To do this, click on the cell which has the label for the murder column. Select the Wrap Text button from the Alignment group (is the icon with the letters ‘ab’ next to each other and an arrow pointing at the letter ‘c’). Now do the same for the label for the population column. Finally, highlight both column labels (cells C6:D6) and center the titles. 18.You can further format your data by including commas to increase the legibility of your data. Select cells C7:D27. Under the Alignment group, select the arrow down in the bottom r
ight corner, which will pop up a Dialog Box with additional formatting options. Select the Number tab, and then select the Number category. For these two columns, make sure the decimal places option is set to 0. You can insert a comma, by checking off the box next to “Use 1000 Separator (,)”. Once done, click the Ok button. You can also access the Formatting options by selecting the cells you wish to format, right-clicking your mouse, and selecting the “Formatting Cells…” option. 19.Save your workbook again before we will move onto something slightly more complicated. Part III: Entering Formulas 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 5 arithmetic operations (+, -, *, /) to perform calculations: (+) performs addition, (-) performs subtraction, (*) performs multiplication, and (/) performs division. Formulas often contain cell addresses and ranges. Using a cell address or range name in a formula is called cell referencing. Referencing cells keeps 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. Formulas can contain more than one arithmetic operator. 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 second. 20.Now we need to have a rate for each year. In cell E6, create a column title (using the same format as your other column headers – 12-point font, bold, centered, text wrapped) labeled, Rate of Murder and Nonnegligent Homicides per 100,000 People in the U.S. Essentially we want a fourth column of data which contains the rate for each year. To do this, make E7 the active cell. In that cell, type: =c7/d7*100000, and then hit Enter. Using cell referencing in formulas is convenient because instead of having to type a new formula for each additional year, Excel can now copy the same formula down the rows for the same column, which saves you from writing a new formula for each year. To do this, make E7 the active cell. Select Copy from the Clipboard group (the icon is right below the cut icon (looks like scissors) and looks like two overlapping pieces of paper). (You can also simultaneously hold down the CONTROL and C buttons on your keyboard). Now select the range E8:E27. When the range is highlighted, select Paste from the Clipboard group. The formulas for the rate for each year are now created, and the rates are displayed. Excel simply updated your original formula by 1 row, which saves you a lot of time and effort. Press the ESC button on your keyboard, so the cell you just copied is no longer active. There is also another way to copy a formula in Excel. First hit the Undo button removing the rates you just calculated for cells E8- E27. Press the ESC button on your keyboard again, so cell E7 is no longer active. Click on cell E7 to active the cell. Once the cell is activated, you should see a square in the bottom right corner of the cell. Hover your mouse over this square and you will see it change to a thin, black plus sign. Left-click on the square, hold down the button, and drag your mouse all the way down to cell E27. You have now successfully copied the rate formula for each year! 21.We only want two decimal places for the rate. To change the decimal place and round the numbers off, select the range E7:E27. In the Number group, find the button for decreasing the decimal place (the one with the arrow pointing to the right). Keep clicking on the button until only two decimal places are displayed. 6 22.Save your work. Part IV: Using Excel Functions Functions are predetermined worksheet formulas that enable you to do complex calculations easily without writing formulas. Functions also begin with the equal prefix (=). We will use the Sum function to add up the total number of murders for all the years. 23.Make cell C28 active. Position the cursor over the Auto-Sum button, which is in the Cells group and is the capital Greek letter E (∑), then click on it. Auto-Sum sets up the sum function to add values in the cells above the cell pointer. Here the formula = SUM(C7:C27) appears in the formula. Verify this range and press Enter. The results now appear in the cell. 24.Another function Excel provides is the average function, which would allow you, for example, to calculate the average population for all the years. Let us try that! 25.Instead of typing in a formula to calculate the average, you can use the Function Wizard for a fast and accurate calculation. To do this, make cell D28 active. Place your cursor on the Function Wizard button, which is that little fx below all the groups next to the formula bar (the big white bar where what you type shows up). Click on that, and a dialog box will appear. In the “Select a function” box, choose the Average function, and then click OK. You are then prompted with a series of number boxes. Here, we want the average population over all the years. In the “Number 1” box, type D7:D27, which is the range of cells you want to average. Select OK and the average population appears in the cell. Again, you should have two decimal places for this value. 26.Make sure you go back and insert a comma for the total number of murders and the average population values that you just calculated. Part IV: Viewing the Document Before Printing If you need to print out your spreadsheet, it is always advisable to view it ahead of time. 27.Select the File tab and select Print. A layout of the spreadsheet as it will print appears on the right side of the screen. This way you can make sure all the data are visible and nothing looks out of place! 28.If everything looks good, you can close the print preview and save your workbook. 7 If you did everything correctly, here is what the worksheet should look like: ALT = Excel spreadsheet showing the Murder and Nonnegligent Homicide data for the U.S. from 1994-2014. Contains the year, number of murders and nonnegligent homicides per year (and total), U.S. population per year (and average), and the rate of murders and nonnegligent homicides per year. Lab 1 Assignment: Please submit your final Excel spreadsheet with the data you have just entered. Please be aware, if you simply copy the information from the screenshot from above and you do not calculate the formulas yourself, I will be able to tell. If this happens you will not receive credit for this assignment. Here are the key concepts and what you should have learned in this lab: ▪ Entering and formatting (e.g., text wrapping) labels for your worksheet ▪ Entering and formatting data (e.g., cell width) for your worksheet 8 ▪ How to insert rows ▪ How to cut and paste data, as well as formulas, within a worksheet ▪ How to enter formulas for calculating a crime rate ▪ How to use the auto-sum function for calculating the sum of column data ▪ How to use the function wizard to calculate an average ▪ How to view a spreadsheet before printing it That is not too shabby for your first lab! Please keep all these procedures in mind; you will need to use them again for the next lab and for the first assignment.