Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
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’.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
The input box shown below will appear. This enables you to tell Excel where the data array is
that you want to use in the pivot table. Below that you can specify whether you want the Pivot
Table to appear in a ‘New Worksheet’ (the default) or on an ‘Existing Worksheet.
Step 2: Enter the Table Range (either by typing or highlighting the cells) and Click ‘Ok’.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
The screen below will appear showing a place holder for the Pivot Table in the cells and a menu
on the right to enable you to select the data you are interested in. The top half of the menu
shows the variables in the dataset and the bottom section of the menu shows the various parts
of the pivot table.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Step 3: To produce a Pivot Table showing award total $s by IC, first drag and drop the ‘IC’
variable into the ‘ROWS’ box. When you do this, the list of ICs will appear as the first part of
your PivotTable.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Step 4: Next drag and drop the ‘Awd Tot $’ variable into the ‘VALUES’ box. The total $s
awarded by each IC will now appear in the Pivot Table.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Example 1b: Funding by IC by fiscal year.
Step 5: To produce a cross tabulation by fiscal year, simply drag the ‘FY’ variable into the
‘COLUMNS’ box and the following table will appear.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Step 6: To change the format of the numbers in the cells, click on the down arrow to the right of
the variable name in the ‘VALUES’ box. The menu below will appear. Click on the ‘Number
Format’ button and select the appropriate format for the data, here we selected ‘Currency’ and
changed decimal places to ‘0’.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Example 1c: Calculating number of grants instead of award $s.
When entering cell values in a pivot table, Excel will usually default to ‘Sum’ if a numerical
variable is used. The default for text variables is ‘Count’.
Step 7: To change the cell values to show a count (number of projects in this case), click on the
down arrow to the right of ‘Sum of Awd Tot ‘ and the menu below appears. Select ‘Value Field
Settings’
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
You will see ‘Sum’ highlighted.
Step 8: Select ‘Count’ and click ‘OK’. Other options may be useful e.g. ‘Average’ if you were
looking for average award by grant type.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
The following table appears showing counts of the number of projects instead of dollars
awarded. Any variable, either numeric or text, can be used to calculate ‘Count’
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Example 1d: Filtering by Award Type
It is also possible to filter the data that appears in the table. For example we can filter the
count of projects to show just those that are Type 1 Applications.
Step 10: Drag and drop the ‘Type’ variable into the ‘FILTERS’ box. A new line will appear above
the table with a drop down arrow. (All) means that all Application Types are selected.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
Step 11; Click on the drop down arrow next to (All) and select ‘1’, then click ‘OK’. It is also
possible to select multiple items by ticking the box at the bottom of the menu, just above ‘OK’.
Office of Portfolio Analysis
OPA_T#973_Mar-30-2016
The following table appears, showing a grand total of 1,341 applications instead of the 1,802
shown previously.
In this example the ‘Type’ variable could have been dropped into the ‘COLUMNS’ box instead to
produce a nested table. It would then show Fiscal Year for each Application Type, or
Application Type for each Fiscal year, depending on whether the ‘TYPE’ variable is dropped
above or below the ‘FY’ variable.
More help
Click on the ‘?’ at the top right of the Excel screen and type ‘PivotTable’ into the help search.
