Use Excel Pivot Tables to Analyze FIMS Data

A pivot table is a powerful method of extracting and analyzing data from an Excel spreadsheet. It allows you to select calculation methods to analyze (summarize) large amounts of data.

An analysis is really a summarization of data. For example, the Trends and Analysis reports in FIMS are mostly totals-only reports, which sum a value (for example, Gift or Grant amounts) under a category heading (for example, Donor Class, or Grant Program Area). These reports analyze the data in one dimension.

However, some other Trends and Analysis reports (Full Gift Status and Full Grant Status) analyze data in two dimensions. In both cases, one of the dimensions is time (periods of time). These reports sum transaction amounts in each of four time ranges, broken down within those periods by a second category. For example, Gifts may be broken down by Donor Class, Gift Type, or Fund Type, and Grants may be broken down by Grantee Type, Program Area, or Fund Subtype.

Note: Refer to Creating Three Dimensional Graphs in Excel to see an example of a Full Grant Status report.

It can also be informative to perform a two-dimensional analysis of data where neither dimension is time. For example, you might analyze Grants into Program area by Region, or into Population Served by Fund Type / Subtype.

An Excel Pivot Table makes this kind of two-dimensional analysis easy.

You can use any export from FIMS as a basis for the Pivot Table, as long as the export contains the two analysis fields and the value you want to analyze. For example, the Grant History Export could be the basis for the Grants by Program Area and Region. A Results export can also be useful for Pivot Tables.