Pivot Tables and Charts

If you have any questions or get stuck as you work through this in-class exercise, please ask the instructor for assistance.

Pivot Tables are a powerful tool that can help you quickly and dynamically summarize or consolidate data. Pivot tables can:

  • Group items/records/rows into categories,
  • Count the number of items in each category,
  • Sum the value of items,
  • Compute averages, find minimal or maximal values, etc.
  • Help you normalize your data, as spelling inconsistencies typically become very obvious.

For example, if you have a spreadsheet of workshop attendance data, you can summarize it so you can easily see how many workshops were held, and the total number of participants across all the workshops. In the figure below, from the spreadsheet on the left, you could easily create the pivot table on the right:

Pivot table example

Now let’s see how to create one!

  1. Download this spreadsheet with data for this exercise Note: You may have a yellow bar at the top with a button that says Enable Editing. Click on that to enable editing.

  2. Create a default pivot table in the spreadsheet you just downloaded:

    Insert pivot table icon.

    • Select all the data in the spreadsheet from A1 to C34.
    • Select the Insert tab, and then press the PivotTable button on the top left of the ribbon.
    • When the PivotTable dialogue box appears, press OK.

    Demonstration of Step 2.

  3. You now have a blank canvas of a pivot table setup. Let’s add data:

    Selecting workshops and total attendance checkboxes.

    • Select the Workshop and Total Attendance checkboxes on the right hand PivotTable Fields toolbar. You now have a list of workshops with a sum of total attendance for each workshop sorted by the workshop name.
    • Let’s add a column to count the number of each type of workshop held: In the PivotTable Field Name area, grab Workshop, and drag it to the ∑ Values area in the bottom right of the Excel window. You should now have a pivot table with another column named, “Count of Workshop”.

    Sigma Values.

    • Lastly, select the Date checkbox in the PivotTable.
    • Move Date into Rows field. You should now have a pivot table that looks like this:

    Moving date into the rows field.

    Visual of the table with data.

  4. Let’s create a chart:

    • Start by un-selecting Date in the PivotTable field name area.
    • In the ∑ Values area, right mouse click on Count of Workshop and select Remove Field.
    • Sort the Sum of Total Attendance column by clicking on the little arrow facing down (found within the “row labels” cell). Then select “Descending” and sort by the “sum of Total Attendance”. Feel free to try this by selecting “Ascending” instead as well.
    • Select the Insert tab on the top ribbon, and then select the PivotChart button in the ribbon.
    • Click OK, and now you have a basic chart. Please feel free to experiment with both the data and the chart.

    Demonstration of Step 4.

    Note: this step is a bit different between Windows and Mac. The above works for Mac, but for Windows, you can check the animation below.

    Demonstration of Step 4 in windows.

Great job!

NEXT STEP: Automation with Macros