Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.12 HR: Headcount, Attrition and Salary

By Last updated:

Example first. Shraddha Bagale handles HR reports for a fictional delivery company and wants headcount, attrition and pay on one page.

HR project — in 30 seconds

What: A HR tracker: one Table tblStaff, the calculated column AnnualCTC, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real HR report: Active Headcount, Attrition %, Average Salary (Active) are the numbers a manager reads first.

Tiny example: From 6 rows: Active Headcount = 4, Attrition % = 33.3%, Average Salary (Active) = ₹33,250.

First look at the data (before → after):

HR project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

EmpID AnnualCTC
E-301 ₹3,36,000
E-302 ₹3,84,000
E-303 ₹3,12,000
E-304 ₹4,92,000
E-305 ₹3,60,000
E-306 ₹4,56,000

Expected result — KPI cells (expected results)

KPI Formula Result
Active Headcount =COUNTIF(tblStaff[Status],"Active") 4
Attrition % =COUNTIF(tblStaff[Status],"Left")/ROWS(tblStaff[Status]) 33.3%
Average Salary (Active) =AVERAGEIFS(tblStaff[Salary],tblStaff[Status],"Active") ₹33,250
Before table and formula as text

Before — Table tblStaff (fictional sample rows)

EmpID City Dept Salary Status
E-301 Pune Operations 28,000 Active
E-302 Pune Sales 32,000 Left
E-303 Nashik Operations 26,000 Active
E-304 Nagpur Finance 41,000 Active
E-305 Kolhapur Sales 30,000 Left
E-306 Nashik Finance 38,000 Active

Formula / click path

AnnualCTC: =[@Salary]*12
Active Headcount: =COUNTIF(tblStaff[Status],"Active")
Attrition %: =COUNTIF(tblStaff[Status],"Left")/ROWS(tblStaff[Status])
Average Salary (Active): =AVERAGEIFS(tblStaff[Salary],tblStaff[Status],"Active")

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblStaff in Table Design › Properties › Table Name.
  2. Add the column AnnualCTC with =[@Salary]*12; the Table fills it down.
  3. On a KPI sheet type =COUNTIF(tblStaff[Status],"Active") and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and AnnualCTC in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Dept.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build an HR sheet: annual CTC per employee, active headcount, attrition % for this simple practice (people who left ÷ all people in the list), average salary of active staff, CTC by city, a chart and a Dept slicer.

Calculated column formulas:

Column Formula
AnnualCTC =[@Salary]*12

KPI cells (check your numbers):

KPI Formula Result
Active Headcount =COUNTIF(tblStaff[Status],"Active") 4
Attrition % =COUNTIF(tblStaff[Status],"Left")/ROWS(tblStaff[Status]) 33.3%
Average Salary (Active) =AVERAGEIFS(tblStaff[Salary],tblStaff[Status],"Active") ₹33,250

PivotTable (expected):

City Sum of AnnualCTC
Kolhapur ₹3,60,000
Nagpur ₹4,92,000
Nashik ₹7,68,000
Pune ₹7,20,000
Grand Total ₹23,40,000

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Dept and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template HR_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of AnnualCTC?
  2. Filter the Table on City = Nashik (Data › Filter): what is Active Headcount for Nashik alone?
  3. Add the Clustered Column PivotChart and the Dept slicer to your Dashboard sheet.
Answer

Nashik (₹7,68,000). For Nashik only (2 rows): Active Headcount = 2. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Write the definition of Active Headcount under its KPI card in small text. Two people reading the same dashboard should agree on what the number means.

Loading practice tasks...

  1. Total leave days (Easy): Add up Leave days in D2:D11.
  2. Average salary (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Sales (Easy): How many rows have Department "Sales"?
  4. Largest leave days (Easy): The largest value in D2:D11.
  5. Leave days for Sales (Medium): Total of D2:D11 where B is "Sales".
  6. Sales in Seattle (Medium): Total of D2:D11 where B is "Sales" and C is "Seattle".
  7. Best in Sales (Medium): Largest E2:E11 value where B is "Sales".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Sophie Martin (Medium): The Leave days for "Sophie Martin" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Sales (Hard): Share of D2:D11 that is "Sales", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →