Friday, May 29, 2009

Introduction To Pivot Tables In Intelligence Analysis (Original Research)

Pivot tables are a handy but under-utilized feature of Microsoft Excel's program. Crime analyst's, in particular, make good use of these in their work but they are largely unknown to the wider community of intel analysts.

One of my students, Brent Pearson, explored this particular tool as part of my Advanced Analytic Techniques class last term. As part of his final assignment, he put together a couple of useful videos to introduce the novice to this particular tool:

A couple of caveats: First, Brent used law enforcement data in his examples but you can create pivot tables from virtually any structured data set. Second, Brent was starting at the very beginning with pivot tables. There are many more tricks and tips to pivot tables than he was able to discuss. If you use pivot tables in your work or know of some good resources about how to get more out of them, leave a comment!

Reblog this post [with Zemanta]


Anonymous said...

Well done video, I can definitely see myself using this.

Corey said...

Pivot tables play a "pivotal" role in some of the data analysis I am doing at my internship. We are analyizing different incentives for our agents and determining who they favor, why, and how we can enhance the incentives to benefit all agents, not just the largest. Without the pivot table tool, much of my analysis would take a large amount of time to complete as the data pool i work with is enormous. However, with this tool, I can easily spot trends and anomalies in the data and create a comprehensive analysis in no time at all. Pivot tables allow me to do all this without compromising the integrity of or having to "massage" the data to find the information I need. I definitely agree that this tool is one of the most underutilized in Microsoft Office and that it has large benefits in the business intelligence realm in addition to the applications in law enforcement as demonstrated by the preceding videos. I can't wait to hear more on this topic!

Anonymous said...

In the mid-90's (long before Pen-Link or THREADS), pivot tables were a mainstay of telephone toll analysis. The raw call data was easily cut and paste into the spreadsheet and would allow me to see how many times any number called the target number(s). Even three years ago, while serving as a Military Intelligence Analyst in Afghanistan, we used pivot tables for various analysis. It's only limited by your imagination. Thank you for the great videos!

Deborah Osborne said...

Thanks for these videos! New crime analysts will appreciate them. It would be great if your students could produce more like these... on various sorts of analytical techniques. Just basics would be great! When I was a crime analyst, pivot tables saved me much time and were my favorite tool of all. I didn't learn how to use them until halfway through my 10 years as an analysts, and they made me much more efficient and productive.