Organize HR data using PivotTable reports

Human resource professionals track a lot of data, and often have to share it with others. A Microsoft PivotTable® report can organize and display your data so that it's clear and useful no matter who your audience is.

Is a PivotTable report more useful than a spreadsheet?

A spreadsheet is perfect for storing and analyzing data. But on a spreadsheet, it's not always easy to grasp the implications of your data at a glance. A PivotTable report sorts the various pieces of information and lets you move them around to see how they all relate to each other.

For example, a hiring manager can view open job requisitions by recruiter, by location, for each month. With this information, the recruiting team can create personalized hiring plans and progress reports.

You could do all this with a spreadsheet and formulas, but it's often easier with a PivotTable report.

Organizing your hiring data in a PivotTable report

The following procedure steps you through the process of organizing your hiring data. At the end of the procedure, you will view the data in multiple ways by moving different sets of data on and off the PivotTable report.

Prepare your data

Start by getting your data into a Microsoft Excel worksheet. If it's not already in Excel, you can import data from an existing spreadsheet into a new Excel worksheet.

An HR spreadsheet

HR spreadsheet

Create a blank PivotTable report

  1. In your Excel worksheet, select the columns and rows that contain the data from which you want to create a PivotTable report.
  2. On the Data menu, click PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard.

PivotTable Wizard - Step 1 of 3

  1. In step 1 of the PivotTable and PivotChart Wizard , under Where is the data that you want to analyze?, click Microsoft Excel list or database.
  2. Under What kind of report do you want to create?, click PivotTable.
  3. Click Next.
  4. You can now see the range of data that you selected to use in your PivotTable report. In this example, the range is column A, row 1 through column H, row 21.

PivotTable Wizard - Step 2 of 3

  1. Click Next to continue.
  2. In step 3 of the PivotTable and PivotChart Wizard, under Where do you want to put the PivotTable report?, click New worksheet, and then click Finish.

PivotTable Wizard - Step 3 of 3

Look at it this way!

After you click Finish in the PivotTable and PivotChart Wizard, a blank PivotTable report opens in a new worksheet in your Excel workbook — and you're ready to start arranging your data! Now you can move your fields around to categorize and organize your data.

A PivotTable Field List and blank diagram

PivotTable Field List and blank diagram

Start building your PivotTable report

Drag the Recruiter field from the PivotTable Field List to the Drop Row Fields Here area of the PivotTable report.

As you can see, it automatically populates the area with the data from the Recruiter column in your worksheet.

The Recruiter field on the PivotTable report

Data from the Recruiter field added to the PivotTable report

Add the Job Req.# field to your report

Drag the Job Req.# field from the PivotTable Field List to the Drop Data Items Here area of the PivotTable report. The PivotTable report now shows a summary of which job requisitions belong to which recruiter.

The Job Req.# field on the PivotTable report

Job Req.# PivotTable Field List

Add the Salary Level field to your report

Drag the Salary Level field from the PivotTable Field List to the same area of the PivotTable report where you dropped the Job Req.# field.

This time, the PivotTable report creates a Data column and moves the Job Req.# field and Salary Level field into that column to show us a summary of which recruiter has what salary level for which job requisition number.

The Salary Level field on the PivotTable report

Salary Level PivotTable Field List

Add the Location field to your report

Drag the Location field from the PivotTable Field List to the area of the PivotTable report where you dropped the Job Req.# field and Salary Level field.

Now the PivotTable report contains another column, called Location, and has automatically populated the area with the original location data from your worksheet. From this table you can see what salary level each recruiter is responsible for, what job requisition number each has been assigned, and what location each one is focusing on.

The finished PivotTable report

Location pivot table field list

Refining your PivotTable report

Now you're ready to try moving some of the data around. For example, to see the start date for each job requisition, click the Location field and drag it off the table. Click the Start Date field and drag it to the Data column. This will allow you to view the start date for each job requisition along with the salary level for every recruiter.

 Note   Don't be concerned about the names of the various areas of the PivotTable report. You can drag any item in the Field List to any part of the PivotTable report. It just takes a bit of practice moving and dragging fields around to see what works best for you.

PivotTable report example

Download the Human Resources PivotTable report template to get started with creating your own PivotTable report. You can use the data within the template or change it to fit your specific needs.

 
 
Applies to:
Excel 2003