HR professionals typically keep track of data points such as recruiting statistics, benefits enrollment, and financial plans. To make the best use of this information, it's a good idea to store it in a flexible format that lets you sort and view it in various ways. A Microsoft PivotTable® report enables you to take the static data in your Microsoft Office Excel worksheets and make it "come to life," telling you things about your company, your employees, regional hiring trends, retention rates, and more.
What's a PivotTable report?
A PivotTable report is an interactive table that quickly combines and compares data. You can rotate its rows and columns to see different summaries of your data, and you can display the details for specific areas of interest.
Need to know how many employees started in a particular month? Not too difficult. But what if you want the results in an easy-to-read format, sorted by location, and then by start date, with subtotals for each? With PivotTable reports, you simply drag your data onto the PivotTable layout area and then look at it in various ways until you see the view that best displays the answer to your question.
From worksheet to PivotTable report
In this example, the PivotTable report takes source data from the worksheet and arranges it so that you can see at a glance that in the San Jose office, 50 employees started work on June 5, 2003, and 50 more started two days later, on June 7, 2003.
Source values for the number of employees in San Jose
Start dates for employees in San Jose
View the total number of employees
Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the report shown in the previous graphic, you can easily see how the total number of employees for Portland compares with San Jose and Seattle, or the total number of employees in the company.
Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages, by clicking Field Settings on the PivotTable toolbar.
Organize data by start date and location
In a PivotTable report, each column or row in your source data becomes a PivotTable field that summarizes multiple rows of information. In the previous example, the Location column becomes the Location field, and each record for Portland, for instance, is summarized in a single Portland item.
A data field, such as Sum of Number of Employees, provides the values to be summarized. Cell C5 in the previous report contains the sum of the Number of Employees value from every row in the source data for which the Location column contains Portland and the Start Date column contains 6/6/2003.
Create a PivotTable report
The most common way to create a PivotTable report is with the PivotTable and PivotChart® Wizard. In the wizard, you select the source data that you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Office Excel summarizes and calculates the report for you automatically.
Note If you're using an Office Data Connection to retrieve external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) for your report, you can return the data directly to a PivotTable report, without running the PivotTable and PivotChart Wizard. Office Data Connections are the recommended method of retrieving external data for your reports when you don't need to combine data from more than one table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) in the external database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) or filter the data to select specific records before creating the report, and for retrieving data from OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) databases.
Customize your PivotTable report
After you create a PivotTable report, you can customize it to change the layout and format by using the Chart Wizard
button and the Format Report
button on the PivotTable
toolbar. This is a simple way to graphically display the data that you need to report on and makes it easy for HR team members — and hiring managers — to view the report.