HR and Employee Dashboard Excel Practice Questions with Solutions

Introduction

An HR & Employee Dashboard helps an organization monitor employee-related information in one place. HR teams can use dashboards to analyze employee count, departments, salaries, attendance, leave, performance, hiring, attrition, and employee costs. In this chapter, you will practice creating practical HR dashboard sections using Excel formulas, KPI cards, charts, summary tables, and interactive filters. HR and Employee Dashboard Excel Practice questions with solutions to help you understand the concepts.

Question 1: Create an Employee KPI Dashboard

Problem Statement

An HR department wants to create basic KPI cards showing the total number of employees, average salary, total salary expense, and average employee age.

Excel Data

Excel Formulas

Total Employees:

=COUNTA(A2:A13)

Average Salary:

=AVERAGE(E2:E13)

Total Salary Expense:

=SUM(E2:E13)

Average Age:

=AVERAGE(D2:D13)

Expected Result

Concepts Covered

• HR KPI cards
• COUNTA
• AVERAGE
• SUM
• Employee dashboard

Question 2: Create a Department-Wise Employee Dashboard

Problem Statement

Create a dashboard section showing employee count and total salary expense for each department.

Excel Data

Excel Solution

Create:

1. A column chart for Department vs Employee Count.
2. A bar chart for Department vs Salary Expense.
3. A KPI for total employees.
4. A KPI for total salary expense.

Total Employees:

=SUM(B2:B7)

Total Salary Expense:

=SUM(C2:C7)

Expected Result

Concepts Covered

• Department analysis
• Employee count
• Salary expense
• Column chart
• Bar chart
• HR dashboard

Question 3: Create an Employee Attendance Dashboard

Problem Statement

HR wants to monitor employee attendance. Create KPIs for total working days, present days, absent days, and attendance percentage.

Excel Data

Excel Formulas

Total Working Days:

=SUM(B2:B11)

Total Present Days:

=SUM(C2:C11)

Total Absent Days:

=SUM(D2:D11)

Attendance Percentage:

=SUM(C2:C11)/SUM(B2:B11)

Expected Result

Create a bar chart showing Present Days and Absent Days for each employee.

Concepts Covered

• Attendance dashboard
• SUM
• Percentage calculation
• Employee attendance analysis
• HR charts

Question 4: Create a Leave Analysis Dashboard

Problem Statement

Create an HR dashboard showing different types of employee leave and their totals.

Excel Data

Excel Solution

Create a Total Leave column:

=SUM(B2:E2)

Copy the formula down.

Calculate total leave by category:

=SUM(B2:B11)

Repeat for Sick Leave, Earned Leave, and Other Leave.

Create a column chart comparing the four leave categories.

Expected Result

Concepts Covered

• Leave analysis
• SUM
• Employee leave tracking
• Category comparison
• HR dashboard charts

Question 5: Create a Salary Analysis Dashboard

Problem Statement

HR wants to understand salary distribution across employees. Create KPIs for minimum salary, maximum salary, average salary, and total salary expense.

Excel Data

Excel Formulas

Minimum Salary:

=MIN(C2:C13)

Maximum Salary:

=MAX(C2:C13)

Average Salary:

=AVERAGE(C2:C13)

Total Salary:

=SUM(C2:C13)

Expected Result

Create a bar chart showing Employee vs Salary.

Concepts Covered

• Salary analysis
• MIN
• MAX
• AVERAGE
• SUM
• Salary visualization

Question 6: Create an Employee Performance Dashboard

Problem Statement

HR wants to analyze employee performance ratings. Create a dashboard showing average performance score, highest score, lowest score, and department-wise average performance.

Excel Data

Excel Formulas

Average Performance:

=AVERAGE(C2:C13)

Highest Score:

=MAX(C2:C13)

Lowest Score:

=MIN(C2:C13)

Expected Result

Create a bar chart showing employee performance scores.

Concepts Covered

• Performance dashboard
• AVERAGE
• MAX
• MIN
• Employee performance chart

Question 7: Create a Hiring & Recruitment Dashboard

Problem Statement

The HR team wants to track recruitment activity by month. Create KPIs for applications received, interviews conducted, offers made, and employees hired.

Excel Data

Excel Solution

Calculate yearly totals using SUM.

Applications:

=SUM(B2:B13)

Interviews:

=SUM(C2:C13)

Offers:

=SUM(D2:D13)

Hired:

=SUM(E2:E13)

Create a line chart for Applications and Hired.

Expected Result

Concepts Covered

• Recruitment dashboard
• Hiring analysis
• Monthly HR reporting
• SUM
• Line charts

Question 8: Create an Employee Attrition Dashboard

Problem Statement

HR wants to analyze employee exits and calculate the monthly attrition rate.

Excel Data

Excel Solution

Add an Ending Employees column:

=B2+C2-D2

Add an Attrition Rate column:

=D2/B2

Format Attrition Rate as Percentage.

Create:

• Line chart for Employees Left
• Line chart for Attrition Rate
• KPI for total employees left
• KPI for total new hires

Expected Result

The dashboard should display monthly employee exits and attrition rate.

Concepts Covered

• Employee attrition
• Hiring vs exits
• Percentage calculation
• HR trend analysis
• Line charts

Question 9: Create an Employee Cost Dashboard

Problem Statement

Management wants to analyze the total monthly employee cost, including salaries, bonuses, benefits, and training expenses.

Excel Data

Excel Solution

Add a Total Employee Cost column:

=SUM(B2:E2)

Copy the formula down.

Create a line chart showing Total Employee Cost by month.

Create KPI cards for:

• Total Salary Cost
• Total Bonus
• Total Benefits
• Total Training Cost
• Total Employee Cost

Expected Result

The dashboard should show the monthly employee cost and its major components.

Concepts Covered

• Employee cost analysis
• SUM
• Monthly cost trends
• HR financial reporting
• KPI cards

Question 10: Build a Complete Interactive HR & Employee Dashboard

Problem Statement

Create a complete HR dashboard using employee-level data.

The dashboard should allow HR managers to analyze:

• Employee count
• Department
• Salary
• Attendance
• Performance
• Leave
• Employment status
• Location

Excel Data

Excel Solution

Convert the dataset into an Excel Table named:

HRData

Create KPI Cards

Total Employees:

=COUNTA(A2:A17)

Average Salary:

=AVERAGE(E2:E17)

Average Attendance:

=AVERAGE(F2:F17)

Average Performance:

=AVERAGE(G2:G17)

Total Leave Days:

=SUM(H2:H17)

Create Dashboard Charts

Create the following visualizations:

1. Employees by Department

Use a column chart.

2. Employees by Location

Use a bar or doughnut chart.

3. Salary by Department

Use a column chart.

4. Attendance by Employee

Use a bar chart.

5. Performance by Employee

Use a bar chart.

6. Leave Days by Employee

Use a column chart.

Add Slicers

Add slicers for:

• Department
• Location
• Status

Suggested Dashboard Layout

----------------------------------------------------------------
                    HR & EMPLOYEE DASHBOARD
----------------------------------------------------------------

 TOTAL EMPLOYEES   AVG SALARY   AVG ATTENDANCE   AVG PERFORMANCE
      ____          ₹_____          ____%             _____

                    TOTAL LEAVE DAYS
                          _____

----------------------------------------------------------------

       EMPLOYEES BY DEPARTMENT       EMPLOYEES BY LOCATION
                [Chart]                     [Chart]

----------------------------------------------------------------

          SALARY BY DEPARTMENT       PERFORMANCE BY EMPLOYEE
                [Chart]                     [Chart]

----------------------------------------------------------------

          ATTENDANCE BY EMPLOYEE       LEAVE DAYS BY EMPLOYEE
                [Chart]                     [Chart]

----------------------------------------------------------------

 Department       Location          Status
 [Slicer]         [Slicer]          [Slicer]

----------------------------------------------------------------

Expected Result

The final dashboard should provide a single-page HR report containing:

• Total employee count
• Average salary
• Average attendance
• Average performance
• Total leave days
• Department analysis
• Location analysis
• Salary analysis
• Attendance analysis
• Performance analysis
• Leave analysis
• Interactive slicers

Concepts Covered

• Complete HR dashboard
• Employee KPIs
• Salary analysis
• Attendance analysis
• Performance analysis
• Leave analysis
• Department analysis
• Location analysis
• Excel Tables
• Charts
• Slicers
• Interactive HR reporting

Key Takeaways

• HR dashboards convert employee data into useful management information.
• Employee count is one of the most basic HR KPIs.
• Salary dashboards can show average, minimum, maximum, and total salary costs.
• Attendance percentage helps monitor workforce attendance.
• Leave dashboards help HR understand employee leave patterns.
• Performance dashboards can compare employee performance scores.
• Recruitment dashboards can track applications, interviews, offers, and hiring.
• Attrition dashboards can monitor employees leaving the organization.
• Employee cost dashboards can combine salary, bonus, benefits, and training costs.
• Department and location filters make HR dashboards easier to analyze.
• Slicers can make employee dashboards interactive.
• An HR dashboard should present important information clearly without overcrowding the worksheet.

FAQs

1. What is an HR Dashboard in Excel?

An HR Dashboard is a visual Excel report used to monitor employee-related information such as employee count, attendance, salary, leave, performance, recruitment, and attrition.

2. Which KPIs are commonly used in an HR Dashboard?

Common HR KPIs include Total Employees, Average Salary, Attendance Percentage, Average Performance, Leave Days, New Hires, Employee Attrition, and Employee Cost.

3. Can Excel be used to track employee attendance?

Yes. Excel can calculate present days, absent days, attendance percentages, and employee-level attendance trends.

4. Can an HR Dashboard analyze salaries?

Yes. Salary data can be analyzed by employee, department, location, or other categories.

5. What is an employee attrition dashboard?

An employee attrition dashboard tracks employees leaving an organization and can compare exits with new hires over time.

6. How can HR analyze employee performance in Excel?

HR can use performance scores to calculate averages, identify high and low scores, compare departments, and create charts.

7. Can an HR Dashboard include employee leave information?

Yes. Leave information can be summarized by employee, leave type, department, or month.

8. What are HR dashboard slicers used for?

Slicers allow users to interactively filter employee information such as Department, Location, or Employment Status.

9. Why should HR data be stored in an Excel Table?

An Excel Table keeps employee data structured and makes filtering, formulas, sorting, and dashboard updates easier.

10. Can an HR Dashboard be used for management reporting?

Yes. A well-designed HR dashboard can provide management with a quick view of workforce size, costs, attendance, performance, recruitment, and employee movement.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar