Many great tutorial sites present a long list of functions to learn and are very useful references, but for beginners learning how to use Excel I'd argue that if you memorise just these five core functions you'll be able to perform the bulk of most common tasks.
My top 5 recommendations are:
SUM - used for math operations
VLOOKUP - used to match one list of values against another
COUNTIF - counts the number of values in a range of cells that meet some criteria
IF - checks whether something is true or false
CLEAN and TRIM - cleans up your data
The most commonly used Excel function is SUM, which in it's basic form performs simple addition on a number of cells. It's used to add things up. You may be familiar with it already but it's worth exploring some of it's less obvious capabilities.
SUM can be used to add up the values in seperate cells. To do this type
=SUM( into the formula bar or directly into a cell. You'll see a tool
tip called the arguement list pop up showing
=SUM(number1, number2. Click on the first cell you want to sum, type a comma, then click on the next
cell you want to add to the sum. You can repeat this for as many cells as needed. For instance to sum the values in cells A1, B1 and C1
the formula would be
=SUM(A1, B1, C1).
As well as seperating the cells with a comma you can drag-select a range of cells, or type the range directly into the formula using a colon to seperate the first
cell from the last. For example to add up all the values from cell A1 to cell A10 we'd use
SUM can be used for operations other than addition. Any
math operator can be used inside a sum, for example to multiply A1 by A2 by B2 we'd use
Once you get the hang of it you can start using
nested functions. For instance to multiply B2 by the sum of A1 + A2 + A3 we'd use
=SUM(SUM(A1:A3) * B2).
One of the most useful Excel formulas is VLOOKUP, it's so commonly used that most regular Excel users have committed it to memory. It's used for matching one set of values against another, for example, here is a list of customers on the left and customers who made a purchase this month on the right. If we need to check which customers from our list made a purchase we use VLOOKUP:
The syntax of the formula - that is what to type and in which order - is:
lookup_value - the value which we want to find in the other list. In the example pictured we're using the ID field to match the two lists as each customer has a unique ID, so we've selected cell F2. You'll see it's highlighted in blue to match the blue text of the F2 reference in the formula.
table_array - the range of cells containing the other list, specifically starting with the column to match and covering the cells we want to return a value from. In this case we just want to return the ID so we've only selected column A. If we wanted to return the email address instead we'd select columns A to D.
col_index_num - how many columns to the right from the first column selected is the value we want to return? In this case just one as we only want the ID so we only selected column A , but if we wanted to return the email address instead we'd select columns A to D and the col_index_num would be 4 (A is one, B is two and so on).
range_lookup - this determines if the function will find a close match or an exact match. Enter FALSE for an exact match or TRUE for a loose match. The latter is rarely used.
The final formula in our example is
=VLOOKUP(F2,A:A,1,FALSE), which we can then drag down to the other cells to the right of the list.
COUNTIF and it's close relative SUMIF are used to count or sum cells in a range that meet a criteria that you can specify. This is very useful when you want to do something like count all the cells in a range that are greater than a certain threshold.
For example, to the right is some sales data. Lets assume we have a sales target of 100 or more sales for each department. To find out how many departments met their target we'd use a COUNTIF.
The arguments for COUNTIF (that is to say the information the formula requires) are 'range' - the cells to count and 'criteria' - what should the cells contain in order to be counted?
COUNTIF and SUMIF are slightly unusual in that they require the criteria argument to be in a string format. A string is a value inside of double quotes,
as you can see from the example
The >= operator means 'greater than or equal to'. You can use almost any
operator in a COUNTIF. For example to count the departments that didn't meet the target we'd use the 'less than' operator
SUMIF works in exactly the same way but sums the values rather than counts them. For example to sum all sales over 100 we'd use
Once you get the hang of COUNTIF and SUMIF you can take a look at their big brothers
COUNTIFS and SUMIFS which allow you
to specify multiple criteria.
The IF function (typically called an 'if statement') performs a logical test such as 'is this number greater than that one?' or 'is this date the same as this other one?' and returns either true or false, or other values of your choosing. Let's say we wanted to work out the discount on products we sell based on the sale price of the product:
If the product sells for 150 or less we'll apply a discount of 5% (cell B1), if it's more than that we'll apply a discount of 9% (cell B2).
To use the formula we first type
=IF( followed by the cell we want to check, in this case the item cost for the laptop in cell B5.
Next, we put in what we want to test, in this case if the item cost is less than or equal to 150:
After that we type a comma and say what we want to show when the answer to our test is true, in this case we want to multiply the item cost with
Discount A in cell B1:
In the pictured example I've used a dollar sign before B and 1, making them 'absolute cell references', that means even when we drag this formula down to other rows it will always point to cell B1.
Finally, we type another comma and enter what we want to return if our statement turns out to be false, in this case Discount B in cell
#5 CLEAN and TRIM
Ok, I'm cheating slightly as these are actually two functions but they're so commonly used together it's worth learning both.
CLEAN and TRIM are indispensible functions when dealing with 'real world' data, that is imperfect data that may contain trailing spaces, typos
of non-printable characters and so on. The other 4 functions (and almost every function going) relies on cell values being exactly what they appear to be.
When comparing cell A against cell B the slightest difference will cause Excel to see them as two seperate values. For example
Excel Pro is not the same as
Excel pro. It's very common in real life situations to receive data that hasn't been properly validated.
The clean function removes '
non-printable characters'. Don't worry too much about what they are, but if you find yourself in a situation where you know your formula should work but isn't then
try using CLEAN on the value you're testing. The syntax is
=CLEAN(A1), or whatever cell you're testing. To expand upon the IF function above we might do this:
The TRIM function simply removes leading and trailing spaces from a cell value or other text. Again this is a very common problem and can lead to all sorts of headaches if you don't realise that there is a space.
In the example pictured we can see the result of the clean and trim functions. In column A the length of the text in column B is shown. Although not obvious from appearances there are non-visible values in the text. In the first case on row 3 a special non-printable character had been introduced (this sometimes happens when moving data between different applications), in the second case on row 8 there are spaces before and after the text.
As mentioned it's quite common to use the two functions together e.g.
That's all folks
So there are my top 5 formulas for beginners. In my experience these functions make up probably 50 to 80 percent of the functions you'll need in real life. Once you gain confidence in using them you'll be in a good position to start using nested functions to perform complicated tasks and exploring the rest of the formula library. That's about 460 more functions than I've listed here, have fun with 'em!