Let’s get back to basics today in #formulafriday and discuss something I am asked quite frequently about……

What is the difference between a Formula and a Function in Excel?

If you use Excel as a rather large calculator then you have probably used it to do something like this- ( the result being 23)

=1+2+4+6+10

or

=A1+A2+A3+A4+A5

What you have done in Excel right there in the example above is used a Formula.  If you find this useful, then that is great, but Excel is way more powerful and can speed up simple calculations (like the one above) and very complex calculations.  It contains to date aproximatley 400+ built in formulas for us to choose from, these are known as Functions.  Some of the most well used and well known are VLOOKUP, SUM, (which I have used in the above example) INDEX or MATCH.  As Microsoft release new versions of Excel, they add some and also they can retire some as well.

So to demonstrate the difference between the above Formula calculation as a Function in Excel it would look like this.

=SUM(A1:A5)

 

The terms Formula and Function can be used interchangeably and often are, and essentially they give the same result – that being an output.  A Function is pre-defined by Excel and a Formula is user created as we have seen in the example shown above.

There a few differences to differentiate between Functions and Formulas.

Functions

1. A Function is already built into the Excel software example SUM, AVERAGE,VLOOKUP
2. We can nest Functions with Formulas
3. Functions usually have a built in wizard to help you step through the numerous stages of creating them
4. Functions can be used simplify more complex mathematics

Formulas

1. A formula is typed by the user directly into the formula bar in Exel
2. A formula cannot be nested
3. A formula does not have  built in wizard.  The user has to manually type the formula
4. A formula is a simple mathematical calculation

Where Can I Find All Of Excel’s Functions?

Functions are grouped together in their functional categories within the Functions Library in Excel 2013 onwards if you want to take a look at all of the available functions within a cateogory then use the drop down arrow.

Take a look around all of the functions that are available.

In summary an Excel Formula is any calculation in Excel but a Function is a pre-defined calculation.

 

• Create A Micro Chart In Excel
• How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
• Create A Timestamp In Excel With Formulas
• Use TRIM for a speedy data clean up

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.