Analyst Toolbox

Powerful and Simple Excel Filters

Product: Microsoft Excel

All crime analysts should familiarize themselves with the powerful "Filter" feature found under Excel's "Data" menu. Just click Filter/Auto Filter on the Data menu, and column headings will become drop downs that let you filter based on the values (or custom calculations) within one or more of those columns.

This is easier to try then it is to explain. Open a workbook and go for it. Your incident data is probably the best application, but another good one would be the many tables available in Excel format from the annual FBI UCR report:

http://www.fbi.gov/ucr/ucr.htm

You can do some very interesting and informative comparisons with similarly-sized cities without re-stroking tons of data. If you have filtered a spreadsheet, select all the data and copy then paste to a fresh sheet it you want to work with it in more detail--since the filtered sheet is composed of non-contiguous rows. The pasted data on the new sheet will be contiguous rows that you can manipulate more easily.

Author: Tom Casady
Updated 9/21/2005

View all toolbox tips | Send to a colleague