ICAEW Excel tips: Talking shop with 'Excel geeks'
David Lyford-Smith shared his passion for spreadsheets with two fellow ‘Excel geeks’ this month. If anything the conversations reminded him just why he loves the old reliable tool.
I had the pleasure last month of having not one, but two occasions to socialise with some fellow Excel geeks (and I do hope they will forgive me liberally using the term).
I often met other enthusiasts through my work for ICAEW, but here I had two meetings specifically to talk about Excel, spreadsheets, risk, and tips, with two very capable peers.
In each discussion, I found it interesting to compare stories. The first meeting was with an Excel trainer and adviser whom I hadn’t met before, whose personal story – of steadily increasing their Excel involvement until it entirely took over their role – was very reminiscent of my own.
We talked our shared love of Excel Tables, which can simplify and super-charge data management. We both felt that Microsoft had sold Tables short by badging them in the Ribbon mostly as a decorative feature (‘Format as Table’ is the command on the Home Ribbon), underselling their powerful abilities.
Tables can automatically carry forward formulas and formatting to new data, help ensure calculation columns are consistent, and can even help build more natural-reading functions due to the Structure Formulas system.
We also chatted a bit about keyboard shortcuts and other time-savers, and how we both found that these were the most enduringly popular topics with audiences.
Our own research at ICAEW shows that the most common reason for leaving our Excel Community is that people simply don’t have the time to make full use of the training and resources on offer.
It’s something we’re trying to assist, by working on new, easier-to-digest content and by focusing on time-saving tricks to help users make back the investment made in learning some new spreadsheet skills.
The second meeting I had was with a veteran financial modeller, with whom I share a passion for mathematics.
I’ve written in this column before about how sometimes a problem is best solved through a solid mathematical approach, rather than trying to bend Excel’s (admittedly large) suite of tools to every occasion. We spoke about tricky problems we had come across in our work and shared our own favourite hidden quirks within the program – which is the most useless Excel function, FACTDOUBLE or BAHTTEXT?
All in all, it was a good month to be reminded that I am not weird – or at least, I’m not alone – for finding spreadsheets fascinating and interesting. I still enjoy learning new things with them now as much as I did when I started – and long may it continue.
Tip of the Week highlight – Breaking links
This week’s highlight from the Excel Tip of the Week blog is based on #226.
Look familiar? This error box appears whenever you open a workbook that has links to other workbooks in it that Excel can’t update, usually because the source file has been moved, renamed, or deleted.
Many cases of this can be solved by breaking the links from the Data Ribbon (or from the pictured ‘Edit Links…’ button); this replaces the links with static values from whenever the source file was last available.
However, this often fails to break all links – because it only removes links contained in cell formulas. Links from several other locations are missed and can be quite persistent.
Those locations are:
- Named ranges (from Formulas => Name Manager)
- Data validation (from F5 => Special)
- Hidden worksheets
- Conditional formatting rules (from Home => Conditional Formatting => Manage Rules)
- Buttons with macros attached to them
- The titles of charts or chart elements
While this is quite a list, being thorough is strongly recommended – leaving dead links can make your workbook annoying to use at best, and at worst can leave the user unwittingly relying on out-of-date information.
Spreadsheet Competency of the Month
The Spreadsheet Competency Framework 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 third level is Creator. Creators use spreadsheets as a core element of their roles, and significantly use its functions and features. They often create spreadsheets from scratch and may create templates and workbooks for users at the first two levels.
Find out more about the definitions, and the list of skills needed for each, at the link above.
David Lyford-Smith is the technical manager in 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.