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.
5 Responses to “Preparing Profit / Loss Pivot Reports [Part 2 of 6]”
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
I am not getting sound from the videos. I have checked all the settings and spent several hours searching the Internet to no avail.
Has anyone else had this problem?
Is there anyway to get the Grand Total to be broken out in the same fashion as the items above it? For instance, if you have in column 1, widget a, widget b, and have their sales by month in column 2, I'd like to see the grand total also be by month, for widget a & b combined.
I can't get anything other than a single line for the grand total, rather than the same format as the data above.
Widget A Month Sales
Jan 100
Feb 200
Widget B
Jan 150
Feb 250
Grand total - here I would also like to have Jan, Feb.
Jan 250
Feb 450