A while back I was working with a company and one of the issues that came up was how to track if managers had completed their monthly one to one meetings with their staff. They needed to see who had completed them and who hadn’t. The issue was that while they were tracking it, they had no easy way of being able to see up to date records of who had seen who and when and also to easily keep on top of it.
Step One – Start with the data
You need to start – as always – with the data. So I have begun with a file that just has a list of people, positions, departments and managers. You can view it here. Check out the sheet called List of New Employees. However we haven’t accounted for the actual dates for each scheduled meeting. A key concept in data is the idea of normalisation.
Now before you fall asleep and start drooling with excitement..(Yes, I know the adrenalin is now pumping…) the reason it’s important is that if your data isn’t normalised well, then you won’t be able to do anything with it beyond what you do at the beginning and I would be astonished if you are not asked to do something else with it e.g. like here – show who has had meetings and who hasn’t. The key way to remember it is that every entry needs to be entered as if it’s for the first time so for our list of employees we will need to end up with a situation where there are 12 lines for each employee – one for each month. Yes, you do need to put the name of the person’s manager 12 times – one for each row. Then you can summarize and slice ‘n’ dice using our trusty friend, the old reliable pivot table….
So what’s the next step? Have a look at Employee and dates in the same file and that’s where we need to start. I’ve just added in a column for months but I’ve entered a date (start of the month) for each row. I have entered an actual date because Excel doesn’t recognise a month entry as a date i.e. it would organise them alphabetically August/April etc..
Step Two – Prepare your data
The next step is then to use Power Query to re-organise it and normalise it or as Power Query calls it: “unpivot” so that we end up with a record for every staff member for every month i.e. getting our data normalised – that obscure data thing that means you can actually do something with your data. This used to be very tricky to do in older versions of Excel but with Power Query (AKA Get and Transform Data) it’s the work of seconds.
This is how you do it. I’ve just used the data in Employee and dates. See the video below.
Step Three – Updating the file
So far so good, but how does it actually work? In this file, I have made some entries that cover Yes, No. and because the data is only going to go until October, there will be some blank dates. You can view the data in the sheet called Cleaned up Data – Start. Remember you can add conditional formatting to highlight yeses and nos. Your data is now in good shape.
Step Four – Analyse the data – Did someone say pivot table????
Let’s analyse the data. Did someone mention a pivot table?? Yep, here it is. We are going to use a pivot table to answer the following questions.
Question One – Interviews completed
For each manager, how many interviews have been completed (Yes) and how many have not (No) and broken down by month. Of course, let’s have a chart with that as well. Here is the completed file
Here’s how you create the pivot table
Here’s how you create the chart
Question Two – Percentage Breakdown of Yes and No by employee
For each manager, what is the percentage breakdown of interviews done and not done overall ? Here is the completed file.
See in this video how both the pivot table and chart are created…
Keep it up to date – just Refresh…
I know when I show this sort of set up to people, there is a certain amount of sighing and “do I really have to do all this?”. Of course not, but what you need to remember is that *when* you have this done, all you need to do is add new records e.g. Yes or No in the appropriate column in your data right click on your pivot table and hit Refresh…and everything is updated…