How to Track Police Misconduct for Free with Spreadsheets: A Step-by-Step Guide for Public Defenders

Starting to track police misconduct systematically can transform your defense practice, but many offices struggle with where to begin. While comprehensive solutions like Techtivist offer the most powerful capabilities, building a basic tracking system helps you understand your needs and prove the value of systematic misconduct tracking to your office.

Everyone starts somewhere. The first version of a police misconduct database at The Legal Aid Society of New York consisted of paper scrapbooks where attorneys manually collected articles about officer misconduct and filed them in binders. As the collection grew unwieldy, they transitioned to a simple spreadsheet system, which grew into a library catalog, and then a database which ultimately became the foundation for more sophisticated tracking like Techtivist. This grassroots approach helped them identify patterns, win cases, and eventually build the case for investing in professional-grade solutions.

The Traditional Approach: Officer-Centered Filing

When getting started in this digital age, most offices naturally create folders on a shared drive for each officer and copy documents related to that officer into each relevant folder. This approach works fine if you plan to stay small and never grow beyond basic tracking.

However, as your database expands, this method creates significant problems:

• File duplication: The same lawsuit involving 3 officers gets copied 3 times, wasting storage

• Version control chaos: Update one copy but forget the others exist

• Confidentiality confusion: Mixed case files make it unclear what's shareable vs. privileged

• Search inefficiency: Finding all lawsuits requires searching multiple officer folders

• Duplication Confusion: Inability to differentiate between officers with the same name or if you already have a file saved

For this reason, we recommend maintaining a spreadsheet list of all your officers and a separate drive with the misconduct data separated by the type.  Then, in your spreadsheet, you can link the officer to files related to them.  When anyone wants to look up an officer, they can go to the spreadsheet and click the links. 

What You'll Need

• Microsoft Excel or Google Sheets 

• Shared Drive, OneDrive, Google Drive, Dropbox, or internal network folder

• About 3 hours for initial setup

• Basic spreadsheet knowledge

• File organization abilities

• Commitment to consistent data entry

Step 1: Create Your Master Spreadsheet

First, create a spreadsheet of the officers in the agencies you’d like to track.  We recommend putting this spreadsheet somewhere that your whole office can have “view access” but keep only a few people in charge of maintaining it. Here are some essential columns you might want to track: 

• Unique Identifier - it’s important to have a way to uniquely identify officers. Officers may have license numbers in your state that you can request (check with POST agency) or the National Police Index. We can't overemphasize the need to keep your list of officers clean and know who is who.

• Officer First Name

• Officer Middle Name

• Officer Last Name

• Badge Number

• Previous Badge Numbers (comma-separated)

• Department (use consistent! name)

• Agency Start Date (when the office first began working at the agency)

• Status (Active/Inactive/Retired)

• Content 1 (link to document in step 2)

• Content 2 (link to document in step 2)

• Content 3, etc, etc, etc

Step 2: Smart Document Organization

Organize documents by TYPE first, then link to officers. This approach scales efficiently and maintains clear boundaries between shareable misconduct data and confidential case information. Make sure you check out our blog post on what data to track.

Recommended Folder Structure:

• Police_Misconduct_Database/

• ├── Content_Types/

• │   ├── Lawsuits/

• │   ├── News_Articles/

• │   ├── Internal_Affairs_Reports/

• │   ├── Court_Transcripts/

• │   ├── Body_Camera_Footage/

• │   ├── Emails_Communications/

• │   └── Civilian_Complaints/

• ├── Officer_Spreadsheet.xlsx

File Naming by Case Numbers and Identifiers

With any data project, consistency is key. You want to make sure you have a consistent way to name files so that you avoid duplication.

For content with case numbers or other unique identifiers: Use the case number as the primary identifier to avoid duplicates and enable easy cross-referencing:

• Lawsuits: 2024CV001234.pdf

• Court Transcripts: 2025DOR005678.pdf

For content without case numbers: Create consistent identifying formulas:

• News Articles: PublicationDate_Headline.pdf

  • Example: 2024-03-20_Metro-PD-Corruption.pdf

• Emails/Communications: YYYY-MM-DD_Sender_Subject.pdf

  • Example: 2024-03-10_BarbaraSmith_Officer-Discipline-Policy.pdf

Benefits of This Naming System:

Duplicate Prevention: Before adding a document, check if the case number or identifier already exists

Easy Cross-Referencing: Quickly find all documents related to a specific case

Clear Boundaries: Separate folders make it obvious what may be public record vs. confidential

Step 3: Linking Documents in Your Spreadsheet

Now that you have your officer spreadsheet and organized document folders, you need to connect them. This is where the magic happens—turning separate files into a searchable, clickable database.

Wherever you have your documents stored, create a shareable link to them.  Shared drives like OneDrive, Dropbox, and GoogleDrive will allow you to create shareable links. 

If you are using a local storage system, you can still create links or “symlinks” to the documents using the filepath, but keep in mind that if you change the folder structure or rename the file, the link will break.  If you are using local storage, then best to commit to never changing your structure/renaming files or only put the filename in the spreadsheet. 

Once you have your link, paste that next to the corresponding officers' names in the spreadsheet under "Content 1". If they have something under "Content 1", move to "Content 2" and so on. When one document involves multiple officers, paste the link in each officers' row under the next available Content column.

Sample Workflow: Adding a New Document

1. Receive new document (lawsuit, news article, etc.)

2. Name it properly using your naming convention

3. Save it in the appropriate Content_Types folder

4. Get the shareable link (Google Drive, OneDrive, or Filepath)

5. Find the relevant officer(s) in your spreadsheet

6. Add the link in the next available "Content" column

When to Upgrade

You'll know it's time to move beyond spreadsheets when:

• Data entry becomes overwhelming (more than 3-4 hours per week)

• Search limitations frustrate you (can't find information quickly)

• Collaboration needs grow (multiple people need simultaneous access)

• Analysis requirements increase (need trend analysis and reporting)

• File management becomes unwieldy (hundreds of documents to organize)

Success Metrics

Track these indicators to measure your system's impact:

• Time saved per officer lookup (should decrease as database grows)

• Cases where misconduct evidence was used (should increase)

• Brady violations discovered (previously unknown misconduct)

• Successful motions filed using misconduct evidence

Check out this blog post for more ideas.

Getting Started Today

Don't wait for the perfect system. Learn how to build office-wide momentum to track police misconduct and then get started with these basics:

1. Create your spreadsheet with essential columns

2. Set up your folder structure in Google Drive or similar

3. Add your first 5 officers from recent cases

4. Establish your naming conventions and stick to them

5. Schedule weekly data entry time to build the habit

Remember, this DIY approach is just the beginning. As your database grows and proves its value, you'll likely want to explore more sophisticated options like Airtable or ultimately Techtivist's purpose-built solution.

The important thing is to start tracking today. Your future clients—and your future self—will thank you.

---

Ready to take your misconduct tracking to the next level?

Contact Techtivist at info@techtivist.com or book a call here to learn how our platform can amplify your office's impact and connect you with a network of defenders driving systemic change.