Search

# All articles with 'Excel for HR' Tag

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

Published on Mar 19, 2024 in Learn Excel, Templates

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.

Continue »

## 9 Box grid for talent mapping – HR for Excel – Template & Explanation

Published on Nov 17, 2020 in Charts and Graphs, Learn Excel

9 box grid is a popular method for talent mapping. Download this free excel template for tracking and visualizing your employee performance & potential data.

In this article, I explain about powerful excel features like FILTER(), SORT() and CONCATENATEX() to create a similar 9 box talent map grid for your data.

Continue »

## Leave entitlement vs. usage analysis with Power Query

Published on Sep 24, 2018 in Excel Challenges, Power Query

Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.

This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.

Continue »

## How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Published on Sep 21, 2018 in Excel Challenges

Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

• How many employees used exactly 100% of their entitled sick leave?
• How many employees did not take any sick leaves?
• Listing of all employees who used 100% of their entitlement

Use either Power Query, Excel formulas or any other technique to answer the questions.

Continue »

## Top 5 HR Analytics Examples – Free Video Masterclass

Published on Jul 27, 2018 in Learn Excel, Master Class, Power Query

I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.

Specifically, we will cover:

• Gathering and structuring Employee data in Excel
• How to use Power Query to collect data
• Polish / clean data in Power Query
• Bring cleaner data to Excel as refreshable table
• Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
• Pivot tables for data analysis
• Understanding the results quickly with conditional formatting
• Understanding pay gap
• Calculating gender pay gap
• Visualize pay gap
• Creating salary distribution charts
• Working with histogram charts in Excel 2016 / Office 365
• Making interactive charts
• Generating letters thru mail merge
• Calculating employee bonus based on bonus mapping logic
• Creating 100s of letters with a single click using Mail Merge + Word

Sounds interesting? Read on for details.

Continue »

## How much long service bonus to pay? [Homework]

Published on Dec 8, 2017 in Formula Challenges

‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).

How would you calculate long service bonus?

Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.

You need to calculate how many days of continuous service Julia had (ie total service – duration on leave without pay). How would you write the formula?

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.