What are Excel Functions and which ones do I NEED to know?

What’s the difference between a formula and a function in Excel?

Often people use the words formula or function interchangeably, but they mean different things.

A formula is considered a calculation created by an Excel user, for example, =C2+B2.

Whereas a function is an inbuilt Excel feature that enables users of Excel to perform calculations.  Some functions are considered quite simple and common, such as the SUM function which enables users to quickly add a range of cells together to produce a total, through to more complex functions that can look up lists of data and return values that match certain criteria.

Put simply:

A formula is a user-created calculation.

A function is an inbuilt Excel calculation.

How many functions are there in Microsoft Excel?

There are over 450 functions in Microsoft Excel.

They vary from simplistic to complex financial, statistical, engineering and database functions. Many of which MOST users of Excel will never use or never need to know so no need to set yourself a task to learn all of them, just focus on those that will make your work with Excel less stressful.

Do you need to use functions?

You don’t need to use functions at all…. unless, of course, you want to reduce risk and make less work for yourself when using Microsoft Excel, which I’m guessing is probably what you would like to do!

Functions, particularly the simple ones, take all the hard work out of calculations that may require multiple steps or actions if they were undertaken as a user-created formula.

For example:

Let’s say we have a list of 800 sales invoices that needed to be added together to find out the total value of those 800 invoices.

If we used a formula (user created) it would require manual selection of each cell containing the invoice total and would look something like this:

=C2+C3+C4+C5+C6….. all the way through to C800

Not only would this be time-consuming but get distracted along the way and you can easily miss a row meaning an incorrect total and you may never know!

Using Excel’s inbuilt function SUM (which means add) we can simply click in the cell where we want the answer to appear – for example, C801 and click the Autosum button/icon on the Home tab.  From here Excel offers a highlighted range of cells for us to confirm the data selected is the data we want the function to provide a total of.

This makes it much easier to calculate the total value of the invoices and minimizes the risk of missing a row of data which can easily happen if creating a manual formula.

Why aren’t functions used more?

Often functions are not utilized because either:

1. Users do not know what function will perform the calculation they need or
2. Users do not understand the “layout”, known as a syntax of a function so they don’t use it.