Are your preparing for an excel interview?

We have you covered!


As an Analyst I conducted many technical interviews and created a few excel tests to filter out applicants

I know where you are right now…
You applied for that juicy job with all the benefits, and they actually called you confirming an interview…

But there is an excel test! How can I pass it?

Not to worry.
Grab a coffee and I will explain you what to do and what buzzwords to throw in, in the verbal interview, and what to expect in the practical test. wink

PLEASE NOTE THAT THIS IS GOING TO BE A LONG POST! 
IF YOU JUST WANT TO SKIM THROUGH YOU MIGHT AS WELL THROW IN THE TOWEL BEFORE YOU MAKE IT TO THE INTERVIEW. 
You need to spend about 10 minutes to read through this. But you will be glad you did.  

I will put this on the top otherwise you will miss it.

If you have any question, I am happy to help.
Just drop me a message on Facebook and I will try to answer any question you might have.

Thomas  

 

The Excel interview…

Chat icon

What to expect.

Generally the interviewer wants to make sure that the right candidate will be able to work alone, and deliver the data they want. 

Most of Excel work is boring.
And somewhat a repeating pattern of cleaning “dirty” data. Or digging up valuable data from tools that are reporting them. (SAP, Crystal reports, etc…)

SHOW CONFIDENCE!
I know it is not easy to sound confident if you just dabbled in excel at home, or a few classes in college.

Luckily, there is a few buzzwords around the Analyst/Administrator jobs that they want to hear.
Also, my impression, that in a lot of cases the person conducting the interview will only know a little more than the candidate itself. 

WHAT ARE THE BUZZWORDS?

That’s it.
Literally any HR person and most of the interviewers I met were satisfied if they heard a person throwing in those words…
We will discuss this in a bit of depth now…

GENERAL DISCUSSION,

ESTABLISHING THAT YOU ARE A MASTER OF YOUR TRADE

When they generally ask you, what level you are in excel, just use these statements:
(Or something along these lines)

  • “I am confident that I can deliver any data from any set of source.”
  • “Certainly, there is good data sources, and then there are some reports that give you rubbish outputs.”
  • “Data is key, data is gold for any business.
    If you don’t know the details that lies in data you might as well work blindfolded”
  • “There are challenges, where my known techniques won’t work, but I will figure out a way/research my options to get the data we need.”

Why this is important?

Generally, if this is a technical interview, one of your interviewers will be coming from the field.
(Fellow analyst, or admin, or someone who works with excel day by day.)
And this is the only advantage they have.
That they have to work with excel every day.

Unfortunately Excel is very hard to learn if you don’t have a problem to solve.

It is impossible to become good with excel at home doing the household budget, or figuring it out in a spreadsheet how much dog chow you need to buy for your pet.

By using the buzzwords above you can establish a rapport with that technical person along the lines that carries past experiences for him…
Why I am saying this?
You want to look the part. You want to look like an analyst, act like a veteran analyst, even smell like an analyst. 🙂

No analyst will remember the spreadsheets that they were able to whizz through, and pull the report straight away.
On the other hand most of us remember that report that cost you blood and tears to deliver.

Also, confidently explain that not all excel job works out fine for the first time. There are challenging ones that you will need to use extra resources.

These statements will put you down as and honest candidate who seen it all and willing to work through any problem.

YOUR BUZZWORDS

 

VLOOKUP, CHARTS, PIVOT TABLES…

In my 10 year in the excel field I found that for the average HR personnel
these are the words they been told to ask about.


HOW TO ELABORATE ON YOUR BUZZWORDS:

VLOOKUP
Is a simple search formula. We can find stuff with it in most datasets.
DO mention that VLOOKUP is very limited in it’s use.
And that there are more advanced formulas like INDEX-MATCH that are much more sophisticated.
If you mention that INDEX-MATCH is much more faster in larger dataset they will look at you like you are a wizard. 🙂

CHARTS
Charts are the bread and butter of any Analyst.
Management will want to see data in a way they can understand.
They want to see a graphical representation of the relations in your data.
Mention that sometimes there is a need to build charts that pulling together multiple tabs (Which you will be able to build if you hang around with us.)
Or to build charts that are interactive, with dropdowns that management can control.

PIVOT TABLES
The holy grail of HR personnel! laughing
If you can confidently say that pivot tables are a simple way to handle large / complex datasets, they will bow down.
In reality pivot tables are simple, once you get your head around it.
And mostly if it doesn’t work one way, you can just drag and drop until you find the way it works for you.
Along the pivot tables mention that you can build them into nice dashboards with the use of slicers, and timelines.

The most important thing to establish in this part of your interview:
That you might not know everything in excel. But you can and actively use resources to find a solution to any excel challenges!

AND NOW, ON TO THE TECHNICAL PREPARATION.

 

Here I will give you the formulas that most likely will encounter in an excel test. 
The rest is your homework.

You need to research these few formulas and you will be in. 

COUNT and COUNTA Formulas in Excel

There are a number of times when your boss will ask you to count.Count how many workers in a department, or to Count how many entries in a data set. And this is easier than you think. Excel has two handy formula for us. COUNT and COUNTA What is the difference between...

Excel IF formula

Probably THE most used excel formula Perhaps the most useful formula in the excel universe. This formula gives you the first building block of decision making. What's the formula used for? IF is a logical formula. It means that you can ask the formula to make a...

Zeros in my pivot table.

Thomas Szigeti Is there any way to hide zeros in pivot table? Sometimes we need to hide zeroes. Below you can find how to do it in a pinch. Hey all. This will be a quick post about a simple issue I had once. You know how when you make a pivot table, it is not as...

VLOOKUP vs INDEX MATCH

Today I want to discuss the question above. Which formula is better and why. We all know and love VLOOKUP Probably one of the first more advanced formulas that we all learned at some point. You can take a look at VLOOKUP here and find out how it works. Excel reads...

Auto format charts in excel

Auto format on an excel chart? What? Yes. It is too good to be true. In fact it is not true in the strict sense. I have crated a chart for you that is automatically color itself based on the data. For convenience I uploaded it into a video for you, but let's talk...

COUNTIFS formula with multiple criterias in excel

How to use the COUNTIFS formula with multiple criterias. In this next example we will build a simple, but posh little tool that you can wow your managers with. This is why you are here right? It will be based on the COUNTIFS formula, which is the big brother of the...

COUNTIF formula explained

Thomas Szigeti The COUNTIF formula With this handy formula we can count occurances very quickly. This is one of the basic formulas we will use day by day.  It can count occurrences in large data sets for a certain criteria. Have you ever had to tally up sales data, or...

How to use Excel AVERAGE

Excel Average formula explained So you have an array that you have to average. It is very simple with Excel Average formula. The Syntax is =AVERAGE(A1:A10) Where A1:A10 is whatever you want to average. You can also select individual cells, by holding down the Ctrl key...

Make banded rows with conditional formatting

We all know and love the banded rows in Excel tables, Is it possible to replicate this? When we deal with conditional formatting all we need to get to is TRUE or FALSE. For the banded column we need to write a formula that display TRUE in one of the rows, and FALSE in...

Excel VLOOKUP explained

VLOOKUP Basic The VLOOKUP formula is one of the lookups that we all have to learn to use. It is designed for Looking up a certain value in an array. Human Translation: If we have a bunch of names in one column with their salary in the one next to it we can find a name...
[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]