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?”

Excel design tips for non-designers

I have the visual sense of a blind goat, but over the years I have picked up a number of Excel design tips which I apply to any spreadsheet that someone else has to look at.

This post sets out ten areas which you can look at to make your spreadsheets cleaner and more professional.

I’ve written this post on the assumption that you are reading spreadsheets online, but most of these Excel design tips also work for printed documents.

1. Turn off Gridlines

The first tip is the easiest – turn off gridlines. Gridlines are the lines marking the rows and columns.

With gridlines
Continue reading “Excel design tips for non-designers”