How to Prepare for and Pass  an Excel Test.

How to Prepare for and Pass an Excel Test.

How to Prepare for and Pass your Excel Test.

With Microsoft Excel skills  vital for most  jobs now , many employers are  testing prospective employees  on their Excel skills with  interview questions or even doing sitting an  Excel tests . So what should a student know and how should they prepare for an Excel test.

We will look in detail  at the following factors.


  1. Can I bluff an excel test ?
  2. This one test question reveals your excel skills instantly.
  3. Create an instant report on your  current Excel  skills.
  4. How to show your Excel skills on a resume
  5. Interview questions to ascertain your excel skills and how to answer them.
  6. What is meant by jobs seeking  ‘ basic’  Excel skills.
  7. What is meant by jobs seeking  ‘ intermediate’  Excel skills.
  8. What is meant by Jobs seeking  ‘advanced Excel skills with data analysis.
  9. How long does  it really take to master Microsoft Excel?

But first, is their a real difference between somebody who has a basic knowledge of Excel and one who has studied and mastered it. Well just take a look at  this video.

In it , we can see a  ‘ trained Excel user ‘ and ‘un-trained‘ Excel user tackling five common real-life  Excel tasks.
Notice the huge difference in time and effort. Imagine  that time wasted multiplied by  300 days a year.  What one would you employ ?

 Despite the rapid advances in technology, Excel skills are still one of the most valued skills in the workplace as employers want people who can use it quickly, safely and to it’s potential.

You can’t Bluff your Excel Skills.

First of all, an experienced Excel examiner can gauge very quickly the depth of your Excel skills. Even the simple example below is  enough to tell them any awful lot about your Excel Skills . Lets see how.

Many times now, the examiner will be watching as you complete the spreadsheet.

You are required to create simple formulas in cell C5  to C9 which will work out the commission due to each salesperson. The commission is  5%   of their Sales revenue.  Try it out yourself now and then we will analyse the various answers to this question.

excel training course manchester


Interviewee one  writes this formula as the answer :      =c5*100/20  copied down.

It took him/her  5 minutes to complete.

Mark awarded:  45%



Why such a low mark ?
Well on the positive side, this answer shows that this interviewee  can build a basic formula and knows the  basic Excel formula operators. But this interviewee has hard-coded the formula with the commission amount but also the  interviewer noted that this interviewee  typed  the formula for each  salesperson instead of copying it down.


Interviewee two writes this formula as the answer :   =C5* 5%

Take taken:  1 minute.

Mark awarded: 55%


This answer is similar to the first answer but in addition shows  knowledge of percentage formatting and this interviewee copied the formula down by double clicking on the fill handle.


Interviewee three writes this formula as the answer :   =C5* $C$1

Mark: 95%

Time taken: 40 seconds


As above  but this interviewee shows he/she  understands and uses the three essential Excel concepts, namely

  •  Cell references
  •  Relative references
  •  Absolute references.


Interviewee four writes this formula as the answer :  =C3*C$1

 Time : 40 seconds

 Mark: 100%



As above but this interviewee  also shows that they know about Partial references. If the above question was set to find a user with intermediate Excel skills, answers 1 and 2 would have failed them straight away. If set for a basic Excel skills, answer 1 would have failed and answer 2 would face more questions.


This question would have come with other questions, like format the values to currency format, add another  column, total and average sales figures , put a border around the values and print out the spreadsheet etc  but the questions would have been weighted differently  with over 50%+ of the marks  going for the formula question above.

How to prepare?

 You cannot bluff an Exam test, so be honest.

If Excel skills are vital for the job you are going for and you don’t have  these Excel skills, then  book into an Online Excel training   or public  Excel course  beforehand and tell them. This shows initiative, honesty and ambition.

Before we look at how to prepare to pass your Excel Test, let’s test your current Skills levels.


Test your current Excel Skills:

We have created  a  free ‘  Testing  Workbook’.  It   requires  you to complete seven Excel  tasks. It will  time and  Auto-Correct your work  and  produce  a personalized report of your performance in terms of efficiency and  Excel knowledge.

There are seven special Tasks to complete. (Max time 20 Minutes)

You can download a free copy from here.


Armed with your Test results, how can I prepare?

If you’re Excel skills are rusty, then generally for an Excel test I’d recommend the following as areas to revise by creating a sample spreadsheet like a personal budget or membership list.

Job seeking just Basic Excel skills:

You will need to know the basic Excel Functions like – SUM, AVERAGE, MIN and Max. Also you will need to be competent in Formatting , such as Borders & shading, Word wrap, alignment and number formatting, especially currency formatting. Also know how to delete formats properly. Knowing how to access cell references in different worksheets and workbook is a vital basic skill.


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 seeking  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 and Formulas, knowledge of the following will be important to have.


  • IF Function
  • VlOOKUP Function
  • Text Functions like LEFT,RIGHT, MID


You must also show that you can use these Functions to their optimum by using cell references, relative and absolute references.

Also you will need to be comfortable with – Conditional formatting, page and print layout. Intermediate users would be expected to be familiar with times and date calculations, creating custom charts, data validation especially creating drop-down boxes.

Data  Analysis Skills.

Intermediate Users need to be able to filter, advance filter,  subtotal and be able to create basic Pivot Tables.

Jobs needing  Advanced Excel Skills:

Functions & Formulas.

An advanced user of Excel must have the ability to flawlessly write & combine formulas with …


  • IF
  • SUMPRODUCT Functions
  • Array Formulas.

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.


Conditional Formatting:

While even basic users should know how to do basic conditional formatting, an expert Excel user needs to be able to combine formulas with conditional formatting, so you can highlight data that meets almost any condition.


Reports : Formatting and Tables

Advanced Excel users need to be able to structure and present their reports in an effective professional manner .This will require excellent knowledge of Excel techniques like Tables, cell styles and formatting options and of course advanced Pivot Tables skills.

Advanced Charting:

Charts are vital for communicating data effectively and clearly. The main skills required for advanced charting are,

  • Having the ability to choose the right chart for any given situation.
  • Use features like in-cell charts & conditional formatting charts
  • Have the skill to create dynamic & interactive charts
  • Know how to combine various charts in to one.


Pivot Tables:

Expert Excel users need to be able to analyze massive amounts of data quickly so you be required to be very well acquainted with all features of Pivot tables, such as – grouping, show values as, slicers and calculations fields & items.