I originally wrote this post in April 2011 for the ATS Professional Learning Center blog. 

Microsoft’s Excel 2010 Expert exam is hard. Really hard. No one who has come into the center to take the exam – even our own students – has passed in the first try. We currently have a 90% pass rate with one retake. The rate drops off considerably from there. We have a 0% pass rate for anyone coming in to take the exam without taking a class or doing a tutorial first. The exam is supposed to be hard. An Excel Expert must have the skills assessed in the exam. So what’s the problem?

It’s a problem of marketing, or more specifically, consumer knowledge. The Microsoft Word 2010 Expert and Excel 2010 Expert exams are only a year old. There aren’t any preparation materials yet, and they seem to be a long way off. While there are a number of tools available to help you prepare for the Excel 2010 Core exam, professionals wishing to take the Expert exam are left to their own devices for now. Even our Excel Expert test prep tutorial is a curated collection of lessons on the topics listed as being tested on the exam:

TOPIC AREA SKILLS MEASURED
Sharing and Maintaining Workbooks
    • Apply workbook settings, properties, and data options:
      setting advanced properties, saving a workbook as a template, and importing and exporting XML data

 

    • Apply protection and sharing properties to workbooks and worksheets: protecting the current sheet, protecting the workbook structure, restricting permissions, and requiring a password to open a workbook

 

    • Maintain shared workbooks: merging workbooks and setting Track Changes options

 

Applying Formulas and Functions
    • Audit formulas: tracing formula precedents, dependents, and errors, locating invalid data or formulas, and correcting errors in formulas

 

    • Manipulate formula options: setting iterative calculation options and enabling or disabling automatic workbook calculation

 

    • Perform data summary tasks: using an array formula and using a SUMIFS function

 

    • Apply functions in formulas: finding and correcting errors in functions, applying arrays to functions, and using Statistical, Date and Time, Financial, Text, and Cube functions

 

Presenting Data Visually
    • Apply advanced chart features: using Trend lines, Dual axes, chart templates, and Sparklines

 

    • Apply data analysis: using automated analysis tools and performing What-If analysis

 

    • Apply and manipulate PivotTables: manipulating PivotTable data and using the slicer to filter and segment your PivotTable data in multiple layers

 

    • Apply and manipulate PivotCharts: creating, manipulating, and analyzing PivotChart data

 

    • Demonstrate how to use the slicer: choosing data sets from external data connections

 

Working with Macros and Forms
    • Create and manipulate macros: running a macro, running a macro when a workbook is opened, running a macro when a button is clicked, recording an action macro, assigning a macro to a command button, creating a custom macro button on the Quick Access Toolbar, and applying modifications to a macro

 

  • Insert and manipulate form controls: inserting form controls and setting form properties

What we have seen is that testing candidates don’t really understand what the exam is assessing. Because most users typically implement less than 15% of Office applications’ actual capabilities, users don’t know what they don’t know. It is possible to use Excel every day and not use the functions on the Expert exam. In fact, that is why there is now a Core and an Expert exam. Many test candidates that we see have not been using Excel 2010 very long, and often only download the trial at our suggestion when they sign up for the exam! While Excel 2010 is based on and very similar to Excel 2007 – it is imperative that users know the specific software that will be tested.(from the Microsoft Learning Website)

The Excel Expert exam (and the Word Expert, for that matter) tests your advanced use of the application over time. That is, you must be using the advanced functionality described above every day, for an extended period of time (at least a few months). The examination is timed – you only have 50 minutes to complete the assigned tasks. This means that you have no time to look for where the tool you need is located. Using the program features must be instinctive and natural, just like using Ctrl-S to save periodically as you work.

Please note that not passing the exam is not necessarily a statement on your ability to use the Excel program. Many people do not test well in a timed environment. Additionally, if accomplishing the task takes you a few minutes, your knowledge of the program is still valid. However, as I mentioned, you only have 50 minutes to complete all of the tasks assigned in the Expert exam.

This is not an exam you can cram for, because it is performance-based. The test puts you in a simulated Microsoft Excel environment, and instructs you to complete various tasks. There are no multiple choice questions. I highly recommend getting detailed, formal instruction in Microsoft Excel 2010 under your belt before attempting the Expert exam. Moreover, download the trial and go through each and every exam topic to ensure that you know how to accomplish these tasks quickly. The Microsoft Excel 2010 Expert certification is a great achievement. It requires a high level of skill – and speed – to acquire it. Good luck!

excel-expert-study-guide

Previous post: • Next post: