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 =.
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.
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.
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.
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.
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 &.
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.
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.
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.
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.
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.
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.
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.