Home>Blogs>Dashboard>HR Attrition and Head Count Analysis Dashboard in Excel – FREE download
Attrition Analysis Dashboard

HR Attrition and Head Count Analysis Dashboard in Excel – FREE download

Welcome to our comprehensive guide on how to create an HR Attrition and Head Count Analysis Dashboard in Excel, complete with a free downloadable template! This user-friendly dashboard, built using Power Pivot and DAX measures, is an essential tool for HR professionals looking to analyze and manage workforce data effectively.

Understanding the Dashboard

Dashboard Sheet Tab: A Visual Overview

Dashboard Sheet tab
Dashboard Sheet tab
  • Interactive Slicers: Filter data based on Phase, Process, Sub Process, Year, Month, Gender, and Tenure.
  • Head Count Card: At the top section, view the total head count.
  • Attrition Analysis: Visualize Total Attrition%, YTD Attrition%, and Annualized Attrition% using semi-circle charts with data labels.
  • Attrition Breakdown: Explore Involuntary and Voluntary Counts on a doughnut chart, with Total attrition number displayed in the center.
  • Gender-Based Analysis: A column chart showcasing Attrition by Tenure (in months), featuring a Gender level drill-down option.
  • Monthly Trends: The combo chart displays Voluntary, In-Voluntary, and Total Attrition by Month.

Table View Sheet Tab: In-Depth Data Analysis

Table View Sheet Tab
Table View Sheet Tab
  • Slicers for Detailed Filtering: Similar to the Dashboard tab, allowing for precise data segmentation.
  • Pivot Table Insights: Displays KPIs such as Head Count, Involuntary/Voluntary Counts, various Attrition Percentages (Voluntary, Involuntary, Total, YTD, Annualized).

Data Sheet Tab: The Backbone of Your Dashboard

Data Sheet Tab
Data Sheet Tab

Structured Data: Organized Excel table format, ready to be integrated into the data model for Power Pivot use.

Pivot Sheet Tab: Behind-the-Scenes Magic

Pivot Sheet Tab
Pivot Sheet Tab

Supportive Role: Although hidden, this tab contains multiple pivot tables that feed into the charts on the dashboard sheet.

Key Performance Indicators (KPIs)

To understand the dynamics of your workforce better, we’ve incorporated crucial KPIs:

  • Total Headcount: Average headcount used for Attrition calculations.
  • Involuntary Count: Number of involuntary separations.
  • Voluntary Count: Number of voluntary separations.
  • Attrition Percentages: Detailed percentages for Involuntary, Voluntary, and Total Attrition.
  • YTD Attrition %: Year-to-date Attrition calculations.
  • Annualized Attrition %: An estimated annual attrition rate, calculated from YTD Attrition %.

Harnessing the Power of DAX

In this dashboard, DAX functions play a vital role in data analysis and visualization. Below is the code of  DAX measures used in the dashboard.

Total Head Count



Monthly Head Count

=VAR CM = MAX ( Data[Month] )
VAR PM = EDATE ( CM, -1 )
VAR CM_HC = CALCULATE ( [Total Head Count], Data[Month] = CM,ALL(Data[Month Name],Data[Year]) )
VAR PM_HC = CALCULATE ( [Total Head Count], Data[Month] = PM,ALL(Data[Month Name],Data[Year]) )


IF (
HASONEVALUE ( Data[Month] ),
DIVIDE ( CM_HC + PM_HC, 2, BLANK () ),
[Total Head Count]


Involuntary Count

=CALCULATE(COUNTROWS(Data),Data[RsnCategory] = "Involuntary")


Voluntary count

= CALCULATE(COUNTROWS(Data),Data[RsnCategory]= "Voluntary")

Voluntary Attrition %

=DIVIDE([Voluntary Count],[Monthly Head Count],BLANK())

In-Voluntary attrition %

=DIVIDE([Involuntary Count],[Monthly Head Count],BLANK())

Total Attrition
=DIVIDE([Involuntary Count]+[Voluntary Count],[Monthly Head Count],BLANK())


YTD Attrition

=VAR YTD_Att =
[Involuntary Count] + [Voluntary Count],
ALL ( Data[Month Name], Data[Year] ),
DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year])
[Total Head Count],
ALL ( Data[Month Name], Data[Year] ),
DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year])


Annualized Attrition % =

VAR Month_Count =
CALCULATE ( DISTINCTCOUNT ( Data[Month] ), DATESYTD ( Data[Month], "3-31" ),ALL(Data[Month Name],Data[Year]) )
[YTD Attrition] * DIVIDE ( 12, Month_Count, BLANK () )

Wrapping Up

Creating an HR Attrition and Head Count Analysis Dashboard in Excel is not just about tracking numbers; it’s about gaining insights that drive strategic decisions. With our easy-to-follow guide and free downloadable template, you’re well on your way to mastering workforce analytics. Happy analyzing!

Visit our YouTube channel to learn step-by-step video tutorials



Click here to download the dashboard file.

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!