In modern times, the best computer skill you put on your resume might just be the one that secures you that coveted job. Excel skills, being a great example of such a skill-set, has become an essential requirement of many successful job resumes.
But the thing is, Excel functions and formulas are not that hard to learn! Mastering the first 5 functions listed here will take you less than 15 minutes, but will place you light-years ahead of other applicants.
Don’t know how to write a formula yet? Check out my free 3-day course on Excel formula basics. You’ll be ready to write your first formula within 15 minutes!
Here’s a list of 17 need-to-know functions that will make you stand out.
1. SUM – Add values
The SUM function adds up values. This is by far the most frequently used function in Excel. It takes as input a list of values to add up, separated by commas.
Here are some examples:
- =SUM(1, 4, 9)
- =SUM(A1, A3, A5)
- =SUM(A1, 5, 6, B1:B7)
As you see above, the SUM function can add numbers (1, 2, 3…), cell references (A1, A2, A3…) or cell ranges (A1:B10) – or even a combination of these. Enter them one after the other and separate them out by commas. Instead of providing just a list of numbers, the usual practice is to specify a cell range. Excel will then add up the values of all the cells in that cell range.
2. COUNT – Count cells that contain numbers
The COUNT function counts all the cells in a range that contain numbers, or contain something that represents a number. This is really useful if you need to know how many numbers your list contains, especially if your list has gaps in between numbers.
- =COUNT(3, 5, 9)
As with the SUM function above, the parameters of the COUNT function can be a combination of numbers, cell references, or cell ranges. Common practice is just to provide a cell range to have Excel count all the numbers in that range.
Note that text values are not counted. But Excel will count any representation of a number, such as numbers in text format (“1”), dates (which are really stored as serial numbers), or Boolean values (which are stored as either 0 or 1).
3. AVERAGE – Find the average of a list of numbers
With the AVERAGE function you can easily find the average of a list of values. Excel automatically calculates the sum of the list and divides it by the number of values in the list, resulting in the average.
- =AVERAGE(1, 6, 10)
The AVERAGE function will discard any text it encounters, and treat it as though it were never there. If you need a function that takes text into account, check out the AVERAGEA function.
4. MAX – Find the largest value in a list
The MAX function finds the largest number in a list of numbers. This is useful for finding extreme values in your data.
- =MAX(1, 4, A5, D1:D10)
This function also takes a combination of numbers, cell references, or cell ranges as input.
Below is an example that finds the maximum population in a list of the world’s 8 largest countries.
If you want to find the second or third largest number in a list, or the n-th largest number for that matter, you might want to check out the LARGE function.
5. MIN – Find the smallest value in a list
Similar to the MAX function, the MIN function finds the smallest number in a list of numbers.
- =MIN(1, 4, A5, D1:D10)
Then example below finds the minimum population in a list of the world’s 8 largest countries.
If you want to find the second or third smallest number, or the n-th smallest number, use the SMALL function.
6. ROUND – Round a number
Use the ROUND function to round a number to a specified number of digits. To set this up, you need the following:
- A number to round
- The number of digits you want to round to. The number 1 will round to one digit after the decimal sign, while 2 will round to two digits after the decimal sign. The number 0 will round to an integer, while -1 will round to the nearest 10 and -2 will round to the nearest 100, etc.
String these two together as the two parameters of the function, and separate them by commas. For example:
- =ROUND(134.14, 1)
- =ROUND(14593, -2)
Functions to Work with Text
7. CONCATENATE – Join text strings together
You can effortlessly join text strings together using the CONCATENATE function. It takes as input a list of text strings separated by commas which it joins together and returns one big string as output.
- =CONCATENATE(“John “, “McCain”)
- =CONCATENATE(A1, B7)
Take note that text entered directly into any formula (as opposed to being pulled in from another cell) should be enclosed in double quotation marks. Keep this in mind while working with this function or any of the other text functions.
Another easy way to join text strings in a formula is by simply inserting an ampersand symbol (&) between them, like this:
- =”John “&”McCain”
Take note of the space after the name John, which will ensure that there is a space between the name and surname in the final output.
8. LEN – Find the length of a text string
Use the LEN function to find the number of characters in a text string (commonly called the length of the string). It counts all characters including spaces and punctuation marks.
- =LEN(“One, two, three”)
9. RIGHT – Extract text from the right of a string
The RIGHT function extracts a specified amount of characters from the end of a string. In plain English, the RIGHT function does the following:
=RIGHT(from this text string, extract this many characters from the end)
The first parameter takes a text string as input and the second takes the number of characters to extract.
- =RIGHT(“Butternut”, 3) -> “nut”
- =RIGHT(A3, 4)
10. LEFT – Extract text from the left of a string
Similar to the RIGHT function, the LEFT function extracts a specified amount of characters from the beginning of a string. In plain English:
=LEFT(from this text string, extract this many characters from the beginning)
- =LEFT(“Butternut”, 6) -> “Butter”
- =LEFT(A3, 4)
11. MID – Extract text from the middle of a string
The MID function works much the same as LEFT and RIGHT, but it allows you to specify the position of a character in the string from which to start extracting. It takes three input parameters which reads like this:
=MID(from this text string, starting at this character, extract this many characters)
So you need the following parameters for this function:
- The text string to extract from. This can be text typed directly into the formula or a cell reference pointing to a cell containing text.
- The position of the character from which to start extracting. So if the phrase you want to extract starts at character 4 in the string above, enter 4.
- The number of characters to extract.
Examples of the MID function include:
- =MID(“Jon Lee Bridges”, 5, 3)
- =MID(C10, 7, 5)
Working with Dates and Times
12. DATE – Enter a date
Use the DATE function to enter any date into Excel. It takes a year, month, and a day as input parameters (all in number form, e.g. use 5 instead of May) and then converts them to a date serial number representing a date (to learn more about serial numbers, see this article).
Although Excel stores a date in the form of a serial number, you can have it display as a date by changing the Number Format on the Home tab to Short Date or Long Date.
Some examples of the DATE function include:
- =DATE(2016, 7, 7)
- =DATE(1998, 3, 24)
13. TIME – Enter a time
Similar to the DATE function, the TIME function takes three input parameters (hour, minute, and second) and converts them to a serial number representing the time.
Although Excel stores time in the form of a decimal serial number, you can have it display as a time by changing the Number Format on the Home tab to Time.
Some examples of this function include:
- =TIME(11, 30, 59)
- =TIME(15, 45, 30)
You can also add the DATE and TIME functions together to create a serial number that reflects both the date and time.
- =DATE(2016,7,7) + TIME(12,3,24)
Even though only the time is displayed in the image above, the serial number also contains the date’s information. You can see this by changing the number format to Short Date or Long Date.
14. TODAY – Find the date today
The TODAY function returns today’s date, as read from your computer’s calendar. This is great if you want to calculate the number of days between today and some other day. The TODAY function does not take any input parameters.
The output of this formula will update every time you make a change in the spreadsheet or open it up again.
15. NOW – Find the date and time right now
Similar to the TODAY function, the NOW function returns a serial number reflecting both today’s date and the time right now, as read from your computer’s calendar and clock. It doesn’t require any input parameters.
The output of this formula will update every time you make a change in the spreadsheet or open it up again. If you want to freeze the current time, do the following:
- Right-click on the cell and choose Copy.
- Right-click on the same cell and choose Paste Special -> Values (click on the icon for which the screen tip reads Values).
The following functions are more advanced, but still easy to master. If you can get to grips with them, they’ll transform your spreadsheets into dynamic, intelligent, decision-making masterpieces, and set you apart as the Excel guru of the workplace.
If you struggle to understand their workings here, please check out my entire course on Excel functions and formulas. I’ll teach you all you need to know to make it work.
16. IF – Decide what to do based on a test performed on your data
The IF function (my personal favorite) is the holy grail of spreadsheet automation! It performs a test on some data in your spreadsheet and then returns a value based on the outcome of that test. It takes three input parameters separated by commas. In plain English, it does the following:
=IF(perform this test on some data, then display this value if the test is TRUE, or display this value if the test is FALSE)
So to get this to work, you need the following three things:
- Some test of which the result will be either TRUE or FALSE. There are countless logical tests you can use here, such as testing if cell A1 is equal to 3, or whether cell B3 is less than 10. For an extensive list, see my complete guide to using the IF function.
- Some value to display if the test above turns out to the TRUE. This can be a number, text, or even another IF function that performs a second sub-test.
- Some value to display if the test above turns out to be FALSE.
Add these three components as the input parameters of the IF function, separate them out with commas, and you’re good to go.
Some examples include:
- =IF(A1=3, “Yes”, “No”)
This example tests if cell A1 is equal to 3, and then displays Yes if it does, and No if it doesn’t.
- =IF(B3<10, “Low”, “High”)
This example tests if cell B3 contains a number less than 10, and then displays Low if it does, and High if it doesn’t.
- =IF(C8>=0, 10, 100)
This example tests if cell C8 contains a number greater than or equal to 0, and then displays 10 if it does, and 100 if it doesn’t.
17. VLOOKUP – Find values in a table
Use the VLOOKUP function to find things in a table of data. Excel will search the first column of the table for a specific value and then retrieve any of the other pieces of information in that row. For instance, in the example below I can extract any value from the highlighted row by having VLOOKUP search for 5, the first value in the row.
In plain English, the VLOOKUP function does the following:
=VLOOKUP(Look for this value in the first column, look for it in this table, then return the corresponding value in this column, using either an exact or approximate match based on this value)
To make this work, you need the following things:
- A value to look for. This is the value in the first column of the row from which you want to extract information.
- An actual table in which to search for this value. This is typically a cell range that spans multiple columns and rows.
- The column number from which you want information extracted. Here, the leftmost column is 1, the second column from the left is 2, and so forth. So if you wish to extract a value from the 5th column in the table, enter 5 here.
- Decide whether you want Excel to search for the exact value you provide in (1) above, or the closest value it can find. Insert FALSE for the first case and TRUE for the second case. This parameter is optional.
Now you can put all these together as the parameters of the VLOOKUP function (in that particular order), separate them out with commas, and you’re ready to go.
Some examples of this are:
- =VLOOKUP(142, A5:E16, 3, 0)
The function above will search the first column of range A5:E16 for an exact match of the value 142, and then retrieve the value in column 3 of that row.
- =VLOOKUP(5, B3:F10, 2, 0)
This function will browse the ID column of the customer list (shown in the image below) and search for the number 5. It will then retrieve the entry in the second column of the same row (in this case the name Quinne).
If your table is structured horizontally with the column and rows flipped, try using the HLOOKUP function instead.
There you have it. Seventeen great functions you can easily master. Armed with these skills, you can now transform any old data-laden spreadsheet into a dynamic information powerhouse. Your boss will love you for it.
Still struggling to make ends meet, join my free 3-day course on Excel formula basics below: