Objective & Approach

To assess gender diversity in the hiring process, it was essential to quantify the distribution of hires. I approached this by meticulously filtering the dataset to isolate candidates with a "Hired" status and then segmenting this group by gender.

Key Formulas & Methods

A Pivot Table was the primary tool, configured with 'Status' in the Filters area (set to "Hired") and 'Gender' in the Rows area. 'Count of Gender' was placed in the Values area. Alternatively, the `COUNTIFS` function was used for a formula-based approach:

=COUNTIFS(Data[Gender], "Male", Data[Status], "Hired")

Results: Hires by Gender

The analysis revealed a gender imbalance, with male hires constituting 54.5% of the total, a key finding for diversity and inclusion initiatives.

Gender	Candidates Hired
Male	2,552
Female	1,850
Did Not Disclose	277
Grand Total	4,679

Objective & Approach

To evaluate if compensation is competitive across the organization, I calculated the average salary for new hires within each department. This provides a baseline for ensuring equitable and market-aligned pay structures.

Key Formulas & Methods

This was achieved using a Pivot Table where 'Department' was the Row label and 'Average of Offered Salary' was the Value field. The `AVERAGEIFS` function provides a more direct formulaic method for this calculation:

=AVERAGEIFS(Data[Offered Salary], Data[Department], "Sales Department")

This insight is key for benchmarking. General Management offers the highest average salary at over ₹58,000, while others cluster around ₹49k-₹52k.

Objective & Approach

Understanding salary distribution is crucial for budget planning and compensation strategy. I created salary brackets (class intervals) and visualized the frequency of salaries within each bracket to see where most offers fall.

Key Formulas & Methods

Excel's Histogram tool from the Data Analysis Toolpak is ideal for this. I defined bins (e.g., 25000, 50000, 75000) to group the 'Offered Salary' data. A manual approach involves using the `COUNTIFS` or `FREQUENCY` function for each bracket:

=COUNTIFS(Data[Salary], ">=25000", Data[Salary], "<50000")

The distribution is concentrated in the ₹25k - ₹75k range, with a significant drop-off for higher salary brackets.

Objective & Approach

This task visualized the proportion of employees across departments to understand the company's organizational structure and resource allocation. A pie chart was the ideal tool to show the percentage contribution of each department to the total workforce.

Key Formulas & Methods

A Pivot Table was created with 'Department' in the Rows area and 'Count of Department' in the Values area. To show values as a percentage, I right-clicked the values field and selected "Show Values As" > "% of Grand Total". This feature is fundamental for proportional analysis.

The Operations department is the largest at 38%, suggesting it is central to the business. In contrast, support departments like HR (1.34%) have a much smaller footprint.

Objective & Approach

To analyze the employee distribution across position tiers (job roles/levels), I used a Pivot Table to count employees for each position code. A horizontal bar chart was chosen for clear visualization and easy comparison between tiers.

Key Formulas & Methods

The analysis was conducted using a Pivot Table where 'Post Name' (the position tier identifier) was placed in the Rows area and 'Count of Post Name' was placed in the Values area. This summary was then visualized with a Bar Chart for impact.

Position tiers 'c9' and 'c5' are the most populous, indicating these are likely high-volume, mid-level roles, which is critical information for succession planning and talent development.