Excel has hundreds of functions available.

From the very simple, such as the TODAY() function which returns the current system date, to more complicated functions such as YIELDDISC(), which calculates the annual yield for a discounted security.

Most functions, even the seemingly complex ones, can be understood by reference to Excel help, which includes plenty of examples. Accordingly, in this article we are going to consider some functions whose use might not be immediately obvious.

Incidentally, it’s worth pointing out that when you use an Excel function, you are often giving Excel a set of values that are used to calculate an answer, with no obvious ‘workings’ as to how this is done. For this reason, it’s important to make sure that you understand what inputs a function needs and carefully test the results.

Sometimes, it is possible to cross-check function results against each other. This is obviously not a complete guarantee that the functions are working as you expect but can highlight errors.

Let’s look at four superpower functions that I’m sure will increase your personal as well as your organisation’s efficiency.

MOD()

Often overlooked, this handy function returns the remainder of a division. This is particularly useful if you are planning your cash flow for the following year with recurring payments that go out monthly, quarterly and annually.

OFFSET()

Use this function to offset a reference to a cell by a number of rows and columns, and extract information from your spreadsheet into a separate column. Because it changes the address of the cell that a formula refers to, it makes it difficult to trace cell precedents and dependents directly, so be careful when applying this in Excel.

INDIRECT()

Save time by using this function to manipulate and create cell references. For example, if you have multiple sections of the business that you need to report on, INDIRECT() will create a single report and make it possible to choose which figures that report should include.

CHOOSE()

A simple function with a hidden superpower. On its own CHOOSE() selects an item from a list of items, but this also works in tandem with a VLOOKUP(), allowing the extra capability of looking from right to left.

AAT students and professional members can access a wide range of Excel resources and training here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.