Hello all and welcome back to the Excel Tip of the Week! This week, we have a Basic User post in which we are looking at just what I mean each week when I start my blog by saying “a Basic User post”.
The terms used in this blog – Basic User, General User, Creator, and Developer – are derived from the Spreadsheet Competency Framework (SCF), our second thought leadership publication about spreadsheets. It’s been over five years since the SCF was published, and so in this blog we’re going to take a look at what it has to teach us now. You can find our article covering the launch of this publication here, and the one-year follow-up here.
A potted history of the Spreadsheet Competency Framework
The SCF originated shortly after we completed our first publication, Twenty Principles for Good Spreadsheet Practice. The concept was simple: our Advisory Committee wanted to tackle the age-old problem of defining what different levels of spreadsheet ability really mean. We were spurred by the cliché of writing “proficient with Microsoft Excel” on a CV – a phrase so over-used and inconsistent as to be completely meaningless. The task we set ourselves was to try and make a meaningful and consistent set of competency levels that would actually be useful – whether to:
- Recruiters looking to specify what level of Excel ability they needed; or
- Job-hunters wanting to clearly get across what they were capable of; or
- Managers needing to understand what skills their team members had and might need to learn; or
- Learners looking to understand where they stood on their own journey and what to study next.
We came up with a system of four levels. These are as follows:
Basic Users are those with the essential skills to work with spreadsheets on tasks such as data entry and don’t generally make significant spreadsheets or formulas from scratch themselves. Core skills might include opening and saving files, reading and entering data, and using filters.
General Users are the largest group, comprising those that modify and update spreadsheets created by others and, also generate their own for simple purposes. They will know how to make simple formulas and use many of Excel’s built-in tools for data management. Core skills might include arithmetic formulas, fixed references using $s, cell formatting, and inserting charts.
Creators are more accomplished users that make complex spreadsheets for both their own use and for others to work on. They are capable with a wider variety of functions and capabilities in Excel, and can use these to solve a wider variety of problems. They are more likely to have spreadsheet use as a central part of their job roles. Core skills might include formula auditing and error checking, logical and lookup formulas, conditional formatting, and PivotTables.
Developers are the top echelon of spreadsheet ability, with mastery over one or more areas and deep knowledge across their chosen program. This group includes financial modellers, VBA developers, statisticians, and others who make extensive use of the depths of Excel’s ability. However, there’s no need to know everything in every area to qualify for this level – specialisation is common. Core skills might cover spreadsheet documentation and design, creating error checks, data manipulation and cleansing, VBA and macros, and more.
The SCF itself contains a full table mapping various spreadsheet activities to each level.
What’s changed in the past five years?
Since the publication of the SCF, things have changed in the spreadsheet world. Power Query, although it existed at the time, has become more ubiquitous and more accessible, especially with the rise of Power BI (which despite being a separate program does share its data transformation language with Power Query). Power Pivot has also grown in profile. Dynamic arrays were totally unknown at the time, but are now available in Excel for Office 365 and are set to roll out to ever more users – and with them, some of the most powerful new Excel functions like LET and LAMBDA are also on their way. These newer areas of Excel use aren’t all just for the experts – some are very approachable. I think if we were to redraw that mapping today, we would add something like:
- General User – Refresh a Power Query or similar data query
- General User – Understand a dynamic array formula and clear up a #SPILL! error
- Creator – Create a Power Query using the pre-set transformation options
- Creator – Import data into Power Pivot and connect data sets together
- Creator – Write dynamic array functions such as SEQUENCE, SORT, or FILTER
- Developer – Create custom Power Query transformations
- Developer – Create custom Power Pivot Measures using DAX
- Developer – Write a LAMBDA function
You may have seen of course that the Tip of the Week Index, which lists all the past posts in this series, includes groupings of the posts by their level. Indeed you will see that posts on the above newer topics roughly follow the mapping above, where I have made judgments on each in turn. If you know what level you are (or what you’d like to be), then these sorted lists are a great resource for learning more.