Office of Portfolio Analysis
OPA Excel Tips: Cross tabulations (PivotTables)
The Excel PivotTable function is a useful way to summarize data in Excel. All that is required is a
dataset with column headers (there needs to be a column header for each column you intend
to use in the pivot table data array).
Pivot Tables can be used to create summary statistics e.g. counts of applications by IC, cross
tabulations, nested tables and filtered tables e.g. awarded applications per fiscal year.
Example 1a: Award dollars by IC.
In this example the Transplantation dataset is used.
Assume we want to look at award total $s by IC from this dataset.
Step 1: Select the ‘INSERT’ menu and click ‘PivotTable’.