The Excel Expert

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

This year I attended a conference about John O Donohue , an Irish philosopher/mystic/poet who died in his early 50s. At that conference a friend of his called Dr Dan Siegel talked about how quantum physics and mysticism were now converging and  discovering what they had in common.

In his talk he referred to “the plane of all possibilities” – a mathematical location where all the solutions/answers/possible outcomes are to be found. (I am not a quantum physicist so if there is a more accurate term, happy to learn more).

At this point, you are probably wondering, “where are you going with this Anne and what does it have to do with Excel?” Valid questions indeed. I am telling you about this because an observation that frequently comes up in my classes is well “I don’t know what I don’t know” and “I don’t even know where to look for an answer”.

When it comes to Excel, the reality is that there is a solution somewhere. It may not be obvious, it may require work but someone has solved it somewhere but if you do not even know what to look for, that isn’t really of much help so I am going to suggest something rather unorthodox.

I am going to suggest that you tap into the “plane of all possibilities” by trying the following.

• If you have an Excel problem and you have no idea how to solve it, seek first of all to get calm and settled (even if it requires retreating to a  toilet cubicle for 5 or 10 minutes!)
• Close your eyes and ask that the “plane of all possibilities” shows you a solution to the Excel problem you are having.
• Within an hour you will get a prompt to look somewhere/ask someone – go with that no matter how strange or weird it seems. That may not have the direct answer for you but it will lead you to the answer…
• I use this myself and the prompts can come in many ways e.g. search for X or ask Y or try Z…

I also know that for some of you this may be veering into what a good friend of mine Maggy Whitehouse calls “wanky bollocks” but honestly, surely it’s at least worth a try 🙂

Continue Reading

Note: Since I wrote this I attended the Excel MVP Conference in Amsterdam – and the techniques I learned there made me realise I could have done this more effectively..

You can view Part one here (Steps one to three)

Just a reminder about what deferred income is…

You have paid the company (for example) 365 of your currency for a year’s subscription at the beginning of January 2018. The company hasn’t “earned” that because the service hasn’t been delivered so their revenue is only “earned” at specific dates.

From a sales point of view, at the end of January, the amount of earned income will be 365 X 31/365 = 31 . The amount of deferred (i.e. paid for but not yet “earned”) income will be 334. At the end of February, the amount of “earned income” will be (28+31)/365 X 365 = 59. The amount of deferred income is now 365 – 59 = 306. So on for each month.

Step 4 – Calculate “totally deferred” income

This is income that is spread over the age of the subscription e.g. for a one year subscription that would be 100/365

Here is the start file and finished file

Step 5 – Calculate not deferred income

That means the income is recorded as “earned” on the first date of the subscription.

Here is the start file and finished file

Step 6 – Calculate partially deferred income.

This will depend on the percentage e.g. if it’s 20% deferred that means that 80% is recorded as “earned” on the day of invoicing. The balance i.e. 20% of the sale will be divided by 365.

Here is the start file and finished file

Step 7 – Get it all together with a pivot table

Put all these bits together.

Here is the start file and finished file

Step 8 – Throw in a date to defer and earn…

For this one just wanted to say thanks to Mynda Treacy of www.myonlinetraininghub.com and her excellent Power Query course who introduced me to this method.

Here is the Start file and finished File

I hope you found this useful …and as always comments and questions welcome.

Continue Reading