Pivot Tables and Charts

Pivot Tables are a powerful tool that can help you quickly summarize your data. If you have any questions or get stuck as you work through this in-class exercise, please ask the instructor for assistance.

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.
Create a default pivot table in the spreadsheet you just downloaded:

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.

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:

Visual of the table with data.

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.

Sorting using descending andascending.