Office Wide Excel Skills Test

[timreichert]

We are a small office (25 people) of an economic consulting firm, and are considering running a contest for "excel bragging rights." We need to find a test of advanced excel skills. Can you recommend one?

Thanks in advance.

 

[Smitty]

Welcome to the Board!

There are all kinds of tests out there. Here's a start: http://www.microsoft.com/learning/mcp/OfficeSpecialist/default.asp

A google search will also turn up plenty, like http://stafftesting.com/tests.htm.

The degree and type of test is really subjective though. Like what level? Beginner, Intermediate, Advanced? And is it a General test, basic Formulas, advanced Formulas, VBA?

Just some thoughts,

Smitty

 

[toddbretl]

I am also from the same office. We're looking specifically for some exceptionally difficult questions, that would test someones knowledge of excel formulas, without involving VBA.

 

[PaddyD]

Well there are plenty here!

How about:

Devise a formula that will return the first visible cell from a filtered list.

Devise the shortest formula for returning, in alphabetical order, the just letters from an alphanumeric string (eg 'abc' from '1b23a9c2')

 

[Zack Barresse]

How about...


[1]
A table of data (including headers), dates in column A, multiple conditions to meet in subsequent columns (we'll say B & C). Return the most recent match from column D where criteria matches in column B & C with any date less than or equal to the starting date from column A.

Variables:

• Date
  Column B criterion
  Column C criterion

[2]
Sum a multi-conditional range with AutoFilter on.


[3]
Show a ranked list of unique ranks.


[4]
Use Conditional Formatting to highlight every odd row in a specified range.


[5]
Sum a column based on multiple conditions.


[6]
Count a column based on multiple conditions.


[7]
With Data Validation, prevent duplicates from being entered into a range.


[8]
Put the sheetname into a cell.


[9]
Use a VLOOKUP based from multiple conditions.


[10]
Extract only numerics from an alphanumeric cell value.

 

[Greg Truby]

Are the contestants allowed to search MrExcel.com, OzGrid, et al for help developing the answers? I'd think that'd be a key rule.

 

[Smitty]

Are the contestants allowed to search MrExcel.com, OzGrid, et al for help developing the answers?

Onlt the ones who know about it.

Smitty

 

[toddbretl]

Nope, I believe the plan is to turn off the office internet, or otherwise prevent people from using those websites. Thanks paddyd and firefytr, those are the types of questions we're after.

 

[Greg Truby]

How about a formula to allow a user to enter an item's SKU into the cell beside it and the formula creates a hyperlink to the matching SKU and product description on another worksheet?

 

[Zack Barresse]

Note that I tend to think the questions I posted are geared towards an Intermediate-type formula level. If you are not looking for something like that, how about just asking how to use or how to build some formulas? I.e. Rank this column of numbers; Use the DGET function on this (xxx) data range; Use the HLOOKUP function on this (xxx) data range; etc. I guess it depends on how advanced your office-wide skills go. That interpretation can vary from office to office.

Then there is always questions you can ask about native functions, such as Autofilter, AdvancedFilter, Text to Columns, Sheet/Workbook Protection, Find/Replace, Defined Names, Security Settings, Subtotals, Sorting, Pivot Tables, Charting, Hyperlinks, Comments, etc.