fbpx
Search
Close this search box.

How to calculate the Gender Pay Gap using Excel Formulas? (Free Calculator Template)

Share

Facebook
Twitter
LinkedIn

Gender Pay Gap is the difference in pay for groups of men & women and usually based on the average or median salaries. We can use Microsoft Excel to quickly calculate the GPG (Gender Pay Gap) from your data. In this article, let me explain the process, Excel formulas and offer you a ready to use GPG calculator.

What is Gender Pay Gap?

What is Gender Pay Gap?

According to NZ Government,

Gender pay gaps are differences in pay for groups of women and men, usually based on the median or mean pay that men and women receive.

Source: Statistics New Zealand

How to calculate Gender Pay Gap in Excel?

Gender Pay Gap Excel formulas

Assuming you have average salary of men & women in two cells C3 & C4, we can calculate Gender Pay Gap using the below formulas:

Gender Pay Gap in $s:

=C3 - C4Generalized formula = average of male salary - average of female salary

Percentage Gender Pay Gap:

=(C3-C4)/C3Generalized formula = (average of male salary - average of female salary) / average of male salary

Gender Pay Gap from raw data:

example staff data table

Excel is a great option for identifying and reporting gender pay gap issues when you have full employee data. Let’s say you have the staff data in an Excel table as shown above.

In this case, we can use below formulas to calculate the Gender Pay Gap:

Step 1: Set up your data in as a table

Create a 3-column table in Excel with the staff ID, gender & annualized full-time salary. (Related: Learn how to create a table in Excel)

Name your table as “staff” using the Table Design ribbon in Excel.

Step 2: Calculate male & female average salaries:

You can use AVERAGEIFS function in Excel to calculate the male & female specific average salary.

The formula for male average looks like this:

=AVERAGEIFS(staff[Annualized Full-time Salary],staff[Gender], "Male")

And the formula for female average looks similar.

Step 3: Calculate the Gender Pay Gap in $s and %:

The formulas for this are explained above. They are:

GPG in $s: =Average Male Salary – Average Female Salary

GPG in %: =(Average Male Salary – Average Female Salary) / Average Male Salary

Step 4: Format everything

Format the GPG $ and Salary calculations in your currency formatting (Ctrl Shift 4)

Format the GPG % in Percentage formatting using Excel format cells option (CTRL Shift 5)

Please refer to below illustration for formula set up and help.

Formulas to calculate "average" GPG from raw data

Average vs. Median Gender Pay Gaps

Average vs. median gpg

It is a good idea to calculate both average and median GPG values from your data. We all know that an odd high value can impact the average calculation. May be your CEO is a female and her high $$$ salary thus she bumps up the average female pay significantly.

To calculate the Median Gender Pay Gap values in Excel:

Firstly, calculate the median pay for both male & female groups. Unfortunately, Excel doesn’t have a MEDIANIFS function. So, use the below formula instead:

=MEDIAN(IF(staff[Gender]="Male",staff[Annualized Full-time Salary]))

Caution: Array formula

After typing the formulas, press CTRL+Shift+Enter to get the correct result.

Change the gender value to Female for the respective median salary.

Once both medians are calculated, you can easily calculate the gender pay gap (both in dollars and percentage) using the same formulas as above.

Download FREE Gender Pay Gap Calculator – Excel File

download pay gap calculator excel workbook

Click here to download my Gender Pay Gap calculator template. Copy and paste your data and the file calculates the GPG automatically.

How to get the Hourly Pay Gap values?

how to calculate hourly pay gap

Once you have calculated the Gender Pay Gap in dollars, just divide the number with total annual hours of work. In most countries, this would be 2080 hours (ie 52 weeks times 40 hours per week).

So, for example, if you have a pay gap of $3,117, then the hourly pay gap is $1.50

This means, female staff are earning $1.50 less than their male counterparts every hour.

Our GPG is negative, what does it mean?

A negative GPG value indicates that your female staff are paid more (on average or median basis) compared to the male staff.

Limitations & Problems with Gender Pay Gap statistics:

While Gender Pay Gap offers a great insight into the compensation of men vs women employees, it has a few limitations.

  • GPG doesn’t explain any hierarchical distribution issues. If you have a lopsided distribution of staff in your organization (may be more female staff at lower-level positions and more male staff in senior positions), GPG doesn’t expose this issue. I recommend visualizing the male vs. female distribution by salary bands or seniority for a better insight in to these issues.
  • A low or zero Gender Pay Gap is not enough. If you want an equitable and fair organization, aiming for a zero gender pay gap at aggregate level is not enough. You need to examine GPG by:
    • department level GPG
    • city / location level GPG
    • manager vs. non-manager GPG
    • new hires vs. existing staff GPG
  • GPG is meaningless for small organizations. If your total headcount is less than 30, GPG calculations can be meaningless or less insightful.

In conclusion,

Gender Pay Gap is a key metric (KPI) in HR data analysis. Calculating, measuring and tracking GPG is helpful to understand any underlying pay issues in your organization. But don’t forget to explore the staff distribution, hiring patterns and historical trends to fully understand your data.

For more on HR data analysis, check out below articles:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply