Create a Summary Using Excel PivotTables

Related pages:

Add Excel PivotTables to Exported DTS Reports

The Development and Training System (DTS) offers reports for managers and leads to view. Once you have downloaded a report from the DTS and saved it as an Excel file, you can insert a PivotTable to view the information in a table format and even add graphs.

Directions are detailed below, or you can skip to the bottom and watch a video on this process.

Instructions & Tips

1.

Open the downloaded DTS report in Excel. 

[Ok] through error messages.  Convert the file to an updated Excel book if asked.

2.

Prep the DTS report data for use with a PivotTable.

A. Delete the last few rows (> be sure to delete the row with a total so there is a blank row under the data).

B. Save prepped file.

3.

Add a PivotTable.

A. Place your cursor in the first cell of data.

B. In the options at the top, select the INSERT tab.

C. Select  “PivotTable”.

D. A “Create PivotTable” message box will appear. If you selected all cells prior to inserting the PivotTable, you can accept the Table/Range by clicking “OK”.

E. It will open a new sheet, with a Pivot Table on it and Pivot Table Fields to the right.

4.

Customize the PivotTable.

Choose fields to add or remove from the report by dragging them from the top box to one of the bottom boxes.  If the fields are not visible, simply click on the pivot table and the optional fields will appear.

We recommend you try the following setup to get you started.  You may have to scroll up and down in the PivotTable Fields box to see all of the options below.

Simple view:

  • Drag “Learner ID” to the VALUES box and drop
  • Drag “Status” to the ROWS box and drop
Excel pivot table example image of an Excel pivot table field selector box

Feel adventurous? Click on the pivot table (sample above), select the "INSERT" tab, select "Recommend Charts" and click a pie chart, OK.

Other views to try:

  • Drag “Learner ID” to the VALUES box and drop
    • Confirm that "Count" (rather than "Sum") is being used in the Value Field Settings
  • Drag “Department Name” to the ROWS box and drop
  • Drag “Learner Name Full” to the ROWS box and drop
  • Drag “Status” to the COLUMNS box and drop
Excel pivot table fields selector box
Sample pivot table

Remember to save the file when complete.  See video below for demonstration of above instructions.



Want to learn more?  Access your http://www.csuchico.edu/lynda account and search for PivotTables (and Slicers).

Need Help?

  • For more information, contact Professional Development at (530) 898-6433.
  • For assistance with Excel, go to https://www.csuchico.edu/lynda and search for your topic of interest.
  • For DTS support, review tips for Navigating the DTS, or contact Professional Development at (530) 898-6433.