4.3 Tools to Compile Data & Presenting HR Reports
Key Takeaways
A clean HR spreadsheet stores one record per row and one field per column, with consistent codes and dates, before any formula or chart is built.
COUNTIF, SUMIF, AVERAGE, MEDIAN, and lookup functions such as XLOOKUP or VLOOKUP handle most routine HR counts, totals, and data matching.
A pivot table summarizes large data sets by category, for example separations by department and reason, without changing the source data.
Bar charts compare categories, histograms show the distribution of a continuous measure, line charts show trends over time, and pie charts show parts of one whole with only a few slices.
HR reports should state the data period and source, aggregate or remove personal data the audience does not need, and avoid reporting groups so small that individuals can be identified.
Tools to Compile Data & Presenting HR Reports
Quick Answer: HR compiles data with spreadsheets, HRIS report writers, survey platforms, statistical software, and dashboard tools, then presents findings with charts and short explanations. Choose the chart for the question: a bar chart to compare categories, a histogram to show how values are distributed, a line chart to show a trend over time, a pie chart for a few parts of one whole, and a table when readers need exact numbers. Every report should be accurate, clearly labeled, and privacy-safe.
The aPHRi outline lists tools to compile data (for example, spreadsheets and statistical software) and reporting and presentation techniques (for example, histograms and bar charts). Section 4.2 explains how data is collected and summarized statistically; this section shows how an early-career HR professional turns raw data into a report a manager can use.
1. The HR Data Toolkit
| Tool | What it is good for | HR examples |
|---|---|---|
| Spreadsheet (such as Excel or Google Sheets) | Flexible calculations, lists, quick charts | Turnover calculations, training trackers, salary survey comparisons |
| HRIS report writer | Standard reports straight from the system of record | Headcount by department, new-hire lists, leave balances |
| Survey platform | Collecting and summarizing feedback | Engagement, pulse, and exit surveys |
| Statistical software (such as SPSS, R, or Python) | Larger data sets and advanced analysis | Pay-equity regression, attrition prediction |
| Dashboard or business intelligence tool (such as Power BI or Tableau) | Interactive visuals that refresh automatically | Monthly HR dashboard for executives |
Early-career HR roles rely mostly on spreadsheets and HRIS reports, so those skills matter most.
2. Building a Clean Spreadsheet
Good analysis starts with a well-structured data set:
- One row per record (one employee, one hire, one separation).
- One column per field (employee ID, department, start date, separation date, reason).
- Consistent codes (for example, always "VOL" or "INV" for voluntary or involuntary), enforced with data validation drop-down lists.
- Real dates and numbers, not text that only looks like a date.
- No merged cells or blank rows inside the data.
- A unique identifier (employee ID) instead of names wherever possible.
Essential functions
| Function | What it does | HR example |
|---|---|---|
| SUM | Adds values | Total overtime hours for a month |
| AVERAGE / MEDIAN | Mean or middle value | Average and median time to fill |
| COUNTIF / COUNTIFS | Counts rows meeting one or more conditions | Voluntary separations in the Sales department |
| SUMIF / SUMIFS | Adds values that meet conditions | Total recruitment spend by channel |
| IF | Returns one value or another based on a test | Flag employees whose probation ends this month |
| XLOOKUP / VLOOKUP | Finds a value in another table | Pull each employee's department from the HRIS extract |
For example, with separation reasons in column E and departments in column C, the formula =COUNTIFS(C:C,"Sales",E:E,"VOL") counts voluntary separations in Sales.
Pivot tables
A pivot table summarizes thousands of rows by category without changing the source data. Dragging "Department" into rows, "Separation type" into columns, and "Employee ID" into values (as a count) instantly produces a separations-by-department table that can be refreshed each month.
3. Data Quality Checks
Before sharing results, check:
- Completeness: are any departments or months missing?
- Duplicates: does anyone appear twice?
- Consistency: do headcount totals match the HRIS and payroll?
- Outliers: is a salary of 5,000,000 a real value or a typing error?
- Definitions: does "turnover" include retirements and interns, and is the base headcount or FTE?
4. Choosing the Right Chart
| Question the audience asks | Best chart | Why |
|---|---|---|
| Which category is biggest? | Bar chart | Lengths are easy to compare across categories |
| How are values spread out? | Histogram | Shows the distribution of a continuous measure, such as salaries grouped into ranges |
| How has something changed over time? | Line chart | Shows trends and seasonality across months or years |
| What share of one whole does each part make up? | Pie chart | Works only with a few parts that add up to 100% |
| Are two measures related? | Scatter plot | Shows correlation, such as training hours and performance scores |
| Which few causes create most of the problem? | Pareto chart | Bars in descending order plus a cumulative line |
| What is the exact figure? | Table | Readers can look up precise values |
Bar chart vs. histogram: a bar chart compares separate categories (departments, exit reasons), and the bars can be reordered. A histogram shows ranges of one numeric variable (for example, tenure of 0-1, 1-2, and 2-3 years), so the bars follow the number line and touch each other.
5. Presentation Techniques
- Know the audience. Executives want the headline and the decision needed; line managers want details for their team.
- Lead with the conclusion. "Voluntary turnover in Operations doubled after the shift change" is more useful than a page of numbers.
- One message per chart, with a title that states the message.
- Label axes and units, and start bar-chart axes at zero so differences are not exaggerated.
- Avoid clutter such as 3D effects, many colors, and unnecessary gridlines.
- State the source and period (for example, "HRIS extract, January-June").
- Protect privacy. Aggregate data and avoid reporting very small groups where individuals could be identified, such as the pay of the only engineer in one office.
- Add context, such as last year's figure, a target, or a benchmark.
6. Worked Example
An HR assistant is asked why employees are leaving. The exit-survey export has 50 voluntary leavers with one main reason each. A pivot table counts reasons, and a bar chart sorted from largest to smallest shows that pay and career growth account for 26 of 50 exits (52%). The report headline reads: "Pay and limited career growth explain half of voluntary exits this year," followed by the chart, a small table of exact counts, and a note that the data covers January to June.
An HR assistant must show managers how employee salaries are spread across ranges such as 30,000-39,999, 40,000-49,999, and 50,000-59,999. Which chart is most appropriate?
Pie chart
Line chart
Scatter plot
Histogram
A spreadsheet lists every separation this year with the department in one column and 'VOL' or 'INV' in another. Which tool quickly summarizes separations by department and type without altering the source rows?
A pivot table
Merged cells
A pie chart of all rows
Manually retyping the totals into a new sheet
A monthly HR dashboard shows the average salary for each office. One office has only one employee in a specialist role. What should HR do before sharing the dashboard widely?
Share it unchanged because averages are always anonymous.
Replace the average with the employee's exact salary for accuracy.
Aggregate or suppress that small group so the individual's pay cannot be identified.
Delete the office from the HRIS.
Sections you finish are checked off in the contents.