Today’s blog is brought to you from Anne Walsh – The Excel Expert 

Anne will explain how to create a Pivot Table to analyse evaluations.

Ah evaluations…the staple of every training course. Often however the information/trends contained in them languishes at the bottom of filing cabinets or on shelves. Until someone in management asks you “can you show me a list of everyone who attended a particular course in a year” or a listing of the training courses someone has attended or how many of a particular type of course did we run? If you have your data set up correctly and you know how to use a pivot table, you can get that information in minutes…

So in this tutorial I want to show you how to use Excel to help you get an overview of training in your organisation. I will be using Excel 2010

What we will cover:

• Data Capture – Capturing the data from a typical training evaluation sheet in an Excel friendly way
• Pivot Tables – Using a pivot table with this list so as to quickly summarise the data. 

Data Capture

This is the part that people often overlook when preparing the data simply because it seems so tedious and unnecessary. It’s tedious in the way chopping the vegetables and washing the meat is – boring but essential. Doing it this way also means that it will be much easier to create the pivot tables that will quickly do the analysis for you.

Let’s take a typical evaluation form that is completed by each participant after the completion of a course.

 We are going to assume that the data has been completed. In this scenario I have listed the attendees and courses run, and the data has been entered on the Data Entry Sheet.

You can see how the sample data is entered in the attached workbook (on the Data Entry Sheet).

 This format is Excel friendly and will allow you to quickly analyse large amounts of data

And to keep it up to date, all you have to do is keep entering the details of the completed evaluation forms in here.

There is a sample workfile attached. The data from the individual training forms is captured here. I have entered 12 sample forms for 3 different courses run at different times.

There are two extra fields called CourseCount which facilitates counting of attendees and Course Occurrence which facilitates course counting. (My thanks to RGonzo of Expert Exchange  Chandoo of Chandoo.org for help with these formulas)

Tips on Data Entry

Repeating a date: If you use the usual autofill for filling in the dates, Excel will create the dates in sequence e.g. if you enter 1/2/2013, and then just drag it down, Excel will enter 2/2/2013 as the next date. To ensure that you repeat 1/2/2013, enter it twice. Highlight BOTH dates and then copy down.

Data Formatting: To ensure that your pivot table creation is as smooth as possible, make sure you do the following:

• Format your headings differently from the rest (just making them bold will make a difference)
• Avoid blanks: No blank rows, no blank columns (as far as possible)
• ID Column: Use an identifier column to uniquely identify each entry (this can be done via autofill in Excel)

Quick Navigation: In order to make it easier to navigate around your sheet do the following:

            Freeze your top headings (Click where you want to freeze the view e.g. B3 in the sample sheet) View: Freeze Panes, Freeze Panes

Keyboard shortcuts: Use Ctrl and Arrow down to get to end of list, Ctrl and Arrow right to navigate to left. Ctrl and Home will always bring you to the top of the sheet.

And of course, use Copy and Paste as much as possible.

Once we have our data entered (and remember Excel 2007 and later has a million rows) we can now start using a pivot table to get meaningful data out of it…

Creating our pivot table

Summary

1. Convert list to a table
2. Summarize using pivot table
3. Construct your pivot table.
4. Refresh after adding new data

 Step 1: Convert list to a table

We convert our list to a table because it makes much easier to refresh and manage the data as we add to it.

1)     Click anywhere in your list

2)     Insert: Table

3)     Click OK.

4)     It’s now a table 

Step 2: Use the pivot field list to construct our pivot table

1)     Click anywhere in your table

2)     You should now see a new ribbon (Design – over on right hand side)

3)     Click on Summarize with PivotTable

4)     Accept the default settings

5)     You should now see something like the following:

We drag the desired fields to where we want them to go.

Now to return to where we started with our three questions (and using the attached sample Excel workbook)

Can you show me a list of everyone who attended a particular course in a year

We can view this in the sheet “Training Courses Attended”. To see the structure of it, click on the pivot table itself and you will see the Field List on the right hand side.

Show me a listing of the training courses someone has attended

We can view this in the sheet “Attendance by Staff Member”. To see the structure of it, click on the pivot table itself and you will see the Field List on the right hand side.

 How many of a particular type of course did we run

We can view this in the sheet “Number of courses run”.  To see the structure of it, click on the pivot table itself and you will see the Field List on the right hand side.

 Step 3: Refresh for when we add more data

If and when we update our pivot table e.g. add new evaluations etc, we need to refresh our pivot table. This is very straight forward.

1)     Click in your pivot table

2)     You will now see a new ribbon called Options.

3)     Click on this and in the middle click on Refresh (

note that if you click on Refresh All, this will update all your pivot table data.

4)     Note that it’s only as simple as this when you have converted to a Table. Otherwise you will have to re-select the data using Change Data Source

Now this is just touching the tip of the iceberg of what you can do with pivot tables but this will help get you started.

Click Here to download the accompanying data sheet. Training_Evaluation_data_sheet

Resources