Five things I learned at the Global Excel Summit 2022

How to change the layout of your pivot table – one of many things I learned at the Global Excel Summit

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.

Continue reading “Five things I learned at the Global Excel Summit 2022”

The five most useful new Excel functions for finance teams

Table showing new Excel function FILTER

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.

Continue reading “The five most useful new Excel functions for finance teams”

The most useful power query functions for accountants

Here are the five most useful power query functions for accountants, in my personal opinion:

  • Extract
  • Conditional Columns
  • Merge Queries
  • Data from Folder
  • Unpivot

It is, of course, impossible to pick just five. However, the point of this post is to illustrate that the power query functions I use the most are actually the simple ones. They are still enormous timesavers. Let’s have a look.

Continue reading “The most useful power query functions for accountants”

Accounting for a property purchase in Xero – partly paid by direct grant to vendor

Sometimes accounting is like a jigsaw puzzle…

I had a fun challenge recently – accounting for a property purchase in Xero where a third party had paid grant funding directly to the vendor. As it took me a while to work it out, and I couldn’t find what I was looking for online, I thought I’d set it out here.

Some of this advice also applies if you need to account for any kind of purchase where a third party has paid the supplier directly. Or if you are accounting for a property purchase in Xero where you have already paid a deposit but only received an invoice at completion. This post assumes that you have reasonable prior knowledge of accounting for invoices, bills, receipts and payments in Xero.

Continue reading “Accounting for a property purchase in Xero – partly paid by direct grant to vendor”

Power Query for accountants – enhance your trial balance

Power Query has been out for over a decade and yet the majority of accountants I meet still seem unaware of what it can do for them.

A possible issue is that it is usually presented in the context of Power BI, and analysing Big Data. And the type of people who like to talk about that also like to talk about other scary things such as VBA and SQL. There aren’t many training resources or articles focussing on Power Query for accountants more generally.

Perhaps we all need a lightbulb moment. Photo by Zach Lucero on Unsplash
Continue reading “Power Query for accountants – enhance your trial balance”

Why aren’t you using Excel Tables?

Cells A2 – F8 show an Excel Table. The data in rows 10-12 is in a tabular format but is not a Table.

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.

Here is a really good one, for example.

However, in this post, I thought I would explore this from a different angle, to try to understand people’s reluctance to use this amazingly useful tool.

Continue reading “Why aren’t you using Excel Tables?”