ICAEW Excel tips: Restrictions breed creativity
I’m a big believer in the above maxim: restrictions breed creativity. An open-ended and free project will tend to be solved in a familiar and comfortable way. Push somebody into approaching something from a new angle or without their familiar tools will make them create new pathways in their brain, and connect ideas they otherwise wouldn’t have.
It’s by forcing someone to solve a problem in a new way that you get the best ideas – even if you’re forcing yourself to.
I had my own restrictions this month when looking to produce a new type of Excel content. ICAEW produces blogs, webinars, in-person training, and many other kinds of content, but we wanted to explore something new – making short videos, each explaining a key Excel skill or topic in just three to four minutes.
Before joining the Institute I was an Excel trainer, and I’m very familiar with explaining topics like lookups, PivotTables, and conditional formatting to an audience. But in that context, I always relied on interacting with my audience, asking them questions and gauging their reactions to see whether I was on target, or needed to shift my approach and explain something a different way. I have done live webinars, too (you can see several of them for free here) – and those required thinking about communication in a different way: without a visible audience, I had to pre-empt their reactions, and try to explain each point in multiple different ways as I was going on.
Short videos were another step: not only was the time much more compressed, but to make a good video, we had to try and make these videos smooth and professional, rather than the more conversational, relaxed tone in those webinars.
I’m normally an instinctive speaker and don’t do much in the way of prepared notes, but for this I had to think a lot more about each topic, and prepare a script for how I wanted each one to go. It helped me once again to understand my topic better.
The lessons here don’t just apply to talking about Excel, but actually learning to use it better, too. For example, if you have to make a solution that will work on older Excel versions, you might have to get creative about workarounds for functions that only exist in the latest releases. You can also create your own restrictions to stretch your creative muscles; for example, if you want to practice keyboard shortcuts – unplug your mouse! See how far you can get into doing a task with no mouse available at all. Or try and build a calculation in a new way; for example, not allowing the use of SUM. See what tools you can come up with to tackle the problem in a new way.
Tip of the Week highlight – Checking formulas in the formula bar
This month’s highlight is taken from TOTW #198, and looks at how to check the result of a part of a formula easily, right from the formula bar. This is great for troubleshooting, or just to see how a complex formula is built up on review.
Simply select the part of the formula you want to evaluate, and then hit F9:
This is particularly instructive when used on array formulas:
And that’s all you need to do!
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #4:
Work collaboratively, share ownership, peer review.
All the research on spreadsheet errors shows that rates of error-making decrease only so much as users get more experienced and capable. Some errors will always remain, and a system of peer review is the only realistic way to tackle reducing them.
If you do a peer review, it’s important for the creator and reviewer(s) to discuss the likely risk areas and key goals of the spreadsheet beforehand. It’s important not to coach reviewers. However, don’t provide instructions beyond what the spreadsheet itself does, as not all users will have the benefit of your personal instruction.
David Lyford-Smith is the Technical Manager for 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.