Excel: Visualising pay differences

As I’ve mentioned more than once previously, I’m a union representative. Among my many duties in this role is wage negotiations. While we negotiate collectively, I also make sure to dive into the numbers to look for the unexplained outliers, and to have them addressed.

One of the tools I use to do this is scatter plot diagrams with trendlines. I want the diagram to show annual wage on the Y-axis, age on the X-axis. In order to identify gender differences, I also want to have the individual plots colored differently for men and women.

I start out by copying the raw data I get from management to a new sheet. I convert birth years to approximate age by subtracting birth year from the current year using =SUM(YEAR-BIRTHYEAR). I then sort by gender, which gives me a table that looks something like this:

I delete the header row, then select the columns for Age and Annual pay for the women, go to Insert > Scatter Chart. Then I click Select Data in the Chart Design tab. I open Series1 and rename it “Women”, then add a second series. I name it “Men” and select the relevant Age and Annual pay cells in the field for X and Y value, respectively:

This gives me this scatter chart:

Useful, though not as useful as it might be. Let’s start out by clipping the axes. I select and right-click the Y-axis, and select “Format Axis”. I set the bounds to 20 and 75, respectively, and set the major unit to 5:

For the X axis, I change the minimum bounds to 350’000, while leaving the remaining settings automatically set. I add – and set – chart and axis titles, and a legend. This gives us this chart:

Employees often see their pay increases taper as they grow older, and the above chart appears to support this. In order to readily identify those employees whose pay is below the mean, I also add trend lines. In the Chart Design tab, I click Add Chart Element, and add linear trend lines for both men and women, giving us our final chart: