Most Difficult MS Excel questions

[satanboy]

Get him to explain VLOOKUP, and get it working on a decently complicated spreadsheet...that usually stumps most people.

Too easy.

 

[Gunner182]

Get him to explain VLOOKUP, and get it working on a decently complicated spreadsheet...that usually stumps most people.

Too easy.

Op ask him to explain what other function you can use instead of VLOOKUP (hint, INDEX and MATCH)

Also ask him to to create various pivot charts from different databases, but have slicers that link all the pivot charts together.

VBA??

 

[Gunner182]

Too easy.

Op ask him to explain what other function you can use instead of VLOOKUP (hint, INDEX and MATCH)

Also ask him to to create various pivot charts from different databases, but have slicers that link all the pivot charts together.

VBA??

Can't edit my post on tapatalk, but ja only how saw INDEX and MATCH was already mentioned.

You could also ask him to demonstrate some IF functions and combine that with conditional formatting.

 

[Gnarls]

VLOOKUPs are pretty basic Excel knowledge, I would say it's probably intermediate difficulty. Would still give a good idea if the guy knows what he's talking about.

Do some SUMIFS over multiple sheets while you're at it.

But you have to 'or' the conditions.

 

[roadcat]

How to auto-resize merged cells with wrap text. It is a bug that's been around for years. Merge two cells. Add wrap text to the merged cell. Enter text longer than the length of the merged cell. It doesn't auto-resize the cell. Do it without a merged cell and it works.

Damn, this has bugged me severely in the past!! The bug explains it.

 

[Arzy]

Indirect function could be fun as well

 

[etienne_marais]

Yeah, us IT guys are a******s who think they are geniuses, better put us back into our place

 

[Venomous]

Thanks for all the ideas. Keep them coming

The version in question is Professional Plus 2010

intentionally placed in a quote..

Have you ever had a spreadsheet with lots of numbers and even more formulas?
And one day when you are in a rush you can see there is a problem with a formula somewhere. Most people go from cell to cell to view the formula of each page.

And all the while there is a way for you to view all the formulas simultaniously, making it easier to see where it deviates from the general pattern.
IE view formulas not answers

 

[Fudzy]

intentionally placed in a quote..

VBA allowed?

 

[Venomous]

VBA allowed?

nope.

2 buttons on keyboard

press to display
press again to display results only.

no scripting or any such required


it is a very old short cut that I was taught, but not something taught in fornal teaching/training for excel I have come accross.

just asked the person. it was last formally taught with office 95, or whatever the version was called then, but only in advanced classes.

 

[MickeyD]

Easier one...

Ask him how to convert text to dates from an imported txt file and then to add/subtract two dates in different cells with the reply in a new cell.

 

[Venomous]

Easier one...

Ask him how to convert text to dates and then to add/subtract two dates in different cells with the reply in a new cell.

that would also work

 

[LaraC]

nope.

2 buttons on keyboard

press to display
press again to display results only.

no scripting or any such required


it is a very old short cut that I was taught, but not something taught in fornal teaching/training for excel I have come accross.

just asked the person. it was last formally taught with office 95, or whatever the version was called then, but only in advanced classes.

And those two buttons would be?

 

[Arzy]

And those two buttons would be?

I'd tell you but I don't want to spoil it for venomous

 

[Gnarls]

I know this one. I used it the other day. It's like alt, d, s to get to the data sort screen but I'm stumped right now

 

[LaraC]

I'd tell you but I don't want to spoil it for venomous

I’ve asked Google and the answer was given. Cool trick Venomous.

 

[Fudzy]

nope.

2 buttons on keyboard

press to display
press again to display results only.

no scripting or any such required


it is a very old short cut that I was taught, but not something taught in fornal teaching/training for excel I have come accross.

just asked the person. it was last formally taught with office 95, or whatever the version was called then, but only in advanced classes.

Ctrl - ` will show you the cell formulas but how do you highlight the wrong formulas?

 

[MickeyD]

Ctrl - ` will show you the cell formulas but how do you highlight the wrong formulas?

djirre, that's mos simple! Wiff this, of coarse...

 

[gjm]

Ask him to explain pivot tables.

 

[Venomous]

I know this one. I used it the other day. It's like alt, d, s to get to the data sort screen but I'm stumped right now

nope.

2 buttons only, not 3