Thursday, July 9, 2009

Scouting with MS Excel (Pivot Tables)

I get asked about this quite a bit, so I figure I will throw it up here for an easier way to share this information.

This is a real sexy (and cheap) way to make it look like you actually know something.

There are several Football applications available (for purchase) to consolidate tendency information for use with scouting opponents (or yourself), but if you're cheap like me (or have no school budget), we can accomplish the same darn things with software everyone already has (free).

HERE'S AN EXAMPLE OF USING EXCEL FOR SCOUTING, THEN CONSOLIDATING THE INFORMATION WITH A PIVOT TABLE....





click the data you want to include (it MUST have a header in each row)




Click Data>Pivot Table




just click "finish" at the wizard and start dragging the columns (of info) you want to look at




here's the fun part - start dragging the column headers in the order you want over to the left side of the pivot table.....include a unique identifier like "yards gained" or "play number" in the 'Data Items' field

BAM! You're done.
Pivot tables work because they are simple when you're exploring a few different (about 6) categories.

If you want an expanded report based on seven or more different variables, you are better off just importing your data as a table into Access or some other database compiling application, and writing queries.

For me, I just basically want to get a good understanding of what a team will do out of what formation, based on D&D.......if you want to worry about hash, score, weather, what color socks the team is wearing, etc.- then you'd better throw it in a database and write a query.

Pivot Tables get what you want done in a manageable fashion, without "losing" information like a filter would do (all a filter does is just hide the data).

Let's say you want to know all the plays that were
PRO RIGHT
1st & 10
<40>











Lets say you just want to get a general idea of your data......











From the data, I could see their big-hitting plays on 2nd down and the bad calls on 3rd down (yardage average)
Now I want to know ONLY 1st down plays based on field position

 Now let's say I want to see ALL the plays that featured Trips Right (I'd double-click on the count of the formation of Trips Right)





No comments:

SIDEBAR