This is a quick post to explain why EOMONTH doesn’t work with # references (dynamic ranges) and what to do about it.
Continue reading “EOMONTH doesn’t work with # references – help?”Why you should use Cell Styles in Excel
I’m a late adopter of Cell Styles in Excel. Before I started doing financial modelling and consulting work for other people, I didn’t really appreciate how useful they were.
I wish I’d learned them a lot earlier as they would have been helpful in earlier roles, too.
Continue reading “Why you should use Cell Styles in Excel”How to fix #SPILL errors in Excel Tables
This blog post sets out how to prevent or fix #SPILL errors in Excel Tables. Contrary to what Microsoft Help says, they might sometimes be fixable.
Continue reading “How to fix #SPILL errors in Excel Tables”Using flags in financial modelling
I was going to write a detailed guide about using flags in financial modelling. However, there’s already a really good one here, as part of the ICAEW’s financial modelling good practice guide(s).
So instead I’ll just write something more simple to explain why you might want to use flags in financial modelling. And add a specific point about dynamic arrays, which are a newish development in Excel.
Continue reading “Using flags in financial modelling”Ten things I learned at the Global Excel Summit 2024
I attended the Global Excel Summit 2024 online earlier this month, and in no particular order, here are ten things I learned.
1. Power Query at Chanel
The most inspiring session was given by two people who work for Chanel, who explained how they had used Power Query to reduce thousands of hours of processing time.
In the middle of a day that was focussed on the theoretical uses of AI, it was fantastic to hear about a tried and tested but scandalously underused technology being used to deliver real business change.
More generally their approach to business process improvement sounded fascinating and I’d love to see this become more widely known.
Continue reading “Ten things I learned at the Global Excel Summit 2024”Excel training for auditors
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.
Continue reading “Excel training for auditors”Monthly irrecoverable VAT adjustments with Xero and Power Query: Part 1.
I asked on LinkedIn the other day whether people would be interested in my method for posting monthly irrecoverable VAT adjustments and was pleasantly surprised by the responses.
So here goes.
Continue reading “Monthly irrecoverable VAT adjustments with Xero and Power Query: Part 1.”Why I don’t have a standard Intermediate Excel course
I don’t offer a standard Intermediate Excel course (or indeed, a standard Beginners course or a standard Advanced course).
There are a couple of reasons for this.
Firstly, I tailor all Excel training that I deliver to the needs of the course participants.
Continue reading “Why I don’t have a standard Intermediate Excel course”Why does my pivot table default to Count?
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.
Investigate your data carefully until you’ve found the inconsistencies and then try it again until it defaults to Sum. Here’s a link to some suggestions for converting text to numbers.
I love training finance teams in Excel and Power Query – have a look here at what I can do for you.
Processing timesheets with Power Query – what about the human factor?
On the face of it, processing timesheets with Power Query ought to be fairly straightforward. By “processing timesheets” I am primarily thinking about records of hours worked that feed into payroll or job costing calculations.
Continue reading “Processing timesheets with Power Query – what about the human factor?”