How to Create Employee Performance Dashboard in Excel | 12 Mins Tutorial | Beginner to Pro, by Vedakarna on YouTube. 12 min 26 s.
- Video length
- 12 min 26 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Lay out the raw data on one sheet: Date, Month, Employee ID, Name, Department, Region, Target Tasks, Completed Tasks, Target Hours, Actual Hours, Productivity, Achievement, Quality Score. Select a cell and press Ctrl+T to make it a table.
Insert a PivotTable on a new sheet. Drag Department to Rows and Achievement to Values, then change the value setting to Average so you see average achievement per department.
Build a second PivotTable with Employee Name in Rows and Achievement in Values. Sort largest to smallest, then use Value Filters, Top 10 and set the number to 5.
Create a monthly trend PivotTable with Month in Rows and Completed Tasks in Values. Select it and go to Insert, then Line Chart to show tasks completed each month.
Add KPI cards for total employees and average quality score. Use a PivotTable or a formula that counts unique Employee IDs, and AVERAGE for the quality score, with a label above each number.
Insert a chart for performance by region: put Region in Rows and Productivity in Values, then choose Insert, Column Chart. Move it onto the dashboard sheet.
Click a PivotTable, choose PivotTable Analyze, Insert Slicer, and tick Region and Performance Status. Right-click each slicer, pick Report Connections and tick every PivotTable.
On a blank sheet, go to View and uncheck Gridlines. Add a title, paste the charts and KPI cards, align them, and use the same font and colours throughout.
Record a macro that runs Data, Refresh All. Insert a shape, type Refresh, right-click it and choose Assign Macro. Do the same for a Full Screen button with a macro that toggles Application.DisplayFullScreen.
Save the workbook as.xlsm so the buttons keep working. Test each slicer and the Refresh button after adding a new row of data.
Tips and common mistakes
Keep the source data in one table with no blank rows or merged cells, or PivotTables will skip records.
Use Average rather than Sum for Achievement, Productivity and Quality Score so the figures stay comparable between departments.
Hold Ctrl while clicking slicer buttons to select several regions or statuses at once.
If a chart ignores the slicer, open Report Connections and tick that PivotTable.
Questions
Can I build this dashboard in Google Sheets?
Google Sheets has pivot tables and slicers, so most of the layout works. The Refresh and Full Screen buttons need Apps Script instead of recorded macros.
Why do my charts not update when I change the data?
PivotTables only refresh when you click Refresh All or press Alt+F5. The Refresh button you build simply runs that command.
Do I need Power BI for this?
No. Everything uses built-in Excel PivotTables, charts and slicers, so a standard Excel 2016 or later copy is enough.
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.
