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 HR analytics examples, 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
Top 5 HR Analytics Examples – Video lesson [60 mins]
Please watch the below video to learn all these topics (or watch this on Chandoo.org YouTube Channel)
Download Sample Files and learn on your own
The sample files for this video lesson are beautifully crafted and explain everything. Download the files for Excel for HR Analysts.
The download file also contains resources to learn more about various topics discussed in the video. So make sure you refer to the download.
Work in HR? Tell me about the challenges you face…
HR is another area where Excel is extensively used. Learning a bit more about most popular features of Excel can help you save a ton of time and be a hero. If you work in HR, do tell me the challenges and interesting scenarios you face in the comments section. I would love to learn from your experiences.
23 Responses to “Top 5 HR Analytics Examples – Free Video Masterclass”
Hi Chandoo,
will this webinar be available afterwards? I'm living in Belgium and fridaynight at 6 I'm nowhere near a pc...
thx
Hugo
Hi short notice for 1sr Webinar, also can you offer 2 or 3 nights for all the folk around the world to make U more awesome lecturer. thanks eric
I have the same question as Hugo about availability of the webinar as a replay. I will be at the airport and not able to participate in the live class.
Thank you!
Hi, I will not be available, can I download later please?
@Eric, Hugo, Tanya and all: Yes, a recorded version of this webinar along with example file and other resource links will be posted once it is over. Please watch the blog for more info.
GREAT STUFF thanks will like to see what U offer AWESOME
Hi,
How can i join your blog ??
Thank you,
Hi Chandoo, as per Eric's comments, more advanced notice and more time/date options would be great as we need some more suitable ones for Perth, Australia... not 1am please!
Hi Chandoo! Finally someone looking at HR data , not just sales / finance! You've mentioned doing more than just this one webinar. Would love to see what you can do with for example proposed pay increases linked with performance reviews ratings on differing pay grades, allowances with various qualifications / certification requirements combined with specific location data, date mismatches in different systems - calculation of under / over payments then comparing to actual system calculations etc.... I hope this helps with you doing more awesome stuff.
Exactly what i said in the last live webinar. Pls dedicate sometime to teaching HR Analytics. Turnover, Recruitment, FTE Calculations, Budget control.... those kinda things. Thanks, a lot ma'am. I bless the day I found you on YouTube Chandoo
Hi. Definitely attending since I created a payroll system in excel 2010.
One question though, On this page it's scheduled for 1:00 PM, Thursday 26th of July, 2018 (US Eastern Day Time) but when I go to the youTube link it shows an option for a reminder for July 26, 2:00 PM. Just wanted to bring this to your attention.
Can you check if your computer / youtube user account is mapped to USDT? I think the reminder is controlled by Google / YT folks. In anycase, it is at 1PM on 26th July.
Hi Chandoo, Fantastic files provided. Would it be possible for some help? I need to report on leave taken for employees. In particular on which day of the week in particular leave is mostly taken on. As a guess Friday then Monday. But evidence is needed! As you can imagine the leave data can be recorded as 1 day - thus easy to see what day this is. But when it is more than a day recorded in a block, - eg week, several weeks - am stuck on doing this. To add to this leave can be recorded Monday to Friday for week one and then the same for week two. Actually it could be split in two applications during the week - one ending Tuesday with the next one starting the next day Wednesday. Is grouping somehow an option?. Technically correct but it needs to be shown as continuous. Any suggestions as to how to tackle this. If you want me to send you a file example let me know....
Thank you Sylvain... very interesting problem. Can you email me a sample file? I will write a blog post explaining few techniques. Also check out this -
Which employees are on leave during Easter?
Great webinar & file. I resisted all onslaught of sleep during the webinar as usually I am fast asleep at such night time when it was beamed in India, just to watch you perform live & found it worth the resistance.
However, in the midst of such fight & with my other eye on the webinar, I could not get to request you to explain the M language code behind the PQ commands that you used. In your next webinar (hopefully at a more favourable time for me), please deal with the M language code behind the PQ commands that you used.
Hi, I am a little concerned of all the "double" emails WRT tio the webinar and the comments. Please adjust setting to receive comments ... thanks
Is there a non-youtube alternative?
Blocked at work, despite this being something that could be VERY helpful!
@Thomas... Welcome to Chandoo.org and thanks for your comments.
Sorry, but no. May be you could watch this on mobile phone / tablet app or from home (TV / PC)?
Thank you for providing useful article, love to see new-one.. keep posting
I can't see histogrm, although I use Excel 2013.
This only works in Excel 2016 or Office 365. It is one of those new charts. For older versions, create a histogram by:
1. enable analysis toolpak (from Developer ribbon > addins)
2. Go to Data > Data Analysis
3. Use Histogram option
All the best.
Chandoo, thanks for the great webinar and file. Do you have an example of a survival analysis in the power pivot that would show the likelihood of working up to a certain length of time in a company?
Hi, I just watched this for the first time and I'd like to try calculating and visualizing the gender pay gap for my organization. I am not able to watch the video, are there any instructions for how to do this online?
Thank you!