How to Prepare for an Excel Test at your Interview.

With
Excel becoming more important for so many jobs, many employers are now giving
Excel tests at interviews.
So what should a student know and how should they prepare for an Excel test.

First
of all, an experienced Excel examiner can gauge very quickly the depth of your
Excel skills. Even the simple example below can be enough to tell them all they
need to know about your Excel Skills as it  can
reveal so much. Let’s see .

Note:
Most times now, the examiner will be watching as you complete the questions.


excel image for articke

You are required to create a simple formulas in cell C5  to C9 which will work out the commission due to each salesperson. The commission will be 5%   of their Sales.  Try it out yourself now and then we will analyse the various 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%

Analysis:

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 interviewer 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%

Analysis:

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

Mark:
95%

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

Mark:
100%

Analysis:

As
above but this student 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 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 but the questions would
have been weighted differently  with over 50% of marks  going for the
formula question.

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 Excel training  online or
public 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:

Click
the relevant section below. Each test comprise of about 10 questions. The test
will be timed and your score and feedback will be given.

Basic
Excel
U
ser

Intermediate
Excel
User

Advanced
Excel
User


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
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

SUMIF
& COUNTIF

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 dropdown boxes.

Data
Analysis Skills.

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

For Advanced Excel Users:

Functions
& Formulas.

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

  • VLOOKUPS

  • IF

  • NESTED
    IF’S

  • INDEX
    &MATCH

  • SUMIFS

  • COUNTIF

  • 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.

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 analyse 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.

Leave a Reply

Your email address will not be published. Required fields are marked *