0

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…

 

 

Continue Reading

41

Do you have to track who is up to date with their certification across a number of courses and departments ? This blog post should help.

Recently I was working in an organisation and one of the users – new to Excel – who had been on a course I had given, had been assigned the task of doing just this. She needed to track who had completed various certifications in the past two years and then to present those numbers analysed across departments and job grades.

She asked me to have a look at what she had done. Two things struck me. First of all, how much work she had put into this. Secondly, that she had set it up in such a way that she’d have to do the same amount of work every month. And I won’t even talk about the Everest of work she would have to face when she was asked to compile that work  for year end.

Shudder.

Essentially what she had to do was track the number of people who had current certification (done in the last two years) for a number of courses. This had to be calculated across departments and positions.

I took on the project and now she has an Excel file that (with the use of dropdown lists, vlookups, if functions and pivot tables) she can easily keep up to date. And if she is asked for annual numbers – no big deal. pie_chart_tracking frustrated_woman

So what I am going to take you through here is how to set up something similar. There will be a file at the end that is all set up for you – so feel free to adapt for your own organisation.

Step One – Data Preparation

  1. The first step was to list the names, positions, departments  and (current employment) status of all the staff members.
  2. I also thought it would be interesting to add the gender as well in case we ever needed to analyse the courses by gender.
  3. List all the courses they had to attend.
  4. List the departments they were all in
  5. List the various positions available
  6. List the various current employment statuses available.
  7. They were all done and then converted to tables (which will allow for updating/adding of courses etc)
  8. Then, using Data Validation, they were added as dropdown lists. The beauty of using the table approach is that when any of these need to be added to, that can be done so in the original table.
  9. They are all listed in the Lists sheets in the file.

You can download the file here …training_records_management_List_not_completed

You can see how the data was entered in the video below.

Step Two – Data Entry – Should feel like the first time – every time.

The natural approach of the inexperienced Excel user is to set up the data the way they think they want it to appear at the end. That means users often set up headings going across (instead of down) and try to get away with entering data once. Alas, that’s not how Excel works. They have seen a completed pivot table. That’s sort of like trying to form flour into a loaf without cooking it. And then doing it again…

What you must do is capture your data line by line.

Every line should be entered as if it has never been entered before. That means that in our Data Entry sheet we must enter every course as if it has never been entered before…yes, I know that seems like a lot of work and that we are entering stuff multiple times. And yes, we are. BUT the beauty of this is that – once you know how to use a pivot table – it’s easy to do analysis and – more importantly – to keep it up to date. Also if we use dropdown lists for it, it does make it much simpler. And of course vlookup functions to pull in the correct position and status.

You can see how it is done here.

Step Three – adding formulastracking

Yes, there’s more! We need to add two formulas to this Data Entry list.

  1. Convert the Data Entry list to a table (by clicking anywhere in the table, press Insert – Table). When you do this it means your formulas automatically copy down.
  2. How to track if our certification is current. “Current” in this case means in the last two years (720 days). Essentially this formula measures the difference between the course date and a cutoff date (that could be today() or a date of your choosing, checks if it’s less than 720. That means a “yes”. Otherwise, it means a No). It looks like this. This is the formula in the Certification Current? column.
  3. =IF(($M$1-[@[Date of course]])<=$K$1,”Yes”,”No”)
  4. However, I came across another problem which was that some people are very enthusiastic course goers i.e. they might have attended the same course more than once in the last two years, but they only should be counted ONCE. I struggled with this. But with the help of the Internet. God bless Google. God bless Experts Exchange. And in particular may the sun always shine on  Ejgil Hedegaard who came up with the humdinger of a formula in UniqueID heading.

You can view the completed entries in this file – training_records_management_List_data_entry

Step Four – adding your pivot tablesvictory

At this point, your data will be coming together nicely and once you have your data set up properly…your life around this will be so much easier…

OK, so you have slaved over your data, now your boss wants to know  how many different grades in each department have done the course…so for that we need our trusty pivot table.

  1. Click in your Data Entry list.
  2. Click on Design – Summarize with pivot table.
  3. Put it on a new worksheet. Set up your pivot table like so..

Pivot_table_training_records

 

 

 

 

 

 

 

 

 

 

 

Two final steps…

  1. Add a chart to that…click in pivot table – Options – Pivot Chart. I chose 100% Stacked Column – (third on top row from left) but you may choose a different one.
  2. And if you add new entries into your data entry sheet as people complete certification – all you have to do is hit Refresh (click in pivot table – Options – Refresh) or click in pivot table and press Alt and F5.

You can view the completed files here: training_records_management_List_completed

A Columbo moment – to finish off. columbo

There is a detective series from the 1970s starring Peter Falk and the final moments of the show were usually heralded by Columbo saying “there’s something bothering me….” and I had that moment with this data set. I found myself wondering how accurate this data is – because what about people who have not attended any courses – quite possible if someone has just started – and it made me realise that I needed to add another layer to it. I did assume that the list of names in the Lists sheet would be complete….

So if you have a look at the Percentage of all sheet – you will see there that I have added another table (linked to the pivot table) that shows the percentage of people completed as a percentage of the total for each position…

This may not be relevant for you but you can view how I did it in the video below…

You can view the completed files here: training_records_management_List_completed

So if you have been assigned this task this approach allows you to track records for a number of years, allows you to easily summarize the

Continue Reading