This lesson will be an introduction to Excel. I imagine most of you will have had some introduction to spreadsheets in your schooling, so this should be pretty easy to start. You will be Excel whizzes by the end of this course though. (As well as have some introduction to databases in Access, as well as SQL to query databases.)
Why are we using Excel for the course? It is easily the most widely used tool among crime analysts (and analysts in the private sector as well). To do more advance statistical analysis, a statistical program is likely necessary (like R, Stata, SAS, SPSS, etc.). You can do alot though with Excel, so I think it has the most value-added to teach to undergrads.
Here I will walk through making a table and a graph of some Uniform Crime Reporting Data. On ELearning, download the LocalCrimeJurisbyJuris.xlsx file to your local computer. (Or download it from this web link.) If you have a laptop, I suggest bringing it in so you can use two computers, one to view the walkthrough and another to do the actual operations.
I downloaded these Dallas PD stats from the UCR data tool. For another good UCR crime trend resource, check out this web-app as well.
In the end, we will be making a table and a chart that looks like this:
Very similar to typical CompStat like reports many crime analysts make.
First, find where you saved your Excel file. Then double click the xlsx file, and it should open up in Excel.
Then you should see a sheet that looks something like this:
For a quick walkthrough of the components of an Excel spreadsheet. First, the cells are designated by a letter for the column, and a number for the row. Cell H12 is the robbery rate per 100,000 population in 1985 in Dallas, which was 913.8.
Select cell P11, next we are going to use a formula. After selecting cell P11, type an equal sign. When you type an equal sign, Excel knows you are entering in a formula. Then type in the formula =(H11/100000)*C11, and hit enter. Once you are done, it should equal 6050 and change in the P11 cell. You can see the formula you typed-in in the formula bar on top as well, although now the cell only shows the final calculation.
What this calculation does it turns the crime rate into a crime count. That should not be an integer value, so lets round it to the nearest integer using the ROUND function. Select the formula bar, and update our formula to be =ROUND( (H11/100000)*C11, 0 ) and hit enter.
In the bottom right corner of P11, you will see a little square green box when you have selected the cell. Click that box, and drag it down to P40. You see it filled in the formula. If you select P12, and check out the formula, instead of referencing the cells H11 and C11, it now references H12 and C12.
Select P11 again, and this time when selecting the lower right corner drag it to the right, into cell Q11. That does not look right, only 10 aggravated assaults?
This is because it is not referencing the population estimate column anymore, C11, but instead went to the right by 1, and is now referencing D11. Delete Q11, and then go back to P11. To stop that going right behavior, we always want it to reference the C column (but still go down the rows), we are going to change the P11 formula to =ROUND( (H11/100000)*$C11, 0 ).
Now drag P11 right again, and you will see the calculation is as it should be now. (Note if you typed it as $C$11 the formula will always reference that exact cell and never move, if you did C$11 it would move columns, but never move the row.)
Now in the lower corner, instead of dragging down double click the little box on Q11, it will auto-fill down until Q40.
Now we are going to make a nicer table that shows the crime rates per 100,000 population in 2013 and 2014.
First we are going to make our table contents in the same sheet, and then we will make a new sheet and do a bit of nicer formatting. So anywhere to the right of your table of numbers, make a column that has the fields:
Crime Type
Violent Crime
Murder
Rape
Robbery
Aggravated Assault
Property Crime
Burglary
Larceny
Motor Vehicle TheftNote I use extra spaces to signify a hierarchy among the different crime types (e.g. Murder is nested within Violent Crime). Since the cell contexts are bleeding out into the neighboring column (for mine it is in column U), I am going to expand the column by double clicking on the right most part of the column, highlighted in the red square.
Then in the columns to the right, in the same row as Crime Type, type-in 2013 and 2014. In the end it should look something like:
Now we just need to fill in the numbers. Select cells D11 to M11, then click Ctrl + C (for copy). Once you have selected the cells, Excel will do a fancy dashed animated line around the cells.
Now select the cell just below 2013 and right click. You will then get a few options, and we want to select the one highlighted, which is the transpose function (turns rows into columns or columns into rows).
Select that option, and you will see your numbers filled in, but are slightly off. The UCR changed the definition of Part 1 rape (it is much broader now than historically), so the uptick in 2014 is expected. So your numbers should look like this:
Select the empty cell to the right of robbery, then right click, and select delete.
You will then get a little pop-up. Select the Shift Cells Up option, then hit OK.
You now get your numbers lined up as they should be. For a quick trick to check and make sure our numbers are OK, select the numbers to the right of Murder, Rape, Robbery, and Aggravated Assault. Excel provides some summary stats of the numbers in the lower right corner. We see that the sum is 663.8, which is close to equal to the total violent crime total of 663.7 (rounding error will account for the single decimal that is off).
If you do the same exercise for Property crimes they should equal the same as the total property crime.
Now fill in the numbers for the year 2014. I won’t go through point-by-point again, but here is what it should look like in the end.
Now we are going to make a new sheet to store our table in. In the lower left corner of the Excel sheet, click the + symbol. This will create a new sheet, by default named Sheet1.
Double click the sheet name, and edit it to be UCR Table.
Go back to the original sheet, named LocalCrimeJurisbyJuris, and select your table you made. Then copy-paste it into your new sheet after selecting cell A2. (We are going to put a title in the first row.) It should then look like this.
Before we move onto the next stage, lets make sure to save our work. Select the File option in the top right corner.
Go down to Save As, and then navigate to where you want to save the file. I do this to save a different file from the one I started with, so I don’t mess up and accidentally delete my original data file.
Now to re-save the file to the same location, all you need to do is hit Ctrl+S for the keyboard shortcut (where that means while holding the control button, hit the S key, and it will save). That is the keyboard short cut for Windows users, Mac users on on your own!
Navigate back to the UCR Table sheet. Then make the A column wider, so we can see the full text in each of our rows. Now we are going to make a header title for our table. Select cells A1:C1, then click Merge & Center:
In the now merged A1 cell, write Dallas Police Department, then hit Alt+Enter (this inserts a line break), and then on the second line write UCR Crime Rates per 100,000. If you don’t hold the Alt button, when you hit enter it will just tab to the next cell.
Now while still selecting the merged A1 cell, left align the cell contents, bold the contents, and then make the row size larger so we can see both lines of text.
Now select row 2 and italicize those cells.
Now select cells B3:C11, and then right click, and select Format Cells.
Then in the left box, select Number, then set the decimal places to zero, and use the 1,000 separator. There is no need for the precision at the level of decimal places for this table.
Now we are going to set the font for the whole table. For here it would be OK to use whatever, but for more complicated tables with numbers, you will want to use a monospaced font (each letter/number takes the same space). Else you risk numbers not lining up. Numbers should almost always be right aligned. Here we are going to pick Consolas for our font.
Now we are going to do some zebra shading to our rows, to further distinguish a hierarchy. Select the Violent Crime row, and in the paint bucket colors, select a middle shade of orange.
Then for the Rape and Agg Assault rows select an even lighter shade of orange. (Pro-tip, when zebra shading many rows, you can copy-paste, right click, and select Paste formats. An easy way to do many rows in a similar style at once.)
Do a similar color style for the Property crimes, but here choose a blue color. Then for the header rows choose varying shades of grey. (This is a bit busy compared to most tables I would do, but is for illustration on how to choose these colors.) Zebra striping rows tends to look nicer than using grid lines, so we are going to turn those off. In the top tool bar, select the View tab, then unselect the Gridlines option. Also increase the width of the A column so you can see Motor Vehicle Theft.
To finish our table, we are going to place a border around the outside of the entire table. Select cells A1:C11, then on the top tool bar navigate back to the home tab. Then underneath the font location, select the box icon. Then choose the thick outside borders.
Since our border is partially cut off by being on the edge of the spreadsheet, I like to insert extra columns/rows so we can see the whole table. Right click on the A column and select Insert.
This will shift the contents over one column, now do the same for the first row.
Now we have a nice table floating in our spreadsheet.
In the next part I am going to show how to make a graph. Select the cells B9:D12 (so the columns rows from Property Crime top left until 554 bottom right). We are just going to do a bar chart for the Property crimes, since they are Violent crimes have such different totals, it is difficult to put them on the same chart. Then in the top tool bar, select the Insert tab. In the charts options in the middle, hit the bar chart icon, and then select the horizontal bars (it is easier to read the labels for horizontal bars than vertical bars).
Now we are going to edit the chart to look nicer. First, select the Chart Title element within the chart. Then place the focus on the formula bar to the top. Hit equals, and then select the merged cell B2. This sets our Chart title to equal whatever is within that cell. Here it is not a big deal, but saves us some typing. Later on when you do interactive charts with slicers, this will make the chart title change when the chart content changes.
Now we are going to change our two series labels to say the years they correspond to. In the chart, click on one of the orange bars. Then right click and hit Select Data.
In the box that pops up, select the Series1 on the left hand side, and then hit the Edit box.
Note that now we are editing the 2013 series that is in column C. In the new pop up box, place the focus on the Series name box, and then select cell C3.
When you hit OK, it will change the series name to 2013 in the legend. Now do the same for Series 2, and change the name to 2014. (You could also type in a string here in that same box.) Now we are going to change the colors for the elements in the chart. Select the 2014 series by clicking on any of the orange bars once (you will see all of the other orange bars highlighted). In the top toolbar, make sure the Home tab is selected, and then use the paint bucket tool (same one as the background for the cells) and select Black.
Now do the same to the 2013 series, and select a lighter grey color. Having a darker (or brighter) color makes it stand out more. So here the 2014 series is more salient in the graph than the 2013 series. Next, select the crime labels on the Y axis, and then set the Font Size to 12.
Now lets make our chart slightly bigger. With the focus on just the overall chart, select the circle in the bottom right, and drag the chart so it is slightly bigger. Here I set it to a height of 4 inches and a width of 7 inches to be exact.
For the last part, I am going to place a box around the interior of the chart. In the interior of the chart, click on an empty space (the tooltip says plot area). Then in the top tool bar, select the Shape Outline option, and then select a middle grey option.
Last thing I am going to show how to change are setting the grid lines to be a dashed line instead of solid. Select the horizontal grid lines, and right click and select Format Gridlines.
You then get a bar to the right that pops up. In the drop down option Dash type, select one of the dash options.
There are so many options for editing charts in Excel. Throughout the course I will illustrate more tricks here and there, but will often leave some of those formatting left unsaid (and you need to figure it out yourself). You can play around some more and figure them out on your own. If you have questions on how to achieve a particular look though, always feel free to ask me in class, at my office, or via email.
To finish the chart I increased the font size for the X axis labels (the numbers), as well as the legend (the 2013 and 2014). So here is what my final spreadsheet looks like:
Make sure to save your work so far!
Typically you will not be disseminating the Excel spreadsheet itself, but will insert tables and graphs into other documents. Go back to the folder where you are keeping your homework document. Right click in the folder and select New -> Powerpoint Presentation. I named my file Lab01_ChartsGraphs.pptx.
Double click to open up the powerpoint presentation. In the New Slide icon, select blank.
Now go back to your Excel document and select the cells of your table and hit Ctrl+C to copy the table.
Now back to the powerpoint. In the paste option in the top left, in the dropdown select the Picture option.
This embeds a picture of the table in vector format. (This will save all of our formatting we did in Excel, but we can’t edit the cell contents.) Now select the Design tab in the top toolbar, and all the way to the right select Slide Size -> Custom Slide Size.
In the next window that pops up, select the letter size paper option (useful if you know you want to print out the slides). Then click OK. Don’t worry about the image option question that follows, we will be editing our images afterwards anyway.
Now drag the corners of your table to fill up the entire page. (Make sure not to drag the interior bottom/top or left-side/right-side handles, as they will distort the aspect ratio of the text.)
Now onto the chart. Create another empty slide. Go and select and copy the chart in Excel. Do the same paste picture as you did for the table. Then resize it to fill up the page. In the end here is what mine looks like:
Save the document, and then go up to File (circled above), and then Export. (Note, if you do not have this option in your version of PPT, you can typical go to Print, and Print as a PDF will be an option as a substitute for this.) Click the Create PDF/XPS Button.
Then save the PDF document in the same folder as your homework files (mine defaults to that same folder). I always want you to turn in the PDF document as the final homework, not an excel, word, or powerpoint file. Check out the PDF to make sure it looks like it should. Congrats, you are done with the tutorial! See below for your homework assignment this week.
For the homework there are two additions I want you to do:
You have your discretion on how to add in the two extra columns, and how to format the graph to make it look nice. While I did not show you how to make a line graph in the tutorial, you have all the background needed to figure it out on your own.
Insert these are two new slides into the powerpoint presentation. Export that presentation to a PDF, and turn in the PDF into ELearning. You will always turn in a PDF for your homework, never turn in the original Excel or other Office files. (Makes my life easier grading, as well as will be representative if you work as an analyst. PDF is the easiest to make it accessible to everyone.)
I will then go over my example finished homework for class the next Tuesday.