📊 Fully Automatic Payroll Management System in Excel | HR Payroll & Employee Analytics Dashboard, by Vizspire on YouTube. 27 min 35 s.
- Video length
- 27 min 35 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Put your raw employee records on one sheet, one row per employee. Include name, department, manager, location, join date, gross salary, net pay, attendance days and training hours.
Add calculated columns beside the data: tenure in years, joining month, payroll cost and attendance rate. Use formulas so every new row fills itself in.
Select the data range and press Ctrl+T to make it an Excel Table. PivotTables built on the table then pick up new rows when you refresh.
Insert a PivotTable for each view you need: payroll by month, by department, by manager, headcount by location, top earners and attrition count.
Set up a blank sheet for the dashboard. Turn off gridlines, widen columns and set row heights so the cards and charts line up in a neat grid.
Build five KPI cards across the top for total payroll cost, total net pay, headcount, attendance rate and training hours. Link each card to a PivotTable cell and apply custom number formatting.
Add PivotCharts: a line chart for the monthly payroll trend, a column chart by department, a bar chart of top earners and a doughnut chart for headcount by location.
Apply the Top 10 value filter to the salary PivotTable and sort largest to smallest, so the earner chart shows only the highest-paid employees.
Insert slicers for Join Month and Department. Right-click each slicer, open Report Connections and tick every PivotTable so all charts filter together.
Press Ctrl+Alt+F5 to refresh everything after a data change, then switch to full screen view for a clean presentation layout.
Tips and common mistakes
Keep the raw data sheet separate from the dashboard so your pivots and formulas never overwrite employee records.
If a chart ignores a slicer, open Report Connections and tick the PivotTable behind that chart.
PivotTables do not update by themselves. Refresh after every edit or the KPI cards will show old numbers.
Test the layout with real data before adding more charts. A crowded sheet is harder to read than five clean ones.
Questions
Do I need any add-ins or paid tools?
No. The whole dashboard uses built-in Excel features: Tables, PivotTables, PivotCharts, slicers and custom number formats. Excel 2016 or later handles all of it.
Will it still work when I add new employees?
Yes, as long as you paste the new rows inside the Excel Table and refresh. The PivotTables and charts then expand to include them.
Why are my KPI cards blank or wrong?
The formulas usually point at a PivotTable that has not been refreshed, or at a cell that shifted. Refresh with Ctrl+Alt+F5 and recheck the cell references.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
