Ever been guilty of using Excel as a fancy calculator? If so, you’d just be using a formula in Excel. If you entered a calculation such as =A1+A2 or =452*12, then that’s just a formula. If a formula is all you need, then that’s fine. But Excel can do so much more!
You may hear the two words function and formula used interchangeably, but they’re not technically the same. So, what’s the difference? A formula is an expression that uses cell references or hard-coded numbers to calculate the value of a cell. Sometimes a simple formula is all you need to get the right answer, but you can do so much more using functions.
A function is a predefined formula already available in Excel. Functions streamline the process of creating a calculation. To date, Excel has more than 500 functions and on Microsoft 365, more functions are added all the time, such as XLOOKUP, IFS, LET and LAMBDA to name a few. Functions can do complex calculations that would be very time-consuming to build manually. For example, if you wanted to add up a range of cells without using a function, you’d need to write something like: =A1+A2+A3+A4+A5 instead of =SUM(A1:A5). Now, for five cells, writing it manually isn’t such a big deal. But what if you’re adding a range of hundreds of cells? Or thousands? As you can see, functions make things a lot easier and are basically the whole point of using Excel.
So in summary, a formula is any calculation in Excel, but a function is a pre-defined calculation.
=A1/A2 is just a formula
=MAX(A1:B20) is a formula containing a function.
Mastering the use of functions in formulas is a key skill in learning financial modelling. But remember! There’s a lot more to being a good financial modeler than simply knowing lots and lots of Excel functions. But the more functions you know, the more likely you are to choose the one that’s most appropriate for the job at hand. As with many things in life, there are usually several ways to achieve the same result, but the best option is the one that’s the clearest and easiest for others to understand, as well as the simplest to audit.
Extract from Chapter 7, Financial Modeling in Excel for Dummies by Danielle Stein Fairhurst.