ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #373 - The twelve formulas of Christmas

Author: David Lyford-Smith

Published: 17 Dec 2020

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! A very happy Christmas to you and yours – hope you are staying safe and enjoying a smaller but merrier festive period.

This week we just have a light Tip covering a Basic User level topic: If you want to look at improving your spreadsheet knowledge, what are the first twelve formulas you should master?

On the first day of Excel…

1 - SUM

We have to start with SUM – the most used formula in all of Excel. SUM is the quickest, easiest, and best way of adding up any range of numbers. It’s simple and can even be written automatically with the keyboard shortcut Alt =.

Excel Screenshot 1

Learn more in TOTW #280.

2 – COUNT

Also covered in TOTW #280, the counterpart to SUM is the simple COUNT function.  Where SUM adds up numbers, COUNT counts how many there are.
excel screenshot 2

This function is equally simple, but also vital for tasks as varied as sizing up data, checking if a data set has lost any items, and more.

3 – AVERAGE

The final part of the basic arithmetic trifecta, AVERAGE calculates the mean of the data.

Excel screenshot 3

You can read more about AVERAGE in TOTW #176.

4 – MAX / MIN

Ok, technically cheating here with two functions, but these are so intertwined that I think it’s fair to group them together.  These functions find the maximum and minimum value in a range, respectively.

Excel screenshot 4

These two are vital for identifying outliers, best prices, earliest dates, and more. Learn more with TOTW #334.

5 – TODAY

Any time you are working with dates in Excel – say, for example, counting down the days until Christmas – use the TODAY function to automatically and dynamically fill in the present date, updated each time you open or use the workbook.

Excel screenshot 5

The formula at the bottom is just a straight subtraction, by the way.  Learn more with TOTW #208.

6 – CONCATENATE, &

Two ways of achieving the exact same thing here – conjoining text from multiple cells into one.  You can go the formula route with CONCATENATE, or just use the text-join operator &.
Excel screenshot 6

Building street addresses or email addresses from parts, inserting live values into text, and more besides are based on the use of these formulas.  TOTW #259 has the lowdown.

…Six Formulas Calculating

Moving on to some equally essential, but slightly more complex formulas…

7 – IF

The quintessential logical function, IF lets Excel pick a different output based on a simple true/false test.

Excel screenshot 7

This is a function with a bit more to it, so check out TOTW #273 for a full guide.

8 – A lookup function

Another semi-cop out answer from me here, but which lookup function you learn depends a lot on what your situation is.  All these functions can pull a value from a list based on some kind of label, but differ in the details.  VLOOKUP is the most commonly written (if clunky); INDEX MATCH is more flexible but more difficult to write; and XLOOKUP is the best option but only available in Microsoft 365 for now.  Whichever you learn, these are vital tools for any Excel user.

Excel screenshot 8

The relevant past Tips are #168, #201, and #337.

9 – SUMIFS

Very closely related to the lookup functions, SUMIFS does the job of adding up all the items with the same label.

Excel screenshot 9

To learn more, including why I recommend always using SUMIFS and never using SUMIF, read TOTW #316.

10 – EDATE

When creating lists of dates, one of the most common questions is “how can I make a list of the same date every month / every year”?  Because months are irregular in length, this can be a tricky proposition.  But the EDATE function makes it easy.

Excel screenshot 10

Read more in TOTW #238.

11 – LEFT, RIGHT

Another inextricable pair, LEFT and RIGHT can extract part of a piece of text – useful when you have a prefix or suffix that you need to list separately.
Excel screenshot 11

Both of these and more were covered in TOTW #341.

12 – Dynamic arrays

Finally, as we look to the future, a newer subject – currently only in Office 365 – but one that has the potential to be transformative to how Excel works.  Dynamic arrays are formulas which spread over more than one cell and grow and shrink as needed.

Excel screenshot 12

For a quick starter guide, check out TOTW #327.

So hopefully that’s a good list of some Excel knowledge to gift to yourself this holiday season!  All the best to you and yours, and see you next week.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250