ICAEW Excel tips: Excel around the world
Last month, I was fortunate enough to travel to South-East Asia with the ICAEW, representing the IT Faculty and the Institute in Kuala Lumpur, Singapore, Hanoi, and Ho Chi Minh City. While the ten-day trip was primarily about our work in the area of blockchain and digitalisation of tax systems, I also managed to fit in some time to talk spreadsheets.
This started with a pre-conference session for some delegates of the MIA Conference, which attracts thousands of accountants and finance professionals from across the ASEAN region. I also spoke to students at Sunway University and to Vietnamese students who were studying for ICAEW’s CFAB qualification.
In each session, I spoke about the Twenty Principles for Good Spreadsheet Practice and the process that led to their creation.
The problems of spreadsheet risk and poor practice are universal, and in particular I think there’s a lot of value when speaking to students and others early on in their careers about the strengths and risks of Excel, as starting out on the right foot can pay huge dividends over the course of a career.
The other thing that struck me was that, even on the other side of the world (more or less), the desire for Excel knowledge felt very familiar. Likewise, the same classics – conditional formatting, PivotTables, and lookup / IF formulas – were still generating the most buzz.
PivotTables, in particular, were the “wow moment” for the student sessions I hosted, and I think really showed the students why spreadsheets are the tool of choice for accountants who need to do ad hoc data analysis.
While the qualifications, experiences, and economies that we work in may differ, I think it’s reassuring that some parts of the accountant experience appear to be universal. Getting the right Excel approach sooner rather than later can be transformative to your career in the longer run – no matter where you’re from.
Tip of the Week highlight – CODE, CHAR, and line breaks
Special characters can sometimes be a pain to deal with in Excel. They can be difficult to insert, and difficult to work within formulas; for example, if you need to use SUBSTITUTE to remove something such as a currency symbol. Two useful formulas in this area are CODE and CHAR.
CODE can be pointed at a cell with a special character in it, and will return a number that shows you that character’s representation in your computer’s operating system (for common characters). For example, CODE(“A”) is 65 in Windows, and CODE(“£”) is 163. CHAR is the inverse: it can take one of those reference numbers and spit out the associated character.
If you are dealing with a more exotic character, later versions of Excel have access to UNICODE and UNICHAR, which can analyse almost any symbol (and yes, that includes emoji).
One particularly useful character to know is CHAR(10) (in Windows). This is the line break character, and can be used to insert line breaks into formulas:
You do need ‘Wrap Text’ enabled to make this work, however. You can also insert line breaks manually by using Alt and Enter.
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #17:
Have a system of backup and version control, which should be applied consistently within an organisation.
One of the most heartbreaking scenarios in Excel is having the program crash when it’s been a long while since your last save, losing hours of work.
While Excel’s AutoRecover will do its best, there’s no guarantee that your work will come back. It’s vital to save early and save often. For critical workbooks, look into what options your organisation has to help with secondary backups in case the file is corrupted or inadvertently deleted or otherwise lost.
Poor version control can swallow up hours of productive time, trying to track and merge divergent versions of workbooks. Wherever relevant, include a version number in the filename of a workbook right from the start and instil strict habits in your organisation that any substantial changes must be done in a separately numbered version.
Version names such as FINAL are unhelpful and often wrong, so stick to simple numbering. And of course, version control only works if all parties working on a workbook are using the same standards, so make sure to have a conversation with others about how different versions will be demarcated.
Finally, one little trick for version numbers: use filenames such as File v01 or File v001 to make sure that the versions go in order when sorted alphabetically, even when the version number reaches double or triple-digits.
David Lyford-Smith is the 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 at this link.