I previously wrote about keeping track of records requests in a spreadsheet, something that I encourage every journalist to do. That post has links to sample spreadsheets and various ways of keeping track of requests.
Public records are not the only thing that you can easily keep track of with spreadsheets.
Really, the options are limitless.
For personal use, you can keep track of accounts and user names. You can record all your serial numbers and item descriptions in case your house if burglarized or your stuff is otherwise stolen.
Having serial numbers at the ready makes it much easier for police to recover your stolen property because of national databases.
But working as a journalist, and more specifically as a cops/courts reporter, there are a few specific instances where I use spreadsheets to make my life that much easier.
Tracking criminal cases
Rio Arriba County has a high crime rate, as illustrated by the cases I’m following.
When it comes to being a cops/courts reporter, I keep a bunch of spreadsheets to track everything going on in my beat and, often, to keep track of what went on.
I have one spreadsheet I use to track all the criminal cases I’m covering.
As you can see, Rio Arriba County has a lot of ongoing and serious criminal cases, although one or two of those may be northern Santa Fe County.
Although not shown in the picture to the left, this spreadsheet currently has three sheets: “open,” “closed” and “master.”
The cases pictured are only the open cases. Everything goes into the “master” case list, is then copied to open cases and, when a case hits final adjudication (either through sentencing or the appeals process) it gets moved from open to closed.
The spreadsheet currently has the following sorting columns:
- Type
- Defendant Name
- District case number (higher court)
- Offense date
- Status
- Victim
- Ancillary case number (such as the pre-trial detention case number, a civil lawsuit or restraining order)
- Weapon
- Magistrate case number (lower court)
- Report (if I have police reports for the case)
- Appeal case number
- Date filed (district court)
- Date filed (magistrate court)
As shown in the screen shot, type refers to what kind of crime was committed.
I include fields for the date of incident as well as the date filed because the date of incident is a field that moves with court filings as they wend their way through the judicial system. That makes it a necessary number when sorting through court records.
While this does take a small, to moderate, amount of time to set up and initially populate, it’s well worth the time savings later on, the brain-wracking when you can’t remember which case is supposed to go to trial in a few months and from potentially missing important hearings.
While I’ve still missed my fair share of court hearings, keeping a spreadsheet makes it easier for me to check on statuses of everything I’m still covering.
Tracking lawsuits
As the cops and courts reporter, I also cover lawsuits against all our public entities, including the various school districts, our college, the city (and its police department/officers), the county (and the Sheriff’s Office) and even State Police.
Keeping track of all those cases requires a little bit of data entry, but it prevents me from forgetting about cases entirely.
Moreover, spreadsheets can have formulas which means you can keep a running tally for how much a specific entity has paid out in settlement agreements and court decisions.
It also means you can make a new “sheet” or tab in your spreadsheet, dump in all the lawsuits that name a specific person (think of individual officers named in civil rights lawsuits, or all of the lawsuits arising under a specific chief or president’s tenure, or all the lawsuits that are whistleblower claims) and create a simple formula to add up all the amounts.
Keeping a spreadsheet of lawsuits (I break mine down into groups, such as “city,” “county,” “state” and “schools”) also serves the same purpose as my spreadsheet keeping track of criminal cases: it helps me not forget about cases.
Tracking death (overdoses, suicides, etc.)
Once a year, I go to the Office of the Medical Investigator to photograph all of the autopsy reports for Rio Arriba County for the previous year.
It usually takes a few hours to take the pictures and then quite a few more to sort everything.
Each year’s overdose spreadsheet has lots and lots of sub-sheets. I didn’t even mention pivot tables, and the massive things you can do with them.
Once all the reports are sorted into their respective folders and sub-folders, I start adding all the names to a spreadsheet, along with the day of death, the year of birth, age at the time of death, agency that handled the investigation, the place of death and a bunch more fields related to alcohol and prescription drugs.
I also have one column for all the drugs the person overdosed on. I also break these out into another sheet, with one drug per row.
That spreadsheet helps me with a couple of different aspects of reporting the story.
When I’m writing it, it’s pretty easy to crunch the numbers of how many deaths involved what drug or what drug combinations (as seen in my story on the number of deaths in 2014). In 2016, for 2015’s deaths, I did the same thing:
Heroin continued its trend of being involved in a majority of overdoses. It was involved in 17 of the 24 deaths, or 70 percent. Three deaths were caused by heroin, alone.
Opiates garnered an even larger portion, being involved in 20 of the 23 deaths, or 87 percent. Heroin is an opiate.
The third most-involved drug was alcohol, contributing to 13 deaths, or 54 percent. Alcohol-only overdoses were not counted.
Prescription drugs, including the opiate methadone, were responsible for nine deaths, or 37 percent, while cocaine was involved in six, or 25 percent.
Having those spreadsheets handy also makes it easier for me to figure out what police reports I still need to request for which deaths.
I can also sort the sheet by age, by drug, by date of death or by location.
It makes reporting the story much easier.
Sorting salaries
Every October, we run the salaries for all the entities we cover.
That means going through whatever document has been provided and updating/adding to our salary list document from last year with new salaries, positions and deleting employees no longer there.
Doing this is easy when the salaries are provided as a spreadsheet.
Doing this is hard when the salaries are provided in a PDF. There are a few different solutions, depending on the layout of the document you were provided, but the point is to get them into a spreadsheet for future reference.
Even if your paper doesn’t run salary lists, it’s still a very good idea, once or twice a year, to request them.
There are a lot of things you can do with that data, including looking for pay discrepancies, but they are really important as reference documents.
Not sure how to spell that employee’s name? Salary list.
Someone got put on admin leave with pay? Salary list states how much he makes and you can then calculate how much he is being paid not to work, per week, per month, etc.
Someone got fired? Salary list states how much he made.
Tracking award entries
If you’re anything like me, your boss comes into the newsroom and says that you have a month to get him your contest entries for the state’s annual newspaper contest, and you then scramble to figure out what you’ve written in the last year that warrants entry.
My contest entry spreadsheet is tailored to New Mexico’s better newspaper contest and the way my paper keeps its archives. None of these entries won during the 2016-2017 contest period. I won second place for news writing.
There is a better way!
Instead of scrambling in June or July, and looking through old PDFs, paper copies and your pre-production text documents, you can keep track of everything you want to enter in a simple spreadsheet.
You only need a few columns to keep track of everything important:
- Headline
- Date (run)
- Content (story vs series vs photo)
- Continuing
- Other dates
- Location (in paper)
- Collaborators
- Contest category
The most important part of keeping track of contest entries is to put them into the spreadsheet shortly after the story runs so, at the end of the contest period, you can go back and cull what you think are the best entries to submit.
In the case of continuing coverage, it’s very important to update your entry with the new story dates because if you don’t, you’ll be scratching your head and asking your editor who has the better memory for all the stories that ran as a result of whichever broader story.
This post is licensed under a Creative Commons Attribution-Only 4.0 license.