This is a follow on to my post on Old Geeks Rule regarding setting up a spreadsheet to hyperlink to existing pdf files.
This will be more about setting up a spreadsheet to allow easy data entry and finding patterns in the jury pool.
First we need a spreadsheet. I happen to have both Excel 2010 and 2013. I will use 2010 to keep things more widespread.
First an overview
Why should I use a spreadsheet to track my juror questionnaires?
Good question. This approach is obviously only useful if you actually have time to analyze their responses. If you get the responses, go to lunch and start voir dire right after lunch; the time spent entering data is not time well spent.
Likewise, if you have twenty potential jurors, again your time is probably better spent elsewhere.
However, if you end up with 150 potential jurors, several assistants, multiple attorneys and an evening (or longer) time frame available to analyze the responses; it makes sense to set up a process to wisely use your voir dire time.
And that is where a spreadsheet to track the juror responses is useful.
The setup
Since the juror questionnaires are known before they are returned by the jurors (obviously), much of the spreadsheet can be setup beforehand.
Let’s get started.
Create a new spreadsheet called Juror Questionnaires.
Use the following column headings;
- cell A1 = Juror Sequence Number
- A unique number by jury pool, typically on the individual jury duty card.
- There may be duplicates if there are multiple pools called.
- It will match to the juror’s personal information as known to the court.
- cell B2 = Juror Number
- With luck, all the Juror Sequence Numbers will be one-to-one with this number
- cell C2 = First Name, MI
- cell D2 = Last Name
- cell E2 = Address, Street
- cell F2 = Address, City
- cell G2 = Address, State Code
- cell H2 = Address, Zip
- cell I2 = Date of Birth
- cell J2 = Age
- cell L2 = Map Location
Put questionnaire specific items in the subsequent columns as appropriate.
Here are some examples of yes/no type questions:
- Married
- Military Service
- Member of Specific Club
Examples of either/or questions:
- Gender
- Own or Rent Housing
- Retired or Employed
Some examples of multiple choice questions:
- Type of Employment
- Education Level
And range of feelings:
- Are there too many lawsuits?
- Are monetary awards too high?
I’ll add some of these to my example spreadsheet to show some code to make data entry easier and less prone to errors; as well as show how to use some built-in data mining.
(Ugh, the worst part is generating the fake data. Fortunately, there are internet sites that will generate at least some of this fake data – my thanks to FakeNameGenerator.com.)
Excel as Data Management
Ok, so now we have our spreadsheet loaded with all sorts of nice data. How do we use Excel for data management?
From within your spreadsheet, choose the Data menu item, then Filter. You should now have little down facing arrows on each column header.
Examples
Find the Males over 50.
In the Gender column, click the arrow and only select Males.
In the Age column, click the arrow, Numbers Filters, Greater Than… and set the Age “is greater than” value to 50 and click the Ok button.
Here are the potential jurors that meet these criteria.
(To clear any filters, click the arrow for that column and choose the Clear Filter From option.)
Find home owners that think awards are too high.
In the Housing column, choose Own and Awards Too High column choose Strongly Agree.
For Extra Credit
What’s with cell L2?
Enter this formula in that cell “=HYPERLINK(CONCATENATE("https:\\maps.Google.com\maps?q=",E2,"+",F2,"+",G2,"+",H2,"&z=12"),"Location")”. This formula is all one line, it breaks due to page width.
This will create a clickable link that will display the address in Google Maps. This is handy to match juror’s abodes to nearby events.
Cool formula to calculate Age from Date of Birth: “=DATEDIF(I2,NOW(),”y”)
Uses the DateDif formula to find the difference between I2 and RIGHT NOW, and returns years.
This is a link to the zipped spreadsheet in my Visionary Legal Dropbox account. If that is no longer available or is non-accessible, please email Support@VisionaryLegal.com and we will try to get you a copy. At the time the file was uploaded it was free of any known security issues. As with all files from the internet, assume there is a problem and be proactive is your security.
Thanks for reading as this is much longer than my typical posts.
Please feel free to contact me for any issues.
chuck
