February 2013

Excel 2010 Advanced Skills Survey

By Andrew Chan, Rich Junker and Pradeep Kumar

Andrew Chan Richard Junker Pradeep Kumar
Andrew Chan Rich Junker Pradeep Kumar

I have been using Excel 2013 since its customer preview was first released. It has some great enhancements, but I hadn’t really appreciated them until I started working in a financial institute which is still using Excel 2003. I suddenly realized how incapable I was without all of the great features Microsoft has added to Excel in the last 10 years. So I was initially thinking to write a CompAct article “Excel from 2003 to 2013” to summarize all these enhancements. Then Richard Junker and Pradeep Kumar came along with an even better idea, i.e., develop an Excel advanced skills survey. Most actuaries have learned a huge amount of Excel skills by self-study. Yet we don’t have a proper Excel curriculum, and there may be some advanced features (both existing and new) that we haven’t even heard of. A simple survey may point up where our skills gaps are. An actuarial colleague from Australia, Tim Heng, recently wrote on the subject of Excel training in his article Excel vs. IT systems, in the October 2012 issue of CompAct:

“… The other key point of difference [Excel vs. IT systems] lies in standardization. Coding in Excel is no different from coding in C++, VB or any other programming language. The people who argue that Excel is not transparent obviously don’t look at the back end of their enterprise software! In software, there are examples of both bad and best practice. In first year courses at university, programmers are taught to leave comments, indent loops, and a whole host of other ‘rules’ that are aimed at making code more transparent and presentable. What do we get taught at university about Excel rules? (On that note, what do we get taught at university about Excel at all?)

“In Australia at least, the answer is ‘not very much.’ A survey of graduate staff (average two to three years of experience) conducted by Macquarie University showed that only 30 percent of respondents reported learning spreadsheet skills during their university education. An even lower proportion (28 percent) of respondents reported that they had been sent on a training course by an employer. This shows that the standards of spreadsheet development can be vastly improved. I’ve trained hundreds of people in public and in-house financial modelling workshops in the United States and Australia, and the common theme of feedback is that people appreciate the transparent and standardized methodology that we apply to modelling, and how they wished they’d learned more about Excel earlier in their careers.”

–Tim Heng is an associate at Corality Financial Group. Tim can be contacted at tim.heng@corality.com, Corality.com. Follow Tim on LinkedIn.

Returning to the survey … it does not cover basic skills that we use every day. We want the survey to be concise and simple. Accordingly, we distilled a list of 20 advanced Excel features. While we may not use them in every Excel application, they definitely allow us to exploit the full potential of Excel.

The survey takes only a few minutes, prompting you to rate your self-assessed mastery of each skill on a scale of 1 to 5. Note the links to the Microsoft online tutorial that addresses each skill. For a window in, below is the list of the 20 advanced Excel features in the survey.

Top 20 Excel Skills

  1. Advanced Sharing Options
  2. Information Rights Management
  3. PowerPivot
  4. Data Validation (list, range etc.)
  5. Goal Seeking and Solver
  6. Data Analysis and Statistical Functions
  7. SharePoint Excel Services
  8. Array
  9. Custom menus/forms
  10. User Defined Functions
  1. Slicer
  2. Microsoft Query
  3. Web Query
  4. Conditional Format
  5. Pivot Tables with Groups and Calculated Values
  6. Data Table
  7. Sparklines
  8. Advanced look up functions, i.e. INDEX, MATCH and OFFSET
  9. Scenario Manager
  10. Create Class Module


Once we have the survey results in hand and have identified the foremost needs of participants, we are considering inviting one of Microsoft’s certified trainers to conduct a webinar to illustrate how each of these advanced Excel features can increase productivity. If demand is strong following the webinar, we may offer subsequent e-learning courses, going into greater depth with selected topics. We greatly appreciate your contribution, as it will help us focus the content of the potential webinar we envision.

Please complete the survey.

P.S.: This survey is administered by the Society of Actuaries.

Andrew Chan, ASA, is a financial model engineer with ALG Consulting. He can be contacted at chan_a@algconsultings.com.

Rich Junker, FSA, CLU, MAAA, is an actuarial consultant at Junker consulting in Tampa Bay, and can be contacted at richardjunker@tampabay.rr.com.

Pradeep Kumar, FSA and CFA, is president at An Actuarial Approach consulting company. He can be reached at Pradeep@AnActuarialApproach.com