What Do We Mean By "Advanced Excel" Skills

Many working professionals have this question. "What I should be able to know to be considered “advanced” in Excel?

Microsoft Most Valuable Professionals (MVPs) sometimes recommend a “user scale” to categorize the abilities of Excel users. I like to use it as a self-test when I am teaching a class in Excel because it lets participants see where they stand fairly quickly.

The following scale is not exhaustive but gives a fairly accurate idea on how you can scale yourself on Excel skills.

Novice

• Can successfully navigate and save an Excel workbook.
• OK with Data Entry tasks.
• Novices can build formulas using: SUM, IF, AVERAGE, COUNT, ROUND

 Intermediate

• Comfortable building formulas to manipulate text and dates
• Knows what a Pivot Table is and how to build one
• Knows what an add-in is and how to install one
• Understands and can use the AutoFilter feature
• Can record a macro and use it later
• Can successfully edit/modify simple recorded macros
• This category includes most users who claim to know Excel very well. Be proud of this skill level, because an Intermediate user is usually the best at Excel in their work group.
• Intermediate Formulas: SUMIF, COUNTIF, VLOOKUP, CHOOSE, MID

 Advanced

• Knows what array formulas are and how to use them
• Can create and modify macros with or without the macro recorder
• Knows how to build a macro that can be triggered by events
• Familiar with most, if not all, of the functions in Excel
• Comfortable teaching or helping others with Excel
• Advanced Formulas: SUMPRODUCT, INDIRECT, INDEX, MATCH, OFFSET

Expert

• Can build add-ins for distribution and widespread use
• If you can think it, they can build it with Excel
• Most likely, a disturbed individual who spends too much time thinking about spreadsheets
• Expert in Formulas: A true expert knows the strengths & weaknesses of every single function, and has probably had to build some new ones that were not available

 Guru

• The true Excel elite
• Probably just a handful of them in the world
• If you are one, you know who you are
• If you're an Expert, you probably know their names
• From time to time, you might see one posting in a forum

If you claimed to be an Advanced user in your resume, the interviewer might ask you to compare the use cases for various lookup formulas. Start by comparing VLOOKUP and INDEX & MATCH. Why would you use SUMPRODUCT, SUMIFS or LOOKUP? What are common causes of errors? How would you make VLOOKUP or MATCH run faster? Would it matter which Excel version you used? Which of those functions accept Boolean expressions—and do you need to array-enter such formulas?

Speaking about SUMPRODUCT, how do you handle “and” and “or” criteria? Should you multiply your parameters or separate them with commas? When should you use SUMPRODUCT versus SUMIFS? What are the issues with using SUMPRODUCT in VBA code?

You might also be asked you to start talking about INDIRECT and OFFSET. What are their benefits? What are their drawbacks? When do they return error values? Are there alternatives? How might you add up visible values (i.e. excluding those hidden by a filter) that are subject to a criteria test?

Given a good list of challenge questions, some people might prepare glib answers for each. No problem. The interviewer can still ask you to describe workbooks where you used those features, and why you didn’t choose a different approach.

Credits 1. MVP Aaron Blood 2. Brad Yundt, Mechanical engineer and Excel expert.

There is a saying that “The global economy is built on two things: the internal combustion engine and Microsoft Excel.”

:)