While both the terms – Formula and Function – are often used interchangeably by Excel users, there are some differences in these two.
While most of the people don’t really care about this, you may want to know the difference (especially if you’re preparing for an interview).
Before I get into the nuances of formula versus function in Excel, let me give you a very simple difference.
Function Vs Formula in Excel
A function is something that is already built-in Excel – such as SUM or COUNT or AVERAGE or VLOOKUP. When you want to use these functions, you need to use these using the proper syntax as decided by Excel.
A formula, on the other hand, is something the user creates. It may or may not involve using one or more functions. For example, below is a formula that simply adds two numbers.
This doesn’t involve any function (rather it uses cell references and an addition operator), but it’s still a formula as it has been created by the user.
Now, if I use the following instead, it would still be a formula as I am using the SUM function (which is in-built), but I (the user) am creating it to get a result.
So, in simple terms, you create formulas in Excel (that use cell references, operators, and functions).
Below is a table that shows the difference between a formula and a function
|It’s already a part of Excel and already has a pre-defined syntax and arguments It can take||It is created by the user and it can involve using one or more functions|
|A function uses parenthesis and takes the specified number of arguments||A formula always starts with an equal to sign (or a plus sign)|
|You can use multiple functions in a single formula||There can only be one formula in a cell in Excel|
|Operators (+ or – or *) can be used only in specific functions. You don’t use these in most functions||You can use operators as a part of the formula|
|You can not change a function||You can create your own formulas|
|A function, when used in Excel, would always be a part of a formula||A formula may or may not contain a function.|
|Example: =SUM(A1:A5)||Example: =A1+A2+A3+A4+A5|
Formula Vs Function Examples
I hope the above explains well enough the difference between a function and a formula.
But just for the sake of more clarity, here are some examples that will make it crystal clear:
=SUM(A1:A5) =VLOOKUP(A1,B1:C10,2,0) =COUNT(A1:A10)
Now, these are all examples that use a function (and function alone). In general terms, these would still be called a formula. The way you say it is that it’s a formula that uses the SUM function (or VLOOKUP function or COUNT function).
A formula may be any calculation that you do in Excel. You start the cell with an equal to sign and then can use a function or operators.
Below are all examples of formulas:
=SUM(A1:A5)*1.2 =AVERAGE(A1:A10)/10 =SUM(A1:A10)*COUNT(C1*C5)
I hope this helps make the distinction clear.
You may also like the following Excel tutorials: