Close this search box.

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.


Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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



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

    • Tayo says:

      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.

    • 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


    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.

  14. Den says:

    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?

  15. Chris says:

    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!

Leave a Reply