Once every registration is in one place, you can start asking questions. How many hours go to teaching in each section? Who has registered more than their norm? How is supervision spread across job titles? In the pivot tool you drag the fields you want into place, and the table does the maths.
How a pivot table is built
A pivot table has four areas. The fields you can use are grouped into Activity, User, Course and Registration, and you drag each one into the area where you want it.
Filters
Limit which data is included.
Columns
Spread the figures across.
Rows
Group the figures downwards.
Values
What gets calculated.
Start from a template or a saved preset
When you open the pivot, you land on a start page first. Under Start new you pick a Blank pivot or one of three templates with rows, columns and values already in place. Under Your presets you'll find the presets you've saved yourself or that colleagues have shared with you, with the most recently used at the top. If the list gets long, you can search it by name.
The data loads in the background while you choose, so the table is ready straight away. The Start button in the toolbar takes you back to the start page. If you've changed a preset without saving, Excelsior asks before the changes are discarded.
Blank pivot
Build your own analysis from scratch.
Hours per employee
Equivalent hours per section and employee by year.
Activities per category
Registrations per category and activity by year.
Courses
Equivalent hours per department and course by term.
Example 1: Hours per section and category
The first question is usually how time is spread. Drag Section into rows, Category into columns and Equivalent Hours into values, then pick the year you want in the Year filter.
| Section | Teaching | Supervision | Administration | Total |
|---|---|---|---|---|
| Section A | 3,420.00 | 1,180.00 | 640.00 | 5,240.00 |
| Section B | 2,875.50 | 1,410.00 | 515.00 | 4,800.50 |
| Section C | 1,960.00 | 720.00 | 380.00 | 3,060.00 |
| Total | 8,255.50 | 3,310.00 | 1,535.00 | 13,100.50 |
To go one level deeper, add Name below Section in rows. You can then expand each section and see every member of staff. Double-click a cell and the pivot shows the registrations behind the number.
Example 2: Who is above or below their norm?
User balance shows the difference between what a member of staff has registered and their capacity for the year. A positive number means more than the norm has been registered, a negative number less. The balance is counted once per person, however many registrations they have.
Put Title and Name into rows and User balance into values. With Advanced format you can colour negative numbers red and large surpluses green, so they stand out.
| Title | Name | User balance |
|---|---|---|
| Professor | Staff member 01 | −130.75 |
| Associate professor | Staff member 02 | 112.50 |
| Associate professor | Staff member 03 | −48.00 |
| Assistant professor | Staff member 04 | 6.25 |
| PhD student | Staff member 05 | 22.00 |
Example 3: One person's account in one click
You don't always have to start from an empty table. Next to a staff member's registrations you'll find Open in pivot. It opens the pivot filtered on that person and year, with Course, Name and Activity already set up as rows. When you open it from a course, it filters on that course too. It's a quick way to go through an account together with the person it belongs to. The last ten layouts you've opened this way are listed under Recently opened on the start page, so you can return to them without looking the person up again.
| Course | Activity | Units | Norm | Equivalent hours |
|---|---|---|---|---|
| Example course I | Course responsibility | 1.00 | 40.00 | 40.00 |
| Lecture | 14.00 | 4.00 | 56.00 | |
| Example course II | Classes | 24.00 | 2.50 | 60.00 |
| No course | PhD supervision | 2.00 | 35.00 | 70.00 |
| Total | 226.00 |
Save the layout and share it with colleagues
When a layout is useful, save it with Save As. Next time it's waiting under Your presets on the start page, with the same layout and the latest figures. Each card shows what's in rows, columns and values, and when the preset was last opened or saved. The card's menu has Rename, Share, Export and Delete.
Share gives colleagues access to the preset. You can only share with active users in your own organisation who have access to the pivot, and for each person you choose Can view or Can edit. The preset isn't copied, so everyone works in the same one. Only the owner can share or delete it. Someone who has been given a preset sees Shared by and the owner's name on the card, and can choose Remove from my list without the preset disappearing for the owner.
Balance per job title
Opened 2 days ago
Shared with 2 people
Rows Title, Name
Values User balance
Share preset
If the figures need to go to management or into a budget, click Export. You get the table as an Excel file, exactly as it looks on screen. A saved preset can also be exported straight from the start page without opening it first.
To show shares, work out your own key figures or compare years, read Get more out of the pivot.
Tips
- Start with one question. "How many supervision hours does each section have?" gives a better table than dragging in every field at once. If the question resembles one of the templates, start there.
- Share the preset instead of sending files. A shared preset always shows the latest figures, whereas an exported file is a snapshot.
- Use filters rather than deleting rows. Put Active in the filters to hide staff who have left, and remove the filter again when you need the whole year.
- Only include the years you need. An administrator chooses which years are included in the pivot. Fewer years make it faster and keep historical personal data out when it isn't needed.