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.