All articles with 'Excel for HR' Tag

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.

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.

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.

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.

Your mission is to find out answers to below questions.

• 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.

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
• Answering questions about employees
  • 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.

‘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?