Calculating Gender Diversity % in Excel

Gender diversity is one among many different aspects of diversity, A measure of Gender diversity has become a critical part of Human Resource management in recent years.

For more on this topic, please see

A study by Mckinsey on ‘Women in the Workplace 2020’

An article by Forbes on ‘What to come in 2021 for Diversity and Inclusion’

Gender Diversity in Workplace

Setting a target for what % of the employee base should be women can differ from country to country and company to company. Discussing that is beyond the scope of this article.

In this article, we will focus on the technical side of measuring gender diversity. I will explain how we can calculate the Gender Diversity % using formulas in Excel.

Data

I have shown here a dataset of employee data where we have

1. Employee ID
2. Employee Name
3. Start date of employment
4. Current status of employee (Active/Inactive)
5. Gender

As always, let’s convert the data to an Excel table by pressing Control+T. Name the table as that is a good practice while working in Excel.

Excel Tables

Video Demo

Definition

We will define Gender Diversity as % of Women in the active employee base.

Formula

As per the definition, we calculate the numerator (# of active employees who are females) and then divide by the denominator (total # of active employees)

• Numerator: We are using COUNTIFS function to count the number of employees from T_EMP table where Gender equals “Female” and Status equals “Active”.
  • COUNTIFS function allows us to count based on more than one criterion or condition.
• Denominator: We use a COUNTIF function to count the number of employees whose Status equals “Active”.
• We divide Numerator by Denominator.

The formula will result in a value as shown below.

In this case, 2 Female employees and 6 Male employees are active. 2 / 8 = 0.25

To display this as %, we have multiple options.

One would be to just select the cell and apply % number format from menu as shown below.

By default, Excel will show 25.00%. You can decrease the number of decimal places.

Another option is to press Control+1 to open this ‘Format cells’ dialog box. Choose Percentage and enter 0 for Decimal places.

The final result will appear like this.

If the data had 5 females and 5 males as active employees, it would appear as below.

Functions: COUNTIFS, COUNTIF

Conclusion

I hope this was useful. How do you calculate the Gender Diversity % in your company? I would love to hear from you in the comments section.

Read More

• How to calculate number of Employees?
• How to calculate average employee tenure?