Table of Contents
There is no doubt that Excel formulas are a huge reason why people turn to Excel for data analysis. The ability to create simple or complex calculations, helps you save time and energy.
A common question I hear when planning any Microsoft Excel course is “Which are the most useful Excel formulas we should learn?“.
Let’s go through a little introduction to some terminology in Excel and some of the most useful basic Excel formulas that you should have under your belt.
If you are new to Excel and not feeling ready to jump into these functions, try out our Formula Basics in Excel tutorial to get you started.
Formula vs Function
There are two main terms used when discussing the ability to create calculations in Excel: Formulas and Functions. The difference between the two boils down to how the user creates a calculation.
- Formula: A formula is a calculation where the user defines the calculation. E.g =A12+B12+C12
- Functions: A function however is a calcuation created from a prefined set of calculations found in Excel. This eliminates the need for you to “create” the entire calcuation yourself and provides a user-friendly name which identifies what the calculation is doing. E.g. =SUM(A12:C12)
Syntax
The term syntax is another one which appears in Excel when using Functions and essentially defines the layout or way in which a function must be entered for it to work.
The syntax of any function includes the equal sign (=) and is then followed by the name of the function, such as SUM, AVERAGE, IF, COUNT. It then identifies the range of cells to be included in the function, surrounded by brackets.
=SUM(A12:C12)
=AVERAGE(A12:C12)
SUM and SUMIF
Most people know about the amazing AutoSum button, but in case you don’t, you need to learn how to use this simple button to use the SUM formula.
SUM will allow you to add together a range of values. Check out our article via our blog site on how to use the SUM formula in more detail. Introduction to Functions in Excel.
SUMIF is a slight variation and allows you to add together a range of values based on specific criteria. Very useful in instances such as “I want to total together only John’s sales“. You can use “John” as the criteria so Excel only adds together those sales figures. Very handy!
COUNT and COUNTIF
Want to know how many records you have in your data set? The COUNT numbers function can be used to count the number of cells which contain a number based value. This will only work where one of the columns within your data contains a number value such as currency, a date, phone number etc.
You can check out our article via our blog site on how to create a COUNT formula. Introduction to Functions in Excel.
The COUNTIF function is again a slight variation where you can count values based on specific criteria. For example “How many leads do we have in VIC only?” Check out our article via our blog site on how to create a COUNTIF formula in Excel.
MIN and MAX
I always think these two sound like a kids cartoon title. But the MIN (minimum) and MAX (maximum) functions allow you to identify the lowest and highest values within a range of cells. Very useful for identifying the highest or lowest currency amount or date.
Again, you can check out our article via our blog site on how to use the MIN and MAX functions. Introduction to Functions in Excel.
IF statements
IF statements can sometimes strike a fear response with people. If you have ever seen an IF statement used in Excel it can sometimes make you wonder what on earth is going on. With so many brackets and commas and cell references all over the place they can seem daunting to use.
IF statements, however, are amazing for answering questions that would have a YES or NO answer. You can even use the IF statement to have Excel perform a calculation based on the answer.
Question with a Yes or No answer:
Did each staff member reach their sales target? Yes or no
Have Excel perform a calculation in response to either a Yes or No result:
If a staff member did reach the sales target, calculate their sales commission.
You can also create very complex IF statements.
If a student has a score between 81 and 100 give them an A grade. If their score is between 61 and 80 give them a B grade. If their score is between 41 and 60 give them a C grade etc.
So although an IF statement can seem daunting, they truly are a very useful tool to have available when working with large volumes of data.
VLOOKUP and HLOOKUP
The VLOOKUP and HLOOKUP functions allow you to look up a value from within a table of data and have Excel return the corresponding answer. I always say that this is one function where Excel can do the work for you. No longer will you need to go and lookup an answer or value needed in Excel – have Excel do that for you.
The VLOOKUP function is used when your data table is set up vertically with the lookup values vertically down a column and answers adjacent.
The HLOOKUP function is used when your data table is set up horizontally with the lookup values horizontally across a row and answers beneath it.
Check out this article on our blog site which shows how to create a Vlookup function in Excel. It’s also worth checking out the newest Lookup formula, XLOOKUP. This new feature in Excel will take things to a whole new level.
IFERROR
When creating a worksheet that is pre-populated with functions that you will use in the future, you can sometimes see errors appearing because you have cells without data. These cells will inevitably be populated as time passes and therefore your formulas will work and any errors will be replaced. However in the mean time, any temporary errors can be daunting or off-putting. I’ve had many people simply feel uneasy having any “errors” showing in their calculations even if it just because there is not yet data to populate those cells.
IFERROR provides a perfect tool to hide those errors until such time that you enter the relevant data and voila! Your calculations begin to function as expected.
You can read up on the IFERROR function here.
TODAY and NOW
Last but not least we have a few very useful date functions.
The TODAY function allows you to quickly add today’s date into your worksheet. This is a dynamic function which means that each time you open the worksheet, the date is automatically updated.
=TODAY()
Read more about the TODAY function here.
Take this an extra step further and use the NOW function to add the date and time into any cell.
=NOW()
That's a wrap!
That finishes up my list of the most useful Excel formulas you should learn to use.
Want to learn these face to face or via our online courses? Check out more information using the buttons below.