HR Skills

Creating an HR Dashboard in Excel




An HR Dashboard is a powerful way to visualize HR metrics, track employee data, and generate insights for better decision-making. Here's a step-by-step guide to building a fully functional and visually appealing HR dashboard.

? 1. Plan Your HR Dashboard

Metrics to Include:

  1. Employee Metrics:
  2. Total Employees.
  3. Active vs. Inactive Employees.
  4. Gender Distribution.

  5. Department-Wise Headcount.

  6. Attendance Metrics:

  7. Average Attendance Rate.

  8. Leave Utilization.

  9. Recruitment Metrics:

  10. Open Positions.
  11. Candidates in Each Stage.

  12. Time-to-Hire.

  13. Performance Metrics:

  14. High Performers by Department.
  15. Employees Due for Appraisal.

?? 2. Create the Data Sheets

1. Employee Data

Create a table to track employee details. | Employee ID | Name | Department | Job Title | Gender | Date of Joining | Status | Performance Score (%) |
|-------------|--------------|------------|-----------------|--------|-----------------|-----------|------------------------|
| 001 | John Doe | IT | Software Engineer| Male | 01/01/2020 | Active | 85 |
| 002 | Jane Smith | HR | HR Manager | Female | 05/15/2021 | Active | 92 |

2. Attendance Data

Track employee attendance and leave. | Employee ID | Month | Present Days | Leave Days | Total Days |
|-------------|--------|--------------|------------|------------|
| 001 | Jan-25 | 20 | 2 | 22 |
| 002 | Jan-25 | 22 | 0 | 22 |

Formula:
- Attendance Rate: =Present Days / Total Days * 100.

3. Recruitment Data

Track open positions and candidates. | Position | Department | Stage | Candidates |
|---------------------|------------|-----------------|------------|
| Marketing Manager | Marketing | Interviewing | 5 |
| Sales Executive | Sales | Shortlisted | 3 |

? 3. Build the HR Dashboard

Step 1: Set Up Key Metrics

| Metric | Value |
|-----------------------|------------|
| Total Employees | =COUNTA(Employee ID Range)
| Active Employees | =COUNTIF(Status Range, "Active")
| Gender Distribution | Male: =COUNTIF(Gender Range, "Male")
| Average Attendance | =AVERAGE(Attendance Rate Range)
| Open Positions | =COUNTA(Position Range)

Step 2: Create Visuals

Add charts for a clear overview of HR metrics.

  1. Employee Distribution by Department:
  2. Insert a Pie Chart to show headcount per department.

  3. Data: Create a summary using COUNTIF: | Department | Count |
    |------------|-------|
    | IT | 10 |
    | HR | 5 |

  4. Gender Distribution:

  5. Insert a Doughnut Chart using gender data.

  6. Attendance Trends:

  7. Insert a Line Chart for attendance rates over months.

  8. Recruitment Pipeline:

  9. Insert a Bar Chart for candidates in each stage: | Stage | Candidates |
    |---------------|------------|
    | Shortlisted | 10 |
    | Interviewing | 5 |

Step 3: Add Interactivity with Slicers

  1. Insert a PivotTable for metrics like Department-Wise Headcount.
  2. Add Slicers for filters:
  3. Go to Insert > Slicer and select fields like Department or Status.
  4. Use slicers to dynamically update charts and tables.

? 4. Automating the Dashboard

1. Use Excel Tables for Dynamic Updates

Convert your data ranges into tables: - Select your data range and press Ctrl + T. - Tables auto-expand when you add new rows, keeping charts and calculations updated.

2. Use Conditional Formatting for Alerts

Highlight employees with low attendance: - Select the Attendance Rate column. - Go to Home > Conditional Formatting > Highlight Cell Rules > Less Than and set a threshold (e.g., < 75%).

3. Automate Data Import with Power Query

  • Import data from external sources (e.g., HR software or CSV files).
  • Go to Data > Get Data > From File > From Workbook.

?? 5. Example HR Dashboard Layout

| Metric | Value |
|----------------------------|------------------|
| Total Employees | 50 |
| Active Employees | 45 |
| Average Attendance Rate | 90% |
| Employees Due for Appraisal| 5 |
| Open Positions | 3 |

Dashboard Visuals

  1. Top Section:
  2. Key metrics like Total Employees, Active Employees, and Open Positions.
  3. Left Panel:
  4. Pie Chart: Employee Distribution by Department.
  5. Doughnut Chart: Gender Distribution.
  6. Right Panel:
  7. Line Chart: Attendance Trends.
  8. Bar Chart: Recruitment Pipeline.

? 6. Ready-to-Use Templates

  1. HR Dashboard Template:

  2. Download Here.

  3. Employee Database Template:

  4. Download Here.

  5. Attendance Tracker Template:

  6. Download Here.

? 7. Tips for Success

  1. Secure Data:

  2. Use password protection for sensitive HR files: File > Info > Protect Workbook.

  3. Back-Up Data:

  4. Save your workbook to a cloud platform like OneDrive for real-time backups.

  5. Review Monthly:

  6. Update metrics and review trends to make data-driven HR decisions.
❤ If you liked this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy