Create Stunning Dynamic HR DASHBOARD in Excel - Part 2 Data and Formulas

Here I outline the first steps to creating an Excel dashboard: Setting up your tabs, colours, and most importantly sorting data and creating category formulas.

It's a long-ish video but this stuff takes time to do right and be assured you can learn dashboards within a day. I take you through each step slowly and methodically, with lots of Excel tips along the way. I cover:

- Setting up your worksheets and colour scheme
- Formatting your data into a table
- Adding extensive formulas and categories to your data
- Setting the print area and layout nicely

See the chapters for more details on what you'll learn.

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.

This is video 2 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. Stay tuned for the next in this mini-series, where we'll sort out the pivot tables as the next step in the workflow.

Download the follow-along dataset should you wish to create this exact thing from my blog (will be there soon!). But using the concepts and learning, you'll be able to create your own dashboard from ANY dataset!
____________________________

Chapters

0:00 - Intro and overview of steps
2:10 - Reset Office colour scheme
3:00 - Set out sheets and rename
5:20 - Bespoke colour scheme & sheet colours
11:25 - Intro & overview of data
14:20 - Format base dataset
16:20 - Formulas intro
17:50 - Formula Personal Sickness Rate
18:30 - Formula Bradford Factor
19:05 - Formula IF categories BF
21:00 - Formula VLOOKUP overview for categories
22:35 - Formula Total Salary & percentage %
23:20 - Formula DATEDIF for service length
24:15 - Formula IF categories service
25:20 - Formula TEXT for month name
26:15 - Formula YEARFRAC for age
27:30 - Formula IF categories age groups
28:00 - Formula Calculate Full Time Equivalent
28:37 - Formula IF full or part time
29:12 - Formula & to string text together
31:00 - Auto populate formulas to data
32:10 - Format columns of data
32:58 - Add filters to data
33:36 - Format data as table
37:10 - Page layout, print area & print titles
40:50 - Hide 'marching ants' page breaks
42:00 - Recap of learning & extra pointers
47:45 - 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