Creating PivotTables

In my previous post I discussed how you can use filtering in Excel to examine the Moodle logs data for a course. In this article I will show you how to create a PivotTable using the Moodle logs data for a course and some simple analysis we can do using the PivotTable.

What is a PivotTable?

A PivotTable is a feature in Excel that takes a set of data and allows you to manipulate the columns and rows that are shown. This allows you to select different parts of the dataset (columns or rows) and to perform calculations on those selections (e.g. SUM, COUNT, AVERAGE). You can also apply filters or look at a combination of columns or rows.

Why use a PivotTable?

A PivotTable is a good way to explore a dataset. You can try different combinations of row or column selections and apply different calculations such as SUM and COUNT. This allows you to look at the dataset from different angles and can highlight points of interest that you would like to explore further. Using a PivotTable on the Moodle course logs could show you the following:

• Which user has performed the most actions in the course
• Which user has viewed the most discussion posts
• Which administrator has created the most discussion posts
• The average number of posts created per user
• The maximum number of course modules viewed.

Preparing your data

Start by opening your Moodle course log in Excel. (See this post for how to access your Moodle logs). Before creating a PivotTable, I strongly recommend that you first convert the data to a table. Do this by clicking into the data and selecting (from the Home tab) Format as Table.

Select your desired table design and then click OK on the Create Table popup box. A couple of points to note:

• Make sure the whole dataset is included in the selection
• Check the box for ‘My table has headers’.

With your data stored in a table you can now create the PivotTable.

Create your PivotTable

This time you need to go to the Insert tab (1) on the Ribbon and select PivotTable (2) –> From Table/Range (3).

Select the table that you created earlier (if it is the only table in the Workbook it should default to the name ‘Table1’) and place the PivotTable on a new worksheet. You will now have a new sheet with a blank PivotTable that looks something like this:

From here you create your PivotTable by dragging the appropriate fields in the top right section down to the Filters, Rows, Columns and Values boxes in the lower right. Here is a brief explanation of each of these boxes:

• Values are the things you are measuring. They contain two components: the field you are measuring (e.g. Event name) and the calculation you are applying to get those values (e.g. SUM or COUNT)
• Rows are the groupings you are performing your calculations on. Each value in the selected field will have its own row in the PivotTable showing its calculated value. For example, placing Event name into the Rows section, and Count of Event name in the Values section, would show you the number of events performed for each Event name
• Columns allow you to split the data further into buckets. If you were to add User full name as a Column to the above example, you would get one column for each user showing how many times they performed each event
• Filters allow you to select one or more values from the selected field to filter the data on. Adding the Event context to the Filters would then give you the option to see the counts, by user, for one or more specific event context.

Applying the field selections discussed in the example above results in this PivotTable:

Using your PivotTable

I will finish this article by discussing what you could learn from the sample PivotTable we have created above. Remember we are looking at how many times each user has performed each action (event) in the course. What stands out for you? I can see the following:

• Administrator 1 has been a lot more active than Administrator 2
• User 5 looks to be the most active while User 2 looks to be the least active
• Only User 5 has made use of the Course user report.

Here we have looked at just one way of using a PivotTable for our course logs data. We could change the fields we use in the columns and rows or our selection for the values to get a very different view of our dataset. In the next article I will show you some other ways of using a PivotTable with our course logs and examine what the data can tell us.

• About
• Latest Posts

Jeff Mitchell

P&C Reporting and Data Analyst at Fisher & Paykel Appliances

Jeff is passionate about the role of learning and development, and has a specific interest in how people and organisations can be developed in order to achieve their potential. Jeff has a keen interest in information technology and specifically data analysis and the e-learning space.

Latest posts by Jeff Mitchell (see all)

• Version Control – Git GUI – 7th March 2023
• Version Control – Git Command Line Tool – 7th February 2023
• Version Control – Creating Repositories – 7th January 2023