Advanced Excel is a skill that is in high demand. A quick query on LinkedIn showed over 68,000 jobs worldwide requesting advanced Excel, as of January 2020.
The jobs that were showing on LinkedIn are generally for financial jobs, such as Financial Planning Manager, or Financial Analyst, though the roles spanned across geographic regions as well as sectors.
However, there is no clear definition of what advanced Excel represents.
In one job description, advanced Excel is defined as
Highly proficient with Microsoft office and particularly Excel (i.e. pivot tables, lookups, advanced formulas)
In another, it required a greater skill set.
Excel advanced functions (macros, index, conditional list, arrays, pivots, lookups)
For most job descriptions, it simply asked for advanced skill in Excel. These jobs did not break down the types of skills within Excel that constitute an advanced understanding of Excel.
In order to try and get a better understanding of how Microsoft defines advanced Excel, the first place to look is their certification exams; the Microsoft Office Specialist.
The Office Specialist Exams
The Microsoft certification site shows there are currently 5 exams that are available for Microsoft Excel.
Two of the exams deal with Excel 2016 instead of Excel 2019 / 365; these exams are being phased out by more current exams. The most advanced exam deals with a combination of Word and Excel and does not provide details on what is evaluated in this exam.
Effectively, there are 2 exams:
Microsoft Office Specialist: Excel Associate (Excel and Excel 2019)
Microsoft Office Specialist: Microsoft Excel Expert (Excel and Excel 2019)
What is an Excel Associate?
The official list of skills provided by Microsoft for an Excel Associate includes:
- Manage worksheets and workbooks
- Manage data cells and ranges
- Perform operations by using formulas and functions
- Manage charts
- Manage tables and table data
The goal at the Associate level may not to be able to produce complex spreadsheets. Instead, the goal may be to be able to work with templates that have been provided by more adept Excel users.
Functions that are referenced by name at this level include:
- Text functions such as LOWER() and LEFT().
- Basic calculation functions such as SUM() and MAX().
- Simple conditional logic with the IF() function.
An Excel associate may be required to work with a sheet that is created by a more adept user. They could also make some modifications to a sheet. However; they would have difficulty creating a spreadsheet without guidance.
What is an Excel Expert?
Based on the skills provided by Microsoft for the Excel Expert exam, the skills are sufficient to meet the requirements of most jobs that ask for advanced Excel skill.
- Manage workbook options and settings
- Manage and format data
- Create advanced formulas and macros
- Manage advanced charts and tables
Functions that are referred to by name at this level include:
- VLOOKUP(), INDEX() and MATCH().
- Conditional column formulas, including SUMIFS().
- Basic financial functions, including PMT().
This level also refers to pivot tables, as well as recording and copying basic macros. At this point, a user has gone from being someone who can work with spreadsheets to someone who can produce spreadsheets from some data sources.
What Advanced Excel concepts are being missed as an Excel Expert?
While the Excel Expert exam may provide sufficient information in order to create a spreadsheet from a data source, it misses many elements that are important in modern Excel processes.
More advanced elements that are not discussed include:
- Math, financial and statistical formulas.
- PowerPivot (both DAX and M)
- VBA programming
Other Formulas
The Excel Expert exam provides a very good overview of some of the key data analysis functions, such as VLOOKUP() as well as conditional formulas such as SUMIFS().
It also provides a good introduction to text functions; even at the associate level!
However; it only provides a basic introduction to financial functions (the PMT() function) and does not use math functions such as RAND().
A notable omission in the text manipulation section includes the TRIM() and SUBSTITUTE() functions. These functions ensure that data is imported properly if Power Query cannot be used.
Power Pivot (DAX and M)
There are currently no certifications or exams offered by Microsoft regarding the DAX or M programming languages. This is unfortunate, as these are critical elements of Modern Excel.
The exam which is offered by Microsoft, Microsoft Certified Power Platform Fundamentals provides an overview to the Power Platform service, but is focused on the business value of the platform as a whole.
While these tools have a graphical interface, some additional resources are recommended to start on the right path with the programming languages!
The best overview of the M programming language is the “M is for (Data) Monkey” book by Microsoft MVP’s Ken Puls and Miguel Escobar.
A good starting point for DAX learning is the “Analyzing Data with Microsoft Power BI and Power Pivot for Excel” book. This book is written by Alberto Ferrari and Marco Russo, the two most knowledgable people in the world on the topic of DAX!
VBA Programming
Given the focus by Microsoft on other tools (such as Power Query / Automate), it may not be beneficial to learn VBA. This sentiment is echoed by a 2019 survey on StackOverflow where developers considered VBA their most dreaded programming language.
This is not to say that VBA is not a useful programming language. However; other programming languages may be more valuable.
Conclusion
There is no clear single definition of what skills an advanced Excel user possesses. The Expert definition provided by Microsoft meets the needs of the job market, but is missing some key concepts.
Advanced users in modern Excel require an understanding of programming concepts that did not exist 10 years ago.
Users who possess skills that are not present in the Excel Expert certification will provide the greatest value to business in the future.