Calculating Diversity Percentage % in Excel

The value of diversity in employees has been recognized and appreciated more in recent times. Diversity in all aspects is good for a company.

In this article, I will explain how you can calculate a Diversity Percentage % KPI for your company.

I will use Racial Diversity in my example below. However, this concept can be used for measuring any type of diversity.

Data

There are two parts to the data we deal with, in this example.

First is the list of employees.

As always, please convert the data to an Excel table by pressing Control+T while selecting the data cells. Name the table. Eg. T_EMP

In the above data, we can see the RACE column representing the race of each employee.

The second data we will use here is a table that will tell us how the diversity groups are categorized.

This table provides us the mapping from Race to Group. Group column tells which races are considered Minority and which are Majority.

Calculating Group for each employee

To begin, let’s calculate for each employee the GROUP they belong to.

We will add a new column ‘GROUP’ in our employee table T_EMP. We will enter the following formula in the column.

Formula

1. For each employee, MATCH function finds the position (or row number) in the T_RACE table where the value in RACE column matches the RACE value of the employee.
2. INDEX function extracts the value from the GROUP column at the specific row (defined by previous step) in T_RACE table.

This creates the values for GROUP for all employees, as shown below.

Calculating the Diversity Percentage

Now we will use the familiar formula (like how we did with the Gender Diversity % KPI) to calculate the Diversity Percentage.

Formula

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

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

Formatting as Percentage

For the final step, we will update the Number format.

Click on the cell and press Control+1 to open Format cells dialog box.

Choose the Percentage format and update decimal places as needed.

Now, the output would appear as below.

Functions used: MATCH, INDEX, COUNTIFS, COUNTIF

Conclusion

I hope this was useful. In your company, how do you calculate the diversity of employees in your company? I would love to hear from you in the comments section. If there is a specific metric or KPI you would like me to do a video about, please let me know.

Read More

• Calculating Average Employee Tenure in Excel
• Calculating Number of Employees
• Calculating Gender Diversity