Calculate the Gender and Ethnicity Pay Gap in your organisation using my simple spreadsheet

Make a FREE copy of the gender pay gap spreadsheet here →

Intro to the Gender Pay Gap

The Gender Pay Gap is a pressing social issue, defined as the noticeable disparity in remuneration between male and female workers. This discrepancy is typically determined by comparing the average or median salaries of these groups.

It's a complex problem and people often mix up the terms "gender pay gap" and "equal pay," but they are not the same.

The gender pay gap refers to the disparity between the average earnings of men and women within an organisation, considering all job levels. Essentially, this means that if women are more likely to occupy lower-paid roles, the gender pay gap tends to be wider.

In contrast, equal pay is a legal mandate that ensures men and women receive the same compensation for doing the same job or work of comparable value.

Reporting requirements in the UK

In the UK, the requirement to report on the gender pay gap applies to private, voluntary, and public sector organisations that have 250 or more employees. That being said, there is a growing trend for companies that do not meet the 250 employee threshold to report their GPG to demonstrate their transparency on this topic.

The calculations required by the UK government are as follows:

• mean (average) gender pay gap in hourly pay
• median gender pay gap in hourly pay
• mean bonus gender pay gap
• median bonus gender pay gap
• proportion of males and females receiving a bonus payment
• proportion of males and females in each hourly pay quartile

You can view the list of calculations in more detail on here on the Gov.uk - Statutory guidance - Making your calculations.

You may also need to submit or publish:

• a written statement
• a supporting narrative
• an action plan

You can find more details on this here on Gov.uk - Statutory guidance Overview

Reporting dates

You are required to publish your organisation's gender pay gap report within one year of the relevant snapshot date. The snapshot dates are as follows:

• 5 April for businesses and charities
• 31 March for public sector organisations

The gender pay gap data must be submitted to the government using the following online service: Gov.uk - Report Gender Pay Gap Data. This data should be made publicly available on your organisation’s website.

How the calculator works

By inputting your organisation's salary details into the spreadsheet aka ‘calculator’, it will calculate all the outputs required by the UK Government. You can then use this information to identify and address any gender-based pay discrepancies.

Step 1 - Enter your employees data into the sheet called ‘Employees’.

Please refer to Gov.uk - Preparing your data for guidance on which employees to include in the report.

When modifying the spreadsheet, make sure to edit the blue cells only.

If you insert new rows, make sure to copy the formula down in the columns with the grey headers.

Step 2 - View your gender pay gap

Click on the sheet called ‘Dashboard’ to view the Mean (average) and Median (middle) ‘unadjusted’ pay gaps across your whole organisation.

You can view data for both annual salaries and hourly pay.

Mean

The mean difference is calculated by finding the average hourly pay. This is done by adding up all individual pay rates and dividing the total by the number of employees.

Median

The median difference is the gap in hourly pay between the male and female employees who fall in the middle of a pay scale when all employees are arranged from lowest to highest earnings. The median is considered the most accurate measure because it prevents extreme high or low salaries from distorting the overall results. For instance, if your CEO is a woman earning a high salary, this could significantly inflate the average pay for females.

Equality timer

This shows the number of days women (or men depending on your pay gap) work for free in a year. More reading on Women’s Pay Day here.

Step 3 - View the ratio of males and females in each pay quartile

Part of the reporting requirements is to determine the percentage of men and women in each hourly pay quartile.

In this sheet, the calculator divides the list of both annual and hourly wages from the employee sheet into four equal quartiles.

Interpreting the data

➕ Positive Gender Pay Gap

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

0% Gender Pay Gap

A zero percentage shows that there is equal pay between men and women in your organisation.

➖ Negative Gender Pay Gap

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

Understanding the ‘Unadjusted’ vs ‘Adjusted’ Pay Gap

This template only calculates the Unadjusted Pay Gap which has certain limitations. By understanding the limitations of the unadjusted pay gap and incorporating an adjusted analysis, organisations can:

• Develop targeted strategies to address underlying causes of pay disparities.
• Communicate more transparently about the factors influencing their gender pay gap.
• Ensure that measures taken to close the pay gap are meaningful and effective.

What is the ‘Unadjusted’ Pay Gap?

The unadjusted pay gap is the focus of current reporting requirements in the UK and what the calculator allows you to calculate and analyse. It provides valuable insights into the discrepancies in pay between men and women. However, it has several limitations:

1. Lack of Hierarchical Insight The unadjusted pay gap does not account for differences in job roles or seniority within a company. For example, if there are more women in lower-level roles and more men in senior roles, the pay gap might not accurately reflect the true extent of gender pay disparity.
2. Relevance to Small Organisations For smaller organisations, especially those with fewer than 30 employees, the unadjusted pay gap calculations might not be very informative or can be misleading due to the small sample size. The results can be significantly influenced by just a few atypical cases.

What is the 'Adjusted' Pay Gap?

An adjusted pay gap analysis offers a more detailed view by accounting for key factors that affect pay. This method controls for differences in job roles, seniority, education and experience—providing a more accurate picture of potential pay discrimination.

This deeper analysis helps organisations and policymakers create targeted strategies to address pay disparities and understand their root causes. It ensures that efforts to reduce the pay gap are meaningful and address underlying factors rather than surface-level symptoms.

Key areas to analyse in your gender pay gap data:

1. By Department: Assess if some departments have larger gaps than others.
2. By Location: Compare the pay gap across different geographical areas to identify regional differences.
3. By Role: Look at differences between managerial and non-managerial roles.
4. By Tenure: Compare the pay gap between new employees and those with longer service.
5. Shares/Equity: Consider how equity and share options might affect the overall remuneration and skew results.

Ethnicity pay gap

The calculator, using a similar logic to that used in calculating the gender pay gap, can also determine the ethnicity pay gap in your organisation.

If you want to change the ethnicity types in your calculator, watch this video here.

Make a FREE copy of the gender pay gap spreadsheet here →

FAQs & Disclaimer

There are three ways that I can help you