How to Calculate Monthly New Hires and Departures using PivotTables
Question details
The user needs to analyze HR data by counting the number of new hires and departures per month, and calculating the percentage breakdown of employee departures for various reasons using a PivotTable.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking employee retention, hiring trends, and turnover rates from an HR dataset.
- Observed behavior
- The goal is to successfully group hire and departure dates by month and display departure reasons as a percentage of the total headcount using a PivotTable.
Ensure your employee dataset is formatted as a continuous table without blank column headers, and verify that all entries in the Hire Date and Departure Date columns are stored as valid dates rather than text.
Create a PivotTable to Group Dates and Calculate Turnovers
Use the grouping feature and value field settings in a PivotTable to calculate monthly trends and departure percentages.
To track hires and departures effectively, you will build a PivotTable that groups individual dates into monthly summary buckets. Using the 'Show Values As' feature will then allow you to translate raw departure counts into helpful percentages.
Select your entire employee data table, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it in a New Worksheet and click 'OK'.
In the PivotTable Fields pane, drag 'Hire Date' into the Rows area. Right-click any date in the PivotTable, select 'Group', choose 'Months', and click 'OK'. Then, drag the 'Name' or 'Employee ID' field into the Values area to get the Count of Hires per month.
To track departures separately, insert a second PivotTable using the same source data. Drag 'Departure Date' into the Rows area and group it by Months. Drag the 'Name' field into the Values area to get the Count of Departures per month.
In a PivotTable, drag 'Departure Reason' into the Rows area and 'Name' into the Values area. To show percentages, click the drop-down arrow on the 'Count of Name' field in the Values area, select 'Value Field Settings', navigate to the 'Show Values As' tab, select '% of Grand Total' from the drop-down list, and click 'OK'.
Easily Analyze HR Data with WPS Spreadsheets
WPS Spreadsheets provides robust and user-friendly PivotTable features to help HR professionals track employee hires, departures, and turnover rates effortlessly.
- 1. Open data in WPS: Launch WPS Spreadsheets and open your HR dataset.
- 2. Insert a PivotTable: Go to the 'Insert' tab, select 'PivotTable', and define your data range.
- 3. Group dates: Drag 'Hire Date' to Rows, right-click the date values, and select 'Group' by Month.
- 4. Show percentages: Drag 'Departure Reason' to Rows and 'Name' to Values, then adjust the Value Field Settings to 'Show Values As % of Grand Total'.
Frequently Asked Questions
Why can't I group my hire dates by month in the PivotTable?
This issue typically occurs if the date column contains blank cells or text values instead of actual dates. Select the column, ensure its format is set to 'Short Date', and clear any invalid text entries. Once corrected, refresh the PivotTable and try grouping again.
Can I count both new hires and departures in a single PivotTable?
Because hires and departures are based on two different date columns, standard PivotTables require you to create separate tables for each. Alternatively, you can restructure your source data to have a single 'Date' column and an 'Event Type' (Hire/Departure) column to analyze both in one table.
How do I show both the departure count and the percentage side by side?
You can drag the 'Name' or 'Employee ID' field into the 'Values' area twice. Keep the first one as 'Count' to show the raw numbers, and modify the second one's Value Field Settings to 'Show Values As > % of Grand Total' to show the percentage.
