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

Calculating gender diversity - Employee Data
Calculating gender diversity – Employee 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

=COUNTIFS(T_EMP[Gender],”Female”,T_EMP[STATUS],”Active”)/COUNTIF(T_EMP[STATUS],”Active”)

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.

Result of Gender diversity calculation formula
Result of Gender diversity calculation formula

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.

Gender Diversity % - Choosing Number format from menu
Gender Diversity % – Choosing Number format from menu

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

Decrease decimals
Decrease decimals

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

Format Cells - Choose Percentage - Decimal Places
Format Cells – Choose Percentage – Decimal Places

The final result will appear like this.

Final output of Gender diversity % Calculation
Final output of Gender diversity % Calculation

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

Gender Diversity 50%
Gender Diversity 50%

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

Leave a Reply

Your email address will not be published.