With Microsoft Excel so important for so many jobs, how can you be sure you have the required excel skills for your new job? What are employers really looking for and if needed , how long will it take to learn them.
First of all, it’s important to understand that an experienced Excel user can gauge very quickly the depth of your Excel skills.
Even the simple question below can be enough to tell them so much about your fundamental Excel Skills. Let’s see how.
You are required to create a simple formula from cell C5 to C9 which will work out the commission due to each salesperson. The commission will be 5% of their Sales as shown in cell C1.
Try it yourself now and then we will analyze the various possible answers to this question.
Answer One: =c5*100/20 which has been copied down.
It took the person 5 minutes to complete.
Mark awarded: 45%
Why such a low mark? Well on the positive side, this answer shows that the person can build a basic formula and knows the basic Excel formula operators. But the examiner noted that the person did the formula for each person instead of copying it down.
Answer 2: =C5* 5%
Take taken: 1 minute.
Mark awarded: 55%
This answer shows the same as above but also knowledge of percentage formatting and the person copied the formula down by double clicking on the file handle.
Answer 3: =C5* $C$1
Take taken: 40 seconds
Analysis: As above but this student shows he understands and uses the three essential Excel concepts, namely
- Using Cell references
- Relative references
- Absolute references.
Answer 4: =C3*C$1
Time : 40 seconds
Analysis: As above but this student also shows that they know about Partial references.
Remember , you cannot bluff about your Excel Skills so be honest.
If certain Excel skills are vital for the job you are going for and you don’t have these Excel skills, then book into an Excel public or online course beforehand and tell your employers. This shows initiative, honesty and ambition.
Before we look at what is meant by various Excel skills descriptions – basic , intermediate and advanced Excel , lets test your current Skills levels first.
Test your current Excel Skills:
The Excel skills reporter is a free Excel Workbook that requires you to complete 7 specific Excel tasks on a real excel workbook. It times your work , then auto-correct your efforts and produces a personalized report of your existing skill levels and tell you what level you are at. You can download a free copy of Excel Training skills workbook by clicking here.
Jobs requiring Basic Excel skills:
You will need to know the basic Excel Functions like – SUM, AVERAGE, MIN and Max, be able to build simple Formulas and be competent in formatting especially currency formatting.
Knowing how to access cell references in different worksheets and workbooks is a vital basic skill. Be able to print workbooks effectively and be able to do create basic charts.
In terms of data analysis, essential skills include sorting and filtering.
Finally to give yourself the edge over other candidates, learn about Relative and Absolute references ($ sign). These are two of the most important concepts in Excel .
Jobs requiring Intermediate Excel Skills:
You will need to have and show a real understanding of Relative and Absolute References and using cell references.
In terms of Excel Functions , knowledge of the following will be important to have.
- IF Function
- VlOOKUP Function
- SUMIF & COUNTIF
You must have the ability to use these Functions to their optimum by using them with cell references , relative and absolute references.
You will need to be comfortable with – Conditional formatting and text functions like TRIM, LEFT etc. Finally be familiar with Excel efficiency techniques like grouping and keyboard combinations
Intermediate Data Analysis Skills.
Intermediate users need to know the rules regarding setting up lists properly , be able to advance filter, subtotal and create basic Pivot Tables.
Jobs requiring Advanced Excel Skills:
For positions seeking advanced Excel skills, you should have the ability to flawlessly write & combine the following functions …
- IF and NESTED IF’S
- INDEX & MATCH
- SUMIFS & COUNTIFS
- OFFSET and SUMPRODUCT Function
- Conditional Formatting using formulas
So much of Excel is grabbing record fields from dynamic changing lists/tables for further processing but in many situations Vlookups are not suitable, so advanced users need to be very proficient in using the Index & Match Functions.
Keep in mind that an advanced Excel user should know which function or combination of functions to use on which occasion, as well as the ability to debug and audit formulas.
Data Analysis with Pivot Tables:
Advanced Excel users need to be able to analyse vast amounts of data so you must have a mastery of Pivot tables . Be able to implement a comprehensive range of pivot table recipes , grouping, show values as methods, slicers and calculations fields & items .
Finally know how to use Excel tables with Pivot tables and functions like Sumifs etc as well as the ability to build macros to help automate procedures.
How long will it take to acquire these skills
From my experience from helping students as a tutor with the ‘Complete Online Excel Course” the following is a rough guide.
Allowing for the time involved in watching the video lessons and study practice , it takes about 6 hours to reach basic Level, then about 10 hours to reach intermediate level and finally about 12 hours for both advanced level and mastery of Pivot tables.
Mark Clinton is an experienced Excel Trainer and works as a tutor with ‘The complete Online Excel Course,a CPD certified online Excel course.
This online course, available for individuals and companies, covers all levels of Excel from beginners to up advanced level. It comes with Live Chat support as you study. Excel certificates are available for each skill level.
You can get a special 5% off the course by clicking here and using the coupon onlineuk