Test yourself with 8 short multi-choice Excel questions
Here's a little eight question multi-choice test that allows you really quickly to rank yourself with Excel. It's based on the material covered in the "Top 10 Excel formulas". If you score:
1. Shortcuts
What’s the best Excel shortcut ever?
A.???What’s a shortcut?
B.???“Ctrl C”
C.???“Ctrl Alt V”, “Ctrl D” and “Ctrl R” – my life just wouldn’t be the same without them.
D.???“Alt” – it’s the equivalent of asking the genie for three more wishes – it’s the pathway to shortcut Nirvana – it’s the mother of all shortcuts.
E.???I pride myself on almost never touching the mouse.?I use all the ones above.?Now let me tell you about my favourite shortcut...
For more see the best Excel shortcuts.
2. Excel formulas
Name 2-3 Excel functions you have seen used in other people’s models that you would love to know a bit more about.
A.???I am getting on the internet now.
B.???I am hitting “F1” now.
C.???I am opening some complicated spreadsheets right now.?My 2-3 functions are:?????????????????????????????????
D.???I am hitting "Shift F3" and "F1" now
E.???Here are the functions I think the rest of my, rather limited, colleagues could really do with knowing more about: ???????????????????????????????????????????????
?3. Vlookup
Tell me about your experience of Vlookups.
A.???What’s a Vlookup?
B.???I’ve seen it before but I don’t know how to use it – I’d like to know more.
C.???I can set up my own Vlookup with a bit of trial and error.
D.???I can set up my own Vlookup blindfolded.?I know to stick “False” into the back of a Vlookup.
E.???Vlookups are for amateurs.
?For more see Excel data picking.
4. Index and Match
Tell me about your experience of Index/ Match.
A.???What’s Index??What’s Match?
B.???I’ve seen them used before, I’d like to know more.
C.???I know all about Match, including what to put at the back (False).
D.???I know all about Index, including the fact that you can simplify Index if you want by omitting the row or column inputs.
E.???Index and Match are much better than Vlookup.
?For more see why we all like Index so much.
领英推荐
5. More data picking
You are given 10,000 lines of student loan data from Haversham Polytechnic and, knowing your brother’s student ID, decide to find out how much beer he really got through while studying there.?You:
A.???Dismiss that thought because it sounds like it might be unethical.
B.???Start scrolling through the data.
C.???Wonder whether you could use an If function to fish out the piece of data, or whether you should use a pivot table.
D.???Press “Ctrl F”.
E.???Straight away think to yourself that it’s the sort of challenge that could easily be solved using a Vlookup, Index/ Match or SumIf (you just can’t help yourself, you’re that kind of person) before quickly deciding to press “Ctrl F”.
See Excel data picking.
6. Data amalgamation
You are given 5,000 lines of customer data and told to place customers in categories according to their spend with the business.?You:
A.???Get on t'internet and start watching random videos, trying hard not to violate the company’s IT policies by drifting towards Taylor Swift’s new video release.
B.???Try an If function.
C.???Think that you might be able to solve it using some or all of Vlookup, pivot tables and Sumif.
D.???Try all of the above before actually talking to a real person who you think might know.
E.???I am the person who knows.
For more see how to sort and amalgamate a massive list of customer data.
?7. Excel macros
?Tell me about your experience of Excel macros:
A.???I have no experience of macros.
B.???I pushed a button on a spreadsheet that contained one once.
C.???I broke one once.
D.???I have recorded my own macro before and I know that they’re a bad idea because they’re impenetrable unfathomable and unauditable for most other regular spreadsheet users.?The only time I’d ever create one was because I was worried about the firm’s redundancy programme and wanted to make all the company’s spreadsheets unusable by anyone but myself.
E.???I know what “Alt F11” does.
8. If functions
Tell me about your experience of If functions.
A.???I’ve seen one before.
B.???I build them pretty regularly.
C.???I am comfortable with multiple nested/ embedded Ifs.?I know what this formula is doing: =IF(A1=1,"Don't embed",IF(A1=2,"Never embed",IF(A1=3, "Seriously, don't do it")))
D.???I use If functions every so often.?I would never nest or embed because it’s against my own personal life philosophy.?I would try and find another function to solve the problem, break the problem into its component parts and lines or maybe think of simplifying the If logic like this: =(A1=1)*B1+(A1=2)*B2+(A1=3)*B3.?I do this sort of thing every day: it’s how I roll.
E.???The best solution to a problem like this would be something like =INDEX(B1:B3,A1) at 15 characters or maybe =CHOOSE(A1,B1,B2,B3) at 20 characters where A1 contains the values 1 through 3, B1 contains the text “Don’t embed”, B2 “Never embed” and B3 “Seriously, don’t do it”.?Also, you are right that Index is the shortest function but I rebuilt all your formulas in a spreadsheet and double checked the character length using the LEN() function and it’s 16 not 15 for the Index.?Unfortunately the only way I could get the LEN() to work was by combining it with FORMULATEXT().?The final formula I used was =LEN((FORMULATEXT(A3))) where A3 contains the function =INDEX(B1:B3,A1).?So I ended up having to embed FORMULATEXT inside of LEN and having to violate my own personal life philosophy just to check character length.?Thanks a lot.
For more on If formulas see the If function masterclass.
How did you score?
Mostly Es and you probably didn't need to read this.
If you fancy a bit of help on some of the (essential) topics above, please bookmark the "Top 10 Excel formulas" material for later reading!?