This week I have been in Wales, presenting training courses for local ICAEW members. This has become something of a tradition (it’s now my third year doing it) and I was looking at the different topics I’ve done for it with interest.
Some have changed year on year as expressions of interest have shifted, or the audience has changed. But the core of the presentation (indeed, of almost all the Excel training courses I have ever run) remains the same: conditional formatting, PivotTables, and a few formulas. It seems you can’t ever get enough of the classics.
Of course, most people’s needs are covered by just a few of the most important features. I reckon that significantly more than half of all formulas written are SUMs, for example. That’s hardly a surprise, but of course almost all Excel users know about SUM and how it’s written. I usually use it as the example of a function when introducing the topic simply because of how classic it is. But time and again you can introduce topics like conditional formatting or PivotTables to audiences and it will be new to many or even most.
Why are these longstanding and powerful features so commonly unknown to users? I suspect the reason lies with how most people learn Excel: through self-teaching and exploration. While functionality like sorting data or adding up numbers are very commonly needed and are obviously part of what users expect from a spreadsheet program, colouring according to content or data analysis are both less expected and less likely to come up. Many people don’t ever realise that Excel even has these capabilities. At the more experienced end, I suspect that even many advanced users don’t learn tools such as Power Query for similar reasons.
So, for as long as Excel remains a closed box that people mostly discover by self-guided exploration, probably many will continue not to stumble across useful and powerful tools like these. And the classics will always remain popular.
Tip of the week highlight: Nested dropdowns
This week’s tip is based on Excel Tip of the Week #219 from ICAEW’s Excel Community.
In-cell dropdown menus can be created from Data => Data Validation => Allow List. They allow you to restrict the allowed entries in a cell, giving the user a limited choice of approved options.
But what if we want to go further – to have one dropdown whose options are populated based on a choice in an earlier dropdown?
We can do this if we set up our options (in a separate sheet) with named ranges, like this:
And then create our data validation rules like this:
The INDIRECT function will read the option chosen for ‘Course’, and then plug in the appropriate named range!
One small caveat: If you change the ‘Course’ chosen, the ‘Menu choice’ will not automatically show as invalid.
Spreadsheet competency of the month
Over the last twenty months, I have used this slot to go through each of the twenty principles for good spreadsheet practice. Now that we have reached the end of those, for the next four months I will be working instead through the different levels of the spreadsheet competency framework (SFC). The SCF was created by ICAEW to provide a guide to how to talk about spreadsheet knowledge and ability, and what skills are needed for which roles.
The first level is Basic User. A Basic User will mostly be carrying out data entry tasks. They will have a grounding in the essential skills needed to avoid major wasted effort or bad practice, but few technical skills beyond that. Anyone that uses spreadsheets should be at this level at a bare minimum.
David Lyford-Smith is a technical manager at the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more.