Video Tutorial Overview: Creating Attendance Tracking HR Dashboard in Excel

Introduction

• Tutorial on creating an attendance tracking HR dashboard using Microsoft Excel.
• Suitable for both beginners and experienced users.
• Aim: To master essential techniques for building interactive dashboards that improve decision-making.
• Template available at other-levels.com.

Dashboard Features

• Purpose: Monitor, analyze, and improve employee attendance.
• Key Features:
  • Total employee count.
  • Employee gender breakdown (counts and percentages) with a pie chart.
  • Departmental analysis using a column chart.
  • Detailed employee insights including present count, weekly off, planned leave, unplanned leave, attendance percentage, and leave percentage.
  • Dynamic line chart for trend analysis.
  • Filtering options by employee name and month.

Employee Information Sheet

• Data Includes: Employee ID, name, department, position, age, and gender.
• Automatically linked to monthly attendance tracker sheets for data synchronization.

Template Setup Instructions

Monthly Attendance Tracker Sheets

1. Employee Data Linking:

   • Link employee ID and name from employee information sheet.
   • Duplicate January sheet to avoid re-typing employee information.

2. Column Setup:

   • Present Count: Use formula =IF(J11="", "", COUNTIF(J11:A11, "P")).
   • Weekly Off/Planned/Unplanned Leave: Similar formula, count respective codes (W, PL, UPL).
   • Percentages:
     • Attendance: =IF(J11="", "", C11/SUM(C11, E11, F11)).
     • Planned/Unplanned Leave: Adjust formula to respective columns.

3. Daily Attendance Input:

   • Use drop-down list: Options P, W, PL, UPL.
   • Set up using data validation.

4. Formatting and Visualization:

   • Use conditional formatting for visual clarity.
   • Set day name above each date using formula =TEXT(J10, "ddd").

Conclusion

• Learn to set up customized dashboards without focusing too much on design.
• Further design tutorials are available on the YouTube channel.
• Subscribe for future content and visit other-levels.com for templates and more tools.

Call to Action

• Like, subscribe, and visit the website for more resources.
• Stay tuned for upcoming videos.