With so many available and many with descriptions that often seem like another language, is it any wonder?
As a professional trainer in Microsoft Office programs, in this article I’ll answer the common questions I get asked about Excel functions and share those you really do need to know if your job involves analyzing data or working with spreadsheets.
So, let’s get started …The first question I am regularly asked is….
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.
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.
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.
Often functions are not utilized because either:
With over 450 functions, Microsoft Excel has categorized functions that perform different actions.
Common Functions
Some of the most commonly used functions that every user should know about are available by accessing the drop arrow beside AutoSum on the Home Tab in Excel in the Editing Grouping.
From here you can easily access the SUM (add) and AVERAGE functions along with the Count function to count the amount of numbers in a range of cells. These would all be considered basic or foundational functions EVERYONE who uses Excel should know.
Remedial or Text Functions
In business today so many Excel users need to clean up data exported from other programs and then analyze it to enable decision-making. As a result, some remedial functions to tidy up text are valuable to know. Functions such as Trim, Concatenate, and Proper are must-know functions if you are regularly analyzing data.
Logical and Lookup Functions
Once data is cleaned up, there are additional functions that every business user of Excel should be across including a range of logical functions such as IF (where a cell is tested, and a result returned depending on whether the test proves to be true or false) as well as understanding common lookup functions such as VLOOKUP and the XLOOKUP available only in Microsoft 365’s version of Excel.
Whilst users may not use any or all of these every day, understanding them is paramount to being able to recognize what is being calculated in a spreadsheet and to be able to troubleshoot the function for quality assurance and to confirm the validity of data.
The only way to learn to use the functions you need to know in Excel in business today is to make a start.
Start at the beginning and build your skills and knowledge up or fill in the gaps by being inquisitive and curious about the result you want to achieve and exploring Microsoft’s Help resources.
Like anything new, take it one step at a time. It isn’t a competition to see who can create the most complex function, just focus on growing your skills so that today you know just that little bit more about Excel than you did yesterday! I promise, doing so will make your life with spreadsheets so much easier!
Want help? Check out my 14 MUST Know Microsoft Excel Functions for Business course with Michael Management.