ACRL, IPEDS, and National Surveys

Using Pivot Table functions within Excel can help you easily sort and calculate format quantity totals for survey reporting. Here, we’ll use an example of a CSV export from the Title Holdings Quantity by Format Custom report.

Step 1: Import your CSV title holdings quantity file into Excel.

Step 2: Add a column to your spreadsheet and create a column named Code. Add item format codes for each Format/Subformat combination. Format codes can be anything meaningful for you that align to the ACRL/IPEDS survey categories. See the box below for guidance from ACRL.

Sample:

Step 3: Convert the spreadsheet to a Table. Highlight/Select the columns containing your data, then click Insert > Table. In the Create Table dialogue box, make sure My table has headers is checked, then click OK.

Sheet after conversion to Table:

Step 4: To create Pivot Table, right-click any cell in your table, and click Pivot Table from the Insert menu. Follow the default Create PivotTable prompts to create a table in a new worksheet (spreadsheet tab).

Step 5: Drag and drop data fields to create the Pivot Table (recommended settings below):

Recommended Fields

Drag and drop the following:
Columns: Code
Rows: Material Format, Material Subformat
Values: Title Holdings Quantity (Sum of Title Holdings Quantity)

Pivot Table Sample:

Step 6: To sort and categorize, insert a slicer. In Pivot Table Tools > Analyze, click Insert Slicer and select Code.

Use the slider buttons one at a time to obtain the calculations for each format code, and use the Grand Total numbers for the survey.

Based on feedback from ACRL/Counting Opinions, PALNI suggests this structure for counting Titles and Items/Volumes (if blank, do not report):

*Please see the PALNI documentation for ACRL guidance regarding counting Digital/Electronic serials.