80% of people answered these questions in Excel incorrectly

Glorious Microsoft Excel.

Guardian of business knowledge, unlocker of data secrets. Frustratingly inaccessible to many people.

Excel can be painful. Learning to make sense of it can also be the best investment in your business skills that you ever make.

We see people of varying Excel capability every day – Excel has always been our most popular training course, hundreds of thousands of people have now learned with us.

Part of the EwB way of teaching is to ask questions before a user starts to identify their current capability. We thought we’d take a look at the questions most people got wrong.

If you get a couple of them right – you might be doing better than you think!

Questions

1. Which of the following is a Formula Auditing option? (9% of people got this correct)

A. Reconcile Totals
B. Trace Precedents
C. Conditional Formatting

2. What does the function =TRIM() do? (11% of people got this correct)

A. It rounds fractions to a specified number of decimal places
B. It strips out all spaces from text
C. It strips out extraneous spaces from before and after text

3. What is a Timeline? (12% of people got this correct)

A. A curve fitted to a set of data in an Excel chart
B. A log of Excel activity to allow actions to be undone or redone
C. A graphical way of filtering by date in a Pivot Tabl

4. What is the final argument 0 doing in this formula? =VLOOKUP(F7,B1:D5,3,0) (13% of people got this correct)

A. It’s an error, the formula will not work
B. It’s making sure the formula finds cells containing a 0 as well as a 3
C. It’s making sure only entries that EXACTLY match cell F7 are looked up

5. Why might grouping rows be preferable to hiding them? (13% of people got this correct)

A. Grouped rows have a symbol in the spreadsheet margin so it is clear when they are not displayed
B. Rows which are not adjacent can be grouped together
C. Grouped rows are automatically assigned a group ID, making reference to them easy

6. What is the syntax of the IF(A,B,C) function? (14% of people got this correct)

A. A and B are tested for equality, C is the result if they are equal
B. A is the condition, B is the result if the condition is met, C is the result otherwise
C. A is the condition, B is the result if the condition is met, C is the flag for Boolean algebra

7.  How could you ensure users entered only days of the week into a cell? (17% of people got this correct)

A. Use the function =WEEKDAY()
B. Format the cell as a custom date of syntax ddd
C. Define a Data Validation List consisting of weekdays

8. What would the formula =SUMIF(A1:A10,”Village”,B1:B10) do? (22% of people got this correct)

A. It adds together the numbers in the ranges A1:A10, B1:B10 and the named range “Village”
B. For rows where column B cells contain the word Village, it adds up the numbers in column A
C. For rows where column A cells contain the word Village, it adds up the numbers in column B

9. What would have to have been set before the formula =SUM(Grid) would work? (22% of people got this correct)

A. A function called Grid, referring to some cells
B. An absolute reference called Grid, referring to some cells
C. A named range called Grid, referring to some cells

10. What does the character ^ do in an Excel formula? (23% of people got this correct)

A. Raises a quantity to a power (e.g. 3^2 is 3-squared)
B. Reformat the characters following it as superscript
C. Force characters following it to be interpreted as text rather than a number

Answers:

1: b, 2: c, 3: c, 4:c, 5: a, 6: b, 7: c, 8: c, 9: c, 10: a

How did you do?

If you answered more than 8 right – well, you’re pretty damn good at Excel, maybe consider an Advanced Excel course, or even a course in Data Science to really hone those skills.
Fewer than that and it’s probably worth taking a look at our Excel course!