HR dashboard with lists of employees

Willem2904

New Member
Joined
Apr 4, 2018
Messages
6
Hi,

I am by no means an expert in MS Excel, but most of the times I can find the solutions myself. Not this time...

I want to track all the changes in our headcount:* All the dates employees were hired, which position they were hired for, and which team they belonged to* All the dates employees were promoted, including the new positions (and the team)* All the dates employees resigned / left the companyFor example: Employee 1 was hired as accountant on 01.01.2016, in the accounting teamEmployee 2 was hired as purchaser on 03.04.2016, in the operations teamEmployee 1 was promoted to senior accountant on 01.07.2016, in the accounting teamEmployee 1 was promoted to Accounting manager on 01.12.2016, in the accounting teamEmployee 2 was promoted to purchasing manager on 31.12.2016, in the operations teamEmployee 1 left the company on 31.12.2018Employee 2 left the company on 31.12.2018......

Based on the data above, I want to create:* a dashboard (with a date filter) with charts showing the headcount over time (monthly/quarterly charts) , the headcount per position over time (monthly/quarterly charts), the headcount per team over time (monthly/quarterly charts), * a "single employee" report, showing all the details about a specific employee (Date hired, promotions (including dates and positions), date left)* a "position" report, showing a list of all the employees that were in a specific position on a given date.* a "New hire" report, showing a list of all the employees that were hired in a specific time period, together with the position they were hired for.* a "Promotion" report, showing a list of all the employees that were promoted in a specific time period (together with the dates and their old & new positions)* a "leavers" report, showing a list of all the employees that left the company in a specific time period (together with the date and their position when they left the company)

How would you organize the data? Have 1 table with all "transactions" (hires, promotions, leavers,...) or split it up in several tables?
How can I aggregate all the data so that I can easily build charts showing the headcount per month etc?

Looking forward to hearing from you!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,
Maybe you could arrange your data in something along these lines:
Extraction could be done with either secondary tables with formulae, or chart or via an analytical tool of your choosing.

Book1
ABCDEF
2nameemployee#datetypedptposition
3employee#1101-01-16hiredaccountingaccountant
4employee#2203-04-16hiredoperationpurchaser
5employee#1101-07-16promotedaccountingsenior accountant
6employee#1101-12-16promotedaccountingmanager
7employee#2231-12-16promotedoperationmanager
8employee#1131-12-18resigned
9employee#2231-12-18resigned
★ Willem2904
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top