I recently designed and delivered some Excel training for auditors. Specifically, it was Excel training for audit trainees who had just started at the firm.
I benefitted enormously at the start of my career when my employer arranged for all new audit trainees to have Excel training as part of our induction. Even though it was pretty basic, it established a good grounding in things like formula construction, absolute and relative cell references, and a few shortcuts.
If your pivot table defaults to Count instead of Sum, then you have inconsistent numerical data.
I found this out on a webinar about five years ago, and it still seems to be a mostly unknown feature.
And yes, I call it a feature, not a bug, because it is ultimately helping you.
What it is doing is giving you an immediate message that your numerical data has gaps, or perhaps has numbers formatted as text, or other errors. If you ignore this, you might produce inaccurate analysis.
So – if your pivot table defaults to Count, don’t do what I used to do, which was to tut and manually change it to Sum.
Last week I “went” to the Global Excel Summit. This is a three day event featuring many of the superstars of Excel, eg Leila Gharani, Chandoo, Wyn Hopkins, Oz du Soleil etc etc. It was virtual this year, but as I’d never been before I didn’t know what to expect anyway.
I learned way more than five things, of course, but here are my biggest takeaways.
This post sets out the five most useful new Excel functions for finance teams, based on my experience. By “new” I mean available in 2019 or later versions of Excel (including Office 365).
Many of us are self-taught in Excel, and it can be hard to keep up with the changes. In the past few years there have been loads of new Excel functions that replace and add to existing ones that you might be familiar with.
I attended an excellent livestream last week about Excel dashboards, presented by the legendary Chandoo. In his tutorial, he built a KPI dashboard in Excel in around an hour. As well as learning lots of useful tips, it also made me think about the key skills (Excel and otherwise) that you need to build a great dashboard.
Excel Tables (capital T) have been part of Excel since the 2007 edition. Given it is now well into adolescence, why is it still common to see spreadsheets holding and calculating data but not using Tables?
A quick internet search will reveal tonnes of articles about why you should use Excel tables.
What do you need to learn to be “good at Excel”? What Excel training should be mandatory for finance teams?
I really rate the ICAEW’s spreadsheet competency framework as a tool to use to diagnose where you and your team are at and where you should be. It breaks down users into four types – Basic, General, Creator and Developer – and allocates the skills that each level should have.
This is much more helpful to me than users saying that they are “intermediate” or “advanced” users. When I recruit for roles I often use the framework in the job description.