Excel tips series

5 simple Excel formulas every accountant should know

Dave Sellick Founder, Sidgrove

Excel is still used regularly by accountants yet many are self taught and are missing out on lots of useful tricks!

To help us learn some more useful Excel functions, Dave Sellick will be giving us a great "Excel Top Tip" in each issue so we can learn how to really use Excel well. Dave is a super user of Excel and believes it is an absolute game changer and a central part of every accountant's toolkit - even in 2020.

In this issue, Dave provides an introduction to the SUMPRODUCT function in Excel which is a really useful and essential tool for all accountants.

5 Simple Excel Formulas Every Accountant Should Know

There is a lot of content out there on Excel and the 100 different formulae we should all be using

  • ❗However, not much of this content is specifically geared towards an accountancy workflow
  • Therefore content around Excel can be overwhelming and it’s easy to be distracted away from the really vital formulae and functions of Excel that are most relevant to accountants

Below 👇🏼 I’ll look at 5 formulae that I think should be on every accountant’s radar; they are:

  • XLOOKUP/VLOOKUP
  • IFERROR
  • SUMIFS
  • IF
  • EOMONTH

👉🏼 Let’s take a look at these in more detail and with some video walkthroughs

✨XLOOKUP/VLOOKUP

What does it do?

Lookups look up a value in a column (or row if you’re using XLOOKUP) based on a value in another column meeting criteria that you have set

E.g: If you have a whole bunch of account codes and want to get the names of all those codes from a data set that has all account codes and names listed (AKA the Chart of Accounts)

  • You could look them up manually OR
  • You could return them in an instant automatically by using the Vlookup or XLOOKUP Formula

How does it work?

Vlookup:

  1. Type ‘=VLOOKUP(’ in the first cell that you want to return a value in
  2. Select the criteria you want to test for
  3. Usually this will involve either
  4. Selecting the cell within which the criteria sits
  5. Typing either the numerical value of the text value (text values need to be between two speech marks “x”)

3. Select and ‘absolute’ the data range for the lookup

  • Start with the first value in the column including the criteria and end with the bottom value in the column you are looking into
  • Absolute this range (so that it does not move as you pull down your vlookup formula) by then pressing F4 (if you’re using a PC) or CMD + T (if you’re using a Mac)

4. Input the column number in the range to return

  • Count the number of columns across from the criteria column that the lookup column is -
  • This needs to include the criteria column, so count across and then add 1

5. Add a ‘0’ or ‘1’ to define whether you want an exact or approximate match

Invariably you will want to input ‘0’ here

Close off the brackets and press enter

6. Close the Brackets & Press Enter

7. Pull down the formula to return multiple values

🎥VIDEO: VLOOKUP WALKTHROUGH

Xlookup:

1. Type ‘=XLOOKUP(’ in the first cell that you want to return a value in 2. Select the criteria you want to test for

  • Same as Vlookup process as above👆🏼

3. Select and ‘absolute’ the vertical or horizontal lookup dataset

  • This is the dataset that you want to locate the criteria you have set in part 2)
  • Absolute this range (so that it does not move as you pull down your vlookup formula) by then pressing F4 (if you’re using a PC) or CMD + T (if you’re using a Mac)

4. Select and ‘absolute’ the vertical or horizontal target dataset

  • This is the dataset from which you want to return a value
  • Absolute this data in the same way as part 3 👆🏼

5. Optional: add a value to return if no value is found

  • This is not possible with a VLOOKUP
  • It may be a good option to return a ‘0’ if no value if not found

6. Close Brackets and Press Enter

🎥VIDEO: XLOOKUP WALKTHROUGH:

✨IFERROR

We’ve all been there - formulas sending us various types of ‘computer says no’ errors in Excel

Good examples include:

  • When a Vlookup or another lookup formula can’t find a match (following on from above) the xlookup has its own solution for this though!
  • When you’re calculating a ratio across periods and there are instances where the value you’re dividing by is 0 - this is probably the most common situation in my workflow

What does it do?

IFERROR will allow us to choose what we would like to return if the given formula returns an error

✅ Keeps Excel reports looking clean

✅ Prevents situations where an error can cause knock-on errors for other formulae (e.g. SUMPRODUCT, SUM etc) or calculations

How does it work?

1. Select a formula that is generating errors

2. Type ‘IFERROR(‘ at the beginning of the formula

3. Type ‘,^insert what you want to replace with’ at the end

  • Example (if replacing with a zero): =IFERROR(N22/N11,0)

💡Tip: I tend to find that replacing with ‘0’ can work with really well

👉🏼You can then combine with a formatting hack to hide all the zeros - this will ensure that it doesn’t interfere with any other formulae that don’t like non-numerical data in the dataset

🎥VIDEO: IFERROR WALKTHROUGH:

✨SUMIFS

This is a key one for pulling together summarised report data from larger more granular datasets (e.g. TB or GL datasets)

What does it do?

The use case is vast and is relevant for any situation where you need to sum data based on a set of criteria

  • I.e. SUM the data, IF certain criteria are met

It’s an alternative to SUMPRODUCT, where you do not need to reference a large dataset in the formula and can define the relevant lookup columns 💡Tip: SUMIF allows you to do the same thing as SUMIFS but only for one criterion 👉🏼 Always use SUMIFS so that if you ever need to add in additional criteria in the future you can do this without completely re-writing the formula 👉🏼 SUMIF is a historical Excel formula and so still exist because some versions of Excel (2007 and before) don’t support SUMIFS; but I don’t advocate writing formula with that level of backwards compatibility!

How does it work?

1. Type ‘=SUMIFS(‘ in the first cell you want to return a result in

2. Select and absolute the column of data within which you want to sum the values that meet one or more criteria

3. Select and absolute the column of data within you want to test the criteria against

4. Insert the Criteria

This could be a cell reference, a specific text/numerical value OR..

A combination of these and a formula - e.g. “>=”&C10

  • This example would return a sum of values that are greater than or equal to the value in cell C10

5. Repeat part 3) & 4) if necessary until all criteria data columns and conditions have been denoted

🎥VIDEO: SUMIFS (SINGLE CRITERIA) WALKTHROUGH

🎥VIDEO: SUMIFS (MULTIPLE CRITERIA) WALKTHROUGH

✨IF

If you’re looking to develop the functionality to calculate different outcomes based on changes in input variables, then the IF formula is for you

Combing a series of IF formulas with other Excel functions can open up the door to developing almost any functionality you can think of

What does it do?

  • IF allows you to calculate different outcomes based on whether the outcome of an initial question (i.e. formula) is true or false
  • Therefore, by combining a number of IF statements, you can develop a formula that returns a different result for any number of scenarios that you want to specify

How does it work?

1. Type ‘=IF(‘ into the first cell where you want to return a value

2. Write the question (i.e. formula) from which you want to return different outcomes

  • This must have a TRUE or FALSE outcome
  • E.g. ‘C51=1’

3. Write the value or formula that you want to return/calculate if the original formula is TRUE

4. Write the value or formula that you want to return/calculate if the original formula is FALSE

OR

5. ‘Nest’ another IF formula (or multiple) to ask another ‘question’, if the first ‘question’ result is FALSE

Like this:

🎥VIDEO: IF WALKTHROUGH - Creating Dynamic Forecast Revenue Scenarios

✨EOMONTH

  • As accountants, we’re always producing reports across time periods
  • Fundamental to those reports are the period/month headers
  • EOMONTH - or in long-form ‘End Of MONTH’, can allow you to:

✅ Quickly create Month Headers for a reports

✅ Create dynamic month headers that adjust depending on what period you’re reporting on

👉🏼Great for a Last 12 Month Report or a 12 Month forward looking projection by month

What does it do?

  • Essentially it does what it says 😊
  • It returns the end of month date value of the date value that you reference
  • However, importantly, it lets you then extend this end of month value by a chosen number of months forward or backwards
  • Using the end of month date of a period (or the first day, by moving back one month and adding 1 to the formula) is a great way to ensure consistency to the period headers throughout a report= 👉🏼Once you’ve got consistency in the headers you can then perform lookups into these headers to return results; therefore it’s vital for pulling together monthly insights from more granular datasets

How does it work?

1. Type ‘=EOMONTH(‘ into header cell where you want to return a preceding or proceeding month header

2. Select the cell from which you want to drive the preceding/proceeding month header

3. Type the number of months that you want to go forward or back

👉🏼 This will usually be 1 or -1 (or 0/-2 if you want to drive the first day in the month, rather than the last)

4. Close the brackets and press enter, OR OPTIONAL: add 1 to give the first day of the next month 5. Drag or copy the formula across to the other headers as required

❗ Note: I’ve used the first day of the month alternative option in the example shown. This ensures that I have a consistent day of the month (i.e. the first) rather than it fluctuating between 29-31. However, it doesn't matter as long as the approach is consistent throughout the report/model

🎥VIDEO: EOMONTH WALKTHROUGH

Here are all the videos, just in case you miss them in the article:

VLOOKUP: https://vimeo.com/483094727/8fc4a58175 XLOOKUP: https://vimeo.com/483094421/0b1772e824 IFERROR: https://vimeo.com/483094182/fe38c3315e SUMIFS PT1: https://vimeo.com/483093970/5da385c334 SUMIFS PT2: https://vimeo.com/483093706/0a99f9cdbb IF: https://vimeo.com/483093283/95b432d463 EOMONTH: https://vimeo.com/483093061/d735a25e97

Thanks for reading, I'll be ready with more tips for you next time 🙌🏼

A bit about Dave

  • I am the founder of a purpose led accounting practice & consultancy based in London called Sidgrove, working exclusively with founder-owned businesses & scaling startups. I initially trained in audit with PwC in banking, before moving into industry for around 6 years with numerous scaling startups; I’ve been running Sidgrove full time for just over a year.
  • I’ve spent more hours than is probably healthy🤦‍♂️😂 working with Excel 📊as an Accountant over the last 12 years in both industry and practice. During that time I’ve continued to push the boundaries 😇 of how it can be used to deliver efficiencies and superior insights to my clients.
  • My Excel journey really took off 🚀during my time as a Head of Finance of various startup businesses, where efficiency🛠, quality commercial insights 📈& creative presentation🌈 was imperative.
  • I’ve now taken all this experience into my practice, Sidgrove, 💪🏼where amongst a wide suite of software and hardware tools, it is still my most prized day-to-day tool 🏆and canvas ✒.
  • I believe Excel is still an absolute game changer 💯🚀and it needs to stay centre stage in the Accountant’s Toolset, even in 2020.
  • However, I know the full functionally of Excel can seem daunting many accountants 😨so I am starting a top tips series to help you get the best out of Excel’s most relevant functionality for Accountancy in the modern-day 😊.

Read next article

WorldFirst e-commerce