0

September can be described as the new January and it’s often a time when people decide to start something new or get stuck into something they have been thinking about learning.  Dare I suggest that could be your Excel skills ?In this blog post I am offering 5 ideas around that..

Idea 01 – Know your tendency.
One of the writers I enjoy following most is Gretchen Rubin and she has developed a framework for expectations which she calls the Four Tendencies. If you are curious you can get the overview here  and if you wish, you can take the quiz here... How is this relevant to developing an Excel learning plan? (In the interests of openness, I am an Upholder). It can help you understand what you need to help YOU learn and why what works for someone else doesn’t work for you.
By the way, one type is not “better” than another type. Each has its own strengths and challenges and what’s important is to leverage this.  Gretchen has an excellent post here about habit change strategies for different tendencies because the most effective way to learn anything is to make it a habit…

  1. Well if you are an Obliger (which is one of the most common tendencies), it means you can meet outer obligations but struggle with honouring your inner expectations) you will struggle with following a plan by yourself but joining a group/creating some sort of external accountability will work very well e.g. set up an Excel learning group – and with people who will hold you accountable – your mother doesn’t count, unless of course she is also an Excel learner….
  2. If you are a Rebel (which means you resist outer expectations and inner expectations!)  this means that even if you are interested in learning Excel, you will not do so if someone else or even yourself (!) tells you to do it. In that case you are better off approaching it as something you are *choosing* to do, that being good at Excel is part of your identity, e.g. that developing computer skills gives you freedom.
  3. If you are a Questioner, (you will meet an inner or outer obligation if you can see a good reason to do so – you will tend to do alot of research) and  you will not learn Excel skills unless you see a good reason to do so. Efficiency is important to you. So if you do want to feel motivated find a reason that works for you e.g. it’s going to help me become more efficient, waste less time and keep that reason in front of you.
  4. If you are an Upholder, (you can meet inner and outer expectations) you will have decided to improve your Excel skills and in that case the best thing to do is to draw up a schedule of what you want to learn and tick it off as you go along. I LOVE ticking things off. Sad to say, there are times when I will learn something, just so I can tick it off…

Idea 02 – Set yourself up for success :
If you are working full-time and have a family, you are probably not going to study for 3 hours a night. Instead look at the reality of your situation and identify pockets of time e.g. on the way to work or from work, early morning, evening where you could spend 15 minutes learning something about Excel. The following is a list of things you could learn in 15 minutes. If 15 minutes is too long, identify something you can do in 5 minutes. Identify something, set a timer on your phone and do it.

  1. Learn 3 keyboard shortcuts. Here is one on Ctrl keyboard shortcuts and this is one from DataPig Technologies which combines Excel and the periodic table – what is not to love? Yes, I know…sad, sad, sad..
  2. Learn how to create basic formulas
  3. Find a YouTube video on a topic – just search for the topic you want to brush up on
  4. Learn how to add up numbers using Autosum.
  5. Learn how to sort and filter.

Idea 03 – Understand how you learn best:
Do you learn best by solving problems, by doing an online course, by asking someone else to show you, by figuring it out yourself on Google (God Bless Google :-)). Note that the strategy may very depending on what you want to learn. When I am improving my technical computer skills, I would invariably choose an online course. I am a big fan of Mynda Treacy.   However, when I started learning music, I worked with a tutor simply because I knew so little about it that I didn’t know where to start. I am working on my French so I use a tutor and I also use an app for daily practice (Duolingo or Memrise are both excellent).

Idea 04 – Identify a retention strategy: If you are going to be working with Excel on a daily basis, that in itself will be a retention strategy. However if you only work with it from time to time…these ideas may help. A comment I frequently hear in class is ” this is great, but I don’t know how to retain it”.

Please feel free to add your own ideas in the comments.

  1. Add a comment in the Excel file (click on a cell, right click and Insert Comment – it’s the Excel equivalent of adding a Post it note to the cell) which outlines what your thinking was or what you did.
  2. Record what you did: Windows 10 comes with a free screen recording tool. Here is a link to a tutorial on it. I use Camtasia (which is a paid product) but here is a list of screen recording options. Store those videos together in a folder called Excel Learning and name them as clearly as you can so that you can find them again. One idea would be to create a hyperlink in your file to this video. Here is how you create a hyperlink
  3. Quickly write out a summary of what you did. Just doing this helps retention. Hand write it or type it up in Word and save it with the same name as the file you are using it with.
  4. Describe to someone else what you did – you know when you don’t want to forget something and you tell someone else to remind you – how that automatically helps you remember – that’s what you are leveraging here.

Idea 05 – Leverage the Pareto principle – 80/20 

 Often people say that they don’t know what they don’t know but as someone who has been teaching Excel a long time, here is a list of the topics you *do* need to know.  

Needless to say, it is in an Excel file :-)

If you know these topics, they will cover 80% of what you need in Excel. On the other hand, you may already know from work what you need to know – ask your boss or colleagues about this and you can devise a plan from this.

Let me know what has worked for you – and also maybe what hasn’t worked for you – we can all learn from it

 

Continue Reading

0
http://memes.com/img/1112650

http://memes.com/img/1112650

In a class recently the subject of age calculation came up, which reminds me of when my son was young and would ask me how old I was. “19”, I would say succinctly. He was at the age when he was learning basic maths. “So what would make you 12 when you had me..” “Yes, what can I say..I was a child bride”…

Of course as he got older that response met with a snort of derision and a muttered comment about what life in the Dark Ages before computers and the Internet…

However, back to the class. I offered two solutions to this dilemma. In both cases, because the formula uses today() – which uses…yes, you guessed it, today’s date…the age will always be current.

One of them used the Int() function (which is to just show the whole number part of an answer – assuming that you want your answer as 12 as opposed to 12 and a half.

In the second approach we used an undocumented Excel function (you won’t find it in Excel functions) called DateDif to show the difference. Both are illustrated here.

I’m in a hurry…I just want to see the file

In that case, you can download the completed file here.

I’d like to see how it’s done

Approach 1 – Using the Integer function.

In this video I show you how to use the Today() function with the Int() function to calculate the current age.

Approach 2 – Using the lesser known DateDif() function

This is an undocumented function in Excel and has some issues but it could be an option. You can read more about this function here.

DATEDIF(start_date,end_date,unit)

Start_date = Date of Birth

End_date= Today()

Unit = “Y”

We are going to work through the same set of dates and we will end up with the same set of answers.

The video shows you how it’s done.

 

 

Continue Reading

0

A common issue for many organisations is tracking time-in-lieu i.e. someone has a certain number of days/hours holidays(vacations) allocated but they can increase that by doing overtime and then taking time off – “in-lieu”.  While some organisations have a system in place, a lot don’t.

This tutorial will show you the following:

  • How to set up a sheet for each employee that automatically tracks and summarizes their time in lieu (all they have to do is enter the date, number of hours overtime and time taken off).
  • This sheet will also calculate a balance so they can see how many hours holiday time they still have outstanding.
  • How to set up a sheet that summarizes all those hours for the manager so that he/she can get the big picture at any time.

The tutorial below takes you through it step by step. If you want to view the file straightaway you can download the file with dummy data (so you can see how it works) or without dummy data when you are ready to experiment with your own data.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

Scenario

  • Wylie Coyote is a manager. She has 5 staff .
  • Each member of staff has an allocation of total holiday hours (cell I1 in the file below)
  • Each day is taken to be 8 hours
  • Extra hours can be done per day – they are recorded as Overtime. Every time an employee does overtime, the date will be entered in the Date column (Column B), and the number of hours entered into the Overtime column (Column C)
  • When the employee takes time off – this is entered in the Time in lieu/holidays column (Column D).
  • The balance is calculated automatically – based on the total number of hours for holidays, plus overtime minus Time in lieu/holidays.
  • The month of that date is entered automatically in Column A – this will be used later for summarizing hours. This is done via a formula

Step 1 – Set up the Employee sheet

View the video here to see how this is done. Some sample data has been entered. Thanks to RGonzo1971 of Experts’ Exchange for help with the dynamic formula.

You can download the file at this stage.

Step 2 – Summarizing the data month by month.

So right now our sheet shows the employee how many hours they have outstanding at any point in time (assuming they have entered all their hours up to date)

However, Wiley Coyote wants to see two things – she wants to see a monthly summary for each employee on their sheet and she also wants to have a picture overall of how much overtime and time in lieu has been taken.

So in the same sheet we set up the formulas to summarize on a monthly basis how much overtime and time-in-lieu each person has taken.

See video below to see how this is done. I have used the Sumifs() function and you can view the tutorial on it here.

  1. Fill in the months of the year from K6 to K17 down
  2. In cell L5, we will use a SUMIFS() function to calculate the amount of overtime per month
  3. In cell M5, we will use a SUMIFS() function to calculate the amount of holidays/time-in-lieu each person has taken.

You can download the file with the summary completed at this stage.

Step 3 – Creating one for all our employees

We now have our first sheet set up – next thing we have to do is make a copy of this for every other employee.

See a video here that shows you how to make a copy of the sheet and rename them accordingly.

You can now download a file where it has been set up for 5 employees.

Step 4 – Creating a bird’s eye view sheet 

The last step is to create a sheet that allows the manager to see how much overtime is taken by each employee by month. This is to help her to do the following:

Make sure that employees are taking their time-in-lieu within the month as far as possible and to make sure holidays are taken within the year.

The summary sheet will summarize by employee and month what their current balance is.

View both completed files here. Again, if you have any questions, just get back to me.

Download completed file with dummy data

Download completed file without dummy data (but with formulas)

 

Continue Reading