Job description - Excel - "Extremely advanced level"

[StormGrey]

Been invited for an interview for a role requiring ...understanding of excel spreadsheets to be at an extremely advanced level

Just wondering whether anyone could give an idea of what formulas/tools I should be practicing ahead of the interview, that would classify as extremely advanced

Have a feeling that doesn't actually need to be that advanced, reckon just looking for someone who knows how to use excel as will spend alot of time working with it - however want to get some practise in ahead of interview just in case

Thanks

[davepoth]

Things that this usually involve will include (but not be limited to)

Vlookup (and Hlookup)
Pivot tables
Macros

-edit-

And also

"we really should be using a database for this"

[EmmaJ]

Some guidance on the industry you're applied to would help?

Personally I'd say extremely advanced level of Excel knowledge would include VB, pivot tables and all the other financial analysis you can perform with Excel the latter being something I don't use as an IT bod but former two are really useful in my world. Being able to knock up eye catching charts is also another plus as management can never get enough of those

Good luck with the interview OP

[StormGrey]

Thanks both, all great info

Industry wise..... job in automotive, but is not a financial role......just requires some analytical knowledge I guess and knowledge of how to go about setting documents up that show what standard/optional etc. on specific vehicles in specific markets (OXO chart) (know this sounds like basic excel, but imagine must use coding/macros to speed process up)

Thanks

[pherlopolus]

I'd think that the ability to use complex maths theory and the ability to put thought into results would be handy. Have a look at power pivot too for getting data from databases.

As mentioned being able to say when a database should be used instead would help.

Being able to envision the underlying structure of the data/formulas rather than just a whole load of squiggles on the monitor would put you in the top 5%

[oldbanger]

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

[davepoth]

oldbanger said:

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

Depends who's asking I suppose. I've had bosses who thought IF statements were very advanced.

[mike9009]

I always like 'CONCATENATE' which comes in handy occasionally but I just like saying it and most people I have come across do not know it as an Excel command!

[98elise]

oldbanger said:

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

This. I've taught these functions to normal users in a short space of time.

Vba is advanced as its the least point and click.

[MKnight702]

INDIRECT used in formulas can be very useful for comparing different sets of data. But overuse can cause the spreadsheet to become unstable.

[texasjohn]

98elise said:

oldbanger said:

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

This. I've taught these functions to normal users in a short space of time.

Vba is advanced as its the least point and click.

Agreed. The costing project I run is very Excel-heavy. I'd only consider the guys who can write VB code as 'advanced'. The rest of the team are at intermediate level and can use pivots and most functions. Apart fro m one young man who gets a special mention for asking what the dollar sign does.

[Jader1973]

I've used excel extensively in the auto industry. VLOOKUP is about as advanced as I need - and I used to manage files that used ALL the columns. (I ran out of columns).

My experience is the files get too big and unstable pretty quickly. Clever formatting becomes a pain in the arse eventually

[Otispunkmeyer]

oldbanger said:

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

Think I need to learn those as well, at least just know about them. I went straight to VB macros to do things I wanted excel to do. In lieu of matlab that is, it was just more natural to carry on coding than learn new things. The lookups sound like they might be nice ways to do what I clumsily cludged together in VB!

[Gaspode]

I would agree that 'Extremely Advanced' would go beyond standard stuff like formulae, macros, pivots etc. I would expect it to embrace VBA, connecting to backend SQL databases, and possibly even connecting to multidimensional cubes and querying in MDX?

[mikef]

I'd swot up on goal seek, pivot tables and the IF functions (SUMIF, COUNTIF)

[mikef]

Gaspode said:

I would agree that 'Extremely Advanced' would go beyond standard stuff like formulae, macros, pivots etc. I would expect it to embrace VBA, connecting to backend SQL databases, and possibly even connecting to multidimensional cubes and querying in MDX?

ie using Excel for things where you shouldn't really be using Excel

[pherlopolus]

What's the job title? One persons advanced is another's intermediate...

[Countdown]

oldbanger said:

It really does depend, but to me advanced would equal vba scripting, macros etc.

Thing like, pivots, vlookup, hlookup, conditional formatting and most complex formulae would come under intermediate to my mind.

I'd agree with this. I wouldn't consider pivots or vlookups advanced.

[Gaspode]

mikef said:

ie using Excel for things where you shouldn't really be using Excel

[mike-r]

Apply, nod and agree to any requirements, learn on the job. Done.