# What are your top-of-mind questions to understand if a person has Basic Excel skill?



## mfexcel (Sep 28, 2014)

Well, I know it's debatable to define "Basic", "Intermediate", "Advance" in using Excel...

The fact is there are many people claiming that they are "Advanced in Excel" but don't know how to do some basic stuff like input date in excel.

My top-of-mind question is:
*How to input date correctly in Excel?*

What's yours?


----------



## Weaver (Sep 28, 2014)

I like your thinking on this, since bad date entry can cause so much pain with data.

I don't think my question would be as straighforward, but I'd ask for an explanation/demonstration of pivot table use.

When we're hiring, I have a workbook with some test questions in it, which I get candidates to complete


----------



## Comfy (Sep 29, 2014)

Q1. Are you a Microsoft MVP
A.Yes = Advanced user.
B. No = move to questions 2

Q2. Do you use Array formula, pivot tables and or VBA regularly?
A.Yes = Intermediate user.
B. No or don't know = basic user.


----------



## Kyle123 (Sep 29, 2014)

Explain Vlookup and demonstrate how to use a pivot table, I also test to see if they have any idea about how to lay a workbook out. I find that a key difference between novices and intermediate users upwards is how they structure data, novices have a tendency to split related data into logical tabs whereas intermediate users use the same table but use categories as it makes the data easier to work with

In contrast to the above, I'd class array formulas as advanced rather than intermediate


----------



## mfexcel (Sep 29, 2014)

Agree!   And I believe that can go further from Basic Array to Advanced Array too.



Kyle123 said:


> In contrast to the above, I'd class array formulas as advanced rather than intermediate


----------



## Fazza (Sep 30, 2014)

Like Kyle, data structure is a good indicator to me.

Array formulas to me mean users may not know the best approaches. Array formulas are bad for the large datasets I work with & would indicate to me that users don't know how to work smart with large datasets.

Advanced users to me would need to be competent VBA programmers able to create just about anything required, and, interact with other applications,


----------



## mfexcel (Sep 30, 2014)

to me, this is Expert! 



Fazza said:


> Advanced users to me would need to be competent VBA programmers able to create just about anything required, and, interact with other applications,


----------



## BiocideJ (Oct 3, 2014)

One of the best ideas I ever saw was on a job interview test I took. It may be a bit more broad and abstract than your question, but I think it was one of the best ways to actually screen a candidate's Excel skill.

There were several tabs with varying sets of data and on each tab there was a formula that was intending to accomplish a task that was stated on the sheet.
The 'trick' was that all of the formulas had something wrong with them. For instance wrong Formula given, formula missing an argument, referencing wrong cells, array formula not CTRL+SHIFT+ENTER confirmed, etc. 

The time limit on the test was relatively short, but it quickly gave insight into both how well the candidate understood Excel AND how they were at problem solving.



... I got the job


----------



## Joe4 (Oct 3, 2014)

> Array formulas to me mean users may not know the best approaches. Array formulas are bad for the large datasets I work with & would indicate to me that users don't know how to work smart with large datasets.


Agreed!  I rarely use Array Formulas and Pivot Tables.  When it starts moving in that direction, I usually stop using Excel and use Access instead, a program meant to handle relational databases!


----------



## steve case (Oct 24, 2014)

mfexcel said:


> Well, I know it's debatable to define "Basic", "Intermediate", "Advance" in using Excel...
> 
> The fact is there are many people claiming that they are "Advanced in Excel" but don't know how to do some basic stuff like input date in excel.
> 
> ...



I positively despise Excel's date function.  I download data from the net and Excel decides some of them are dates, and it really gets tedious to find them and correct them.  

I enter dates in the format YYYYMMDDHH.xx

I've never used hours and tenth's of an hour or finer but that's how I would do it.


----------



## Darren Bartrup (Oct 27, 2014)

Dates are a real pain.

I receive a dump of data each quarter and every quarter I ask him to put it in English date format and not US and every time I have to go through and change 6/7/2014 to 7/6/2014 - got a formula for it somewhere now.

Then I regularly get dates such as 7.6.14 or 7/6//20014 - to be honest, that last one is people just not caring about their data quality "'cos the Excel expert will sort it out." so they feel they can just dump on a sheet and expect us to give it a polish for them.

As my old boss said - if they put s**t in you can't expect anything but s**t out.

And Excel as a 'shared' data entry system to be built in a couple of weeks and just work? Ok, gotta stop now before I really start ranting.... 

Edit....

And can they have a workbook that does this, this, this and that to 100,000 rows of data allowing for the data to move, change and transform at any point and draw lots of charts and rank it and sort it and everything with formula as they don't know where the sort button is and ..... oh.... bit slow isn't it?


----------



## steve case (Oct 27, 2014)

Thanks for the support *(-:

*I wish there was a button that I could turn the date function off, but I am told there isn't one.


----------



## Jon von der Heyden (Oct 27, 2014)

Darren Bartrup said:


> I receive a dump of data each quarter and every quarter I ask him to put it in English date format and not US and every time I have to go through and change 6/7/2014 to 7/6/2014 - got a formula for it somewhere now.


I feel your pain.

By the way:

If your dates are entered e.g. mm/dd/yyyy but you want dd/mm/yyyy, do the following:

1. Highlight the column of _offending _dates
2. Invoke Text to Columns
3. In step 1 choose delimited
4. In step 2 uncheck all delimiters
5. In step 3 choose Date > *MDY*
6. Hit Finish

Voila!


----------



## Jon von der Heyden (Oct 27, 2014)

Fazza said:


> Array formulas to me mean users may not know the best approaches. Array formulas are bad for the large datasets I work with & would indicate to me that users don't know how to work smart with large datasets.


I love you to bits Fazza and I learned many things from you over the years, but I'm afraid I strongly disagree with you here.  I have spent many years working with DB's and interacting with DB's using Excel.  And I believe I have learned the very many good lessons you have shared on this forum.  But I have also learned from our formula big-hitters.  I definitely think array formulas provide solutions to problems that are simply too impractical so solve using a DB approach.  In fact I have even opted for array formulae to calc results where otherwise I would have to resort to T-SQL.

I do however think you have a valid point to a degree.  I do see _countless_ instances where array formulae are used unnecessarily and where better solutions would exist had the data been structured better (properly!).


----------



## Fazza (Oct 27, 2014)

Thanks for the kind words, Jon. I've too learnt much from others on the forum. There are uses for array formulas, for sure. Good data structure is an under-addressed important basis for spreadsheets. best regards, Fazza


----------



## mfexcel (Oct 27, 2014)

Can't agree more.  A well structured data base would absolutely make everyone's life easier.



Jon von der Heyden said:


> I do however think you have a valid point to a degree.  I do see _countless_ instances where array formulae are used unnecessarily and where better solutions would exist had the data been structured better (properly!).


----------



## Darren Bartrup (Oct 28, 2014)

Thanks for the pointer Jon - again, I learn something new with Excel / Access / VBA every day.


----------

