Top 5 HR Analytics Examples – Free Video Masterclass


Top 5 HR Analytics Examples in ExcelI 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.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

20 Responses to “Top 5 HR Analytics Examples – Free Video Masterclass”

  1. Hugo says:

    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

    • eric says:

      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

  2. Tanya says:

    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!

  3. eric says:

    Hi, I will not be available, can I download later please?

  4. Chandoo says:

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

  5. Nicola says:

    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!

  6. Sylvain says:

    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.

  7. DERICK PITCHER says:

    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.

    • Chandoo says:

      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.

  8. Sylvain says:

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

  9. Sandeep Kothari says:

    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.

  10. eric says:

    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

  11. THomas says:

    Is there a non-youtube alternative?

    Blocked at work, despite this being something that could be VERY helpful!

    • Chandoo says:

      @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)?

  12. Tushar Patel says:

    Thank you for providing useful article, love to see new-one.. keep posting

  13. RAJESH KUMAR SINGH says:

    I can't see histogrm, although I use Excel 2013.

    • Chandoo says:

      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.

Leave a Reply


« »