Creating separate worksheets for each major division of data, as we did in the previous Computer Toolkits, makes it easy to enter and format each year’s numbers. When it comes time to start a new year’s worth of data, just copy the last year’s active cells onto a new blank worksheet, delete the values, and the headings and formulas will be left for you to use. But the object of this entire exercise is to accumulate data that can be used to reveal trends from year to year, and see what direction your efforts are taking. There are several ways to go about this, but here we’ll create a summary worksheet to bring all of our data together.
First, we need a new, blank worksheet in our Excel workbook. Right-click on one of the tabs at the lower left of the display, and then choose “Insert” from the context menu that appears. A new tab and worksheet will be created, most likely with a label like “Sheet1.” Right-click on this tab, choose “Rename,” and give it the title of “Summary,” and drag it to whatever position you want it to have in your array of data. You can also use this same menu to change the color of the tab, if you like.
It would be a simple process to just copy the data from the annual crime statistic worksheets to the summary sheet, but if any changes were made later, they would not be reflected in the summary. Ideally, the summary will update as the sheet where the data is first recorded is updated, and each entry need only be made once. We can accomplish this by having the cells on the summary worksheet display whatever is contained in the cells on their source worksheets, instead of just copying the numbers from one sheet to another.
This is done by entering references to the source cells, instead of the copied numbers, into the cells on the summary sheet. Every cell in an Excel workbook has an absolute reference— the sheet name, followed by an exclamation point, and then the cell coordinates themselves. For instance, the upper leftmost cell on the worksheet with the title “2001” can be referenced on any worksheet as =2001!A1. If the sheet name is omitted, then the default is to the sheet where the reference appears.
We’re going to try to get all of our annual data for the past three years in one array on the summary sheet to facilitate making graphical charts of the data. This could be done without creating a summary sheet, but it would be a much more complex process, and would need to be repeated for every new chart. The first step is to get our column headings onto the summary sheet. We could just copy these, but in order to demonstrate the cell reference technique, we’ll do it that way. The column headings are the same on each of our annual worksheets, but we’ll use the headings from the worksheet labeled “2001.”
On the Summary worksheet, in cell A1, enter the formula =2001!A1. On worksheet 2001, this cell contains the word “Crimes,” and the same word should appear in Cell A1 on our summary worksheet. Excel adds a single quote mark before and after the “2001” portion of the formula, because it is reading this as text, instead of a number, but you need not enter that into the formula. (Figure 1)
Just as a check, go back to worksheet 2001, and change the contents of Cell A1 to something other than “Crimes.” Now go back to the Summary page, and you will see that the contents of Cell A1 there reflects the change you made. This will be true with entries of your data as you make them on the source pages.
We’ll use the same labels across the top of the chart, so this formula can be copied to Cells B1 through F1. Click on Cell A1 on the Summary page (in Excel-speak, Summary!A1), then on the Copy icon, then select Cells B1 through F1 and click on Paste. The labels all appear in Row 1. Now, let’s get the data. Select Cells A1 through F1, click on Copy, then select Cells A2 through A13. Click on Paste. The data, not including the totals, population, and crime rate entries, appear in Cells A2 through F13.
For reasons that will be more evident later, we’re going to replace the Totals column with a reference to the year where this data originated. Click on Cell F1 on the Summary sheet, which is where the Homicide totals for 2001 appear. Type “2001” (without the quotes) to replace that entry. Now, copy that entry to Cells F5 through F13. “2001” should appear in the last column of data. Change the label in Cell F1 to “Year.”
Now, we’re going to repeat this process for the data in Worksheets 2002 and 2003. The homicide stats for the year 2002 reside starting in Cell 2002!A4. In Cell A14 on the Summary worksheet, type =2002!A4. The word “Homicide” should appear there. Now use the same copying techniques used in the preceding data set to copy the values contained in Cells A4 through F13 on the 2002 worksheet into Cells A14 through A23 on the Summary worksheet. Repeat for the 2003 worksheet, placing that data in Cells A24 through F33 on the Summary worksheet. Change the entries in the Total (should now be “Year”) column to the year when that data was obtained. When you’re done, you should have something like what appears in Figure 2, although your formatting may be different.
There is one more step before we have data that will be easy to chart. Because of the way that rows were formatted and labeled on the source data pages, we have several rows (2,3,9,19 and 29) that contain labels and no data. We’re going to be sorting these rows, so the empty ones will get in the way. Hold down the Control key, and click on the row numbers at the extreme left of the display for each of the rows I listed. They should remain selected. Now click on Edit|Delete, and the empty rows should disappear, moving up the others to replace them. The result is a single table of data for all of the years contained in our workbook, with a single row of headings in the top row. Next time, we’ll start manipulating this data to produce graphical charts showing trends.
Tim Dees is a former officer who writes and consults about applications of technology in law enforcement. He can be reached at (509) 585-6704 or by e-mail at firstname.lastname@example.org.[*]