Create Excel Dynamic HR DASHBOARD - Part 3 PIVOT TABLES

In PART 3 of how to create a dashboard in Excel, I demonstrate how you create all the PIVOT TABLES which will feed your charts and slicers, which we'll cover later. Pivot tables are your first main step to automate your interactive analysis of the data. Excel pivot tables are such a time saver, many people stop here in terms of data summaries.

This is an essential step to get right, so I guide you through slowly and clearly. I address the common issues you'll encounter with pivots, help you format things nicely, sort the order of data and categories, do 'Top 5' pivot lists, sort out the pivot table settings, choose correct fields and number formats, and organise everything ready for the next step.

It's a long-ish video but this stuff takes time to do right and be assured you can learn dashboards within a day. You can check out the chapters to skip to particular content of interest. Note that there are a few pivots which I leave you to create once I've shown the process many times, just to save time on the video and allow you to practice, so just remember to pause!

For this Excel dashboard, I've used an HR dataset from the fictional company 'Dunder Mifflin', which you may know from 'The Office' TV show. I think it's good to use familiar and fun data while learning, rather than the generic or bland 'dummy data' used in such tutorials. I find it more engaging and the concepts seem to flow easier.

Download the follow-along dataset should you wish to learn by creating this exact thing from my blog below. But using the concepts and learning, you'll be able to create your own dashboard from ANY dataset!

https://excelattheoffice.com/2024/04/...

This is video 3 in my mini-series all about how to make stunning and dynamic dashboards of charts and graphs in Excel. Video 1 gave you an introduction and demo of the HR dashboard I've created. In Video 2 I showed you how to set up the worksheets and refine the data with formatting and calculations. Stay tuned for the next in this mini-series, where we'll get into the front end dashboard itself!

____________________________

Chapters

0:00 - Intro and outline
1:40 - Insert first pivot table
3:15 - Sort pivot table options
4:10 - Pivot fields and areas explained
6:45 - FTE and headcount value settings
8:35 - Rename headings, format table, & title
10:20 - Insert sideways heading
11:35 - Warning about data & file size
12:40 - Copy & paste pivot table
12:55 - 4x Pivots for employee overview categories
17:00 - Pivot tables good practice
18:50 - 4x Pivots for salary, average, number formats
26:40 - 3x Pivots for demographic categories
29:05 - 3x Pivots for sickness, top 5 filters, custom sort
35:05 - 3x Pivots for service & birthdays, top 5, sort
37:35 - 3x Pivots for CPD & top 5 sales reps filter
40:50 - 2x Pivots for complaints (DIY)
41:55 - Give Pivot Table clear name
43:35 - Outro & close

_________________

See my other videos or blog with more excel tips:

https://excelattheoffice.com/blog

I hope you've found this helpful. Please like and subscribe for more handy content. I do this around a day job at at own expense (the main one being £300 per year website fees), so any contributions are most welcome - you can buy me a coffee below, or get in touch if you want training of for me to help make sense of your data with bespoke dashboards:

https://excelattheoffice.com/buy-coffee

Kind Regards,
Adrian