Help and ideas for busy numbers people
I’ve had quite a few enquiries about Excel training for non-finance teams recently.
Which is really cool!
Although my background is finance, and I do predominantly train finance teams in Excel, I’m always delighted to get a request for something different.
Continue reading “Excel training for non-finance teams”
I first wrote this post about using Copilot with Excel less than a year ago and I thought it was worth updating.
It’s quite a challenge for trainers to train on Copilot because it changes every day and a lot depends on what sort of licence you have. And even that isn’t consistent, so if I write a post or training materials based on the assumption you can do a particular task with a particular licence, that may not be true the following week.
Continue reading “Using Copilot with Excel – what I train”
I always change these three Excel default settings when I’m setting up a new machine or profile.
Excel defaults to removing leading zeros. That is, if you type 078456 it becomes 78456.
If you want to keep any leading zeros, you have to type ‘078456. This tells Excel to treat your input as text.
You can (and probably should) change this setting. You can find this in File => Options => Data. Under “Automatic Data conversion”, deselect the first box (“Remove Leading Zeros and convert to a number”). See image below:
This isn’t just a techy change. There are lots of occasions where finance people might want to show zeros as part of a number.
For example, think about bank account and sort codes.
If you’re importing a payment file to or from a CSV file, you need the full string of digits.
Many accounting systems also have account codes that start with zeros. For this reason I often have account codes set to text when I’m building accounts models.
There are lots of settings for changing how your pivot tables look. If you have a particular look you prefer, you can change your defaults.
In the same File=>Options=>Data window pictured above, you have the option to “Make changes to the default layout of pivot tables”.
My particular changes are to switch to Tabular layout, and in the further options bit I untick “Autofit column widths on update”. But there are loads of other options you can change as well.
The Quick Access Toolbar is a feature of most Microsoft products. In Excel, the default items are not massively helpful to me – I use keyboard shortcuts for Save, Undo and Redo.
The location of the QAT is also not helpful to me, as you have to be able to recognise icons to use it effectively. So instead, I move it to under the Ribbon. Here is the before picture – I’ve highlighted with a red circle the button you need.
And here is the after picture:
Much easier to read!
There are tonnes of other ways you can customise your experience. For example, I wrote a blog post here about using Cell Styles. You can set up Templates and default Table styles too. However, these are all specific to workbooks, whereas the three Excel default settings I’ve highlighted above apply to your machine or profile.
Last year, I attended my first Global Excel Summit in person. The experience was so good I immediately booked for 2026 as soon as tickets were available.
As is now tradition, here’s my annual blog on what I learned. And yes, I’ve booked for 2027 already!
Continue reading “What I learned at the 2026 Global Excel Summit”Dates in Excel – the subject of many an internet meme. There’s a common perception that Excel is baffling when it comes to dates.
And this is actually a bit unfair, because it’s got quite a bit better in the last few years at not randomly inserting dates everywhere.
So I thought it would be useful to set out a few principles of how they behave and why. I am using UK date formats in this post.
Continue reading “Dates in Excel – how they work”
Excel is 40 this year, and I decided to mark the occasion by creating a zine.
Continue reading “Excel at 40 Zine!”In the video above (which is silent), I’ve used Excel’s camera tool to toggle between different versions of the same chart.
This post was inspired by Juliana Smith and I had enormous help from this article by Chandeep Chhabra to get to the solution.
Continue reading “Using the camera tool to toggle charts”
Last week I delivered a day’s Excel training in Cardiff for a medium-sized charity.
I designed this bespoke course for a group of finance and data folk who had already done my half day Introduction to power query course and had good Excel knowledge to begin with.
I always love training people who work together, because I know that there’s an increased chance that the learning will continue after the course.
I’ve posted before that I don’t have a standard “intermediate” or “advanced” Excel course. This is because clients have different needs, and different starting points. I don’t want to waste time showing you stuff you already know, or are never going to need.
But as an example of what could be included in a day’s bespoke Excel training, let’s look at last week’s Cardiff session. I had previously circulated a questionnaire to the delegates to understand what they already knew and what they wanted to find out more about.
We revised key building blocks like Tables, and concepts such as number vs text. We then went onto some upgrades. For example, we went through XLOOKUP and the many ways it’s better than VLOOKUP.
And we also covered lots and lots of new Excel content.
Some of this we did via brief examples – such as REGEX, Checkboxes and TRANSLATE. (It was interesting to see how it copes with Welsh!)
Dynamic arrays were completely new to everyone, so we spent a bit of time looking at FILTER, UNIQUE and GROUPBY, and also discussing what made dynamic arrays different.
We then finished with a session on dynamic charts, including some data visualisation tips.
Throughout the day I shared examples of useful shortcuts and navigation tools to make everyday work in Excel a little bit more efficient.
I can design and deliver a day of bespoke Excel training in Cardiff (or Newport) for £875 (£825 for not for profits). I’m based in Bristol so Cardiff is a really easy trip for me and I always enjoy spending time in Wales.
Please get in touch via my Contact page to discuss your needs in more detail.
Structuring data in spreadsheets is really important for producing good financial models and reports.
However, data designed for humans to read should look different from data designed for computers to read.
Continue reading “Structuring data in spreadsheets – for humans and computers”
Does GROUPBY replace pivot tables?
The new functions GROUPBY and PIVOTBY are now available in the current channel of Microsoft Excel, and I’ve been playing with them to try to answer this question.
My conclusion is that it isn’t a simple yes/no answer. What is more useful to think about is “when” or “how” GROUPBY should be used instead of a pivot table – and vice versa.
Continue reading “Does Groupby replace pivot tables?”