Microsoft Excel is packed full of functions you might not be using. Here is a look at the top 24 functions in Excel that you should be using to make your workday easier.
Excel is one of the most powerful spreadsheet tools available. Part of that power comes from the many embedded functions that are available for your use inside of spreadsheets. Some of these functions are well known, while others are rarely used but just as powerful once you understand how to use them.
Essential Excel Functions
There are a few functions that most people couldn’t do without. These include everything from the basic SUM function to the more advanced COUNT functions.
The SUM function is the most commonly used function in Excel. Its use is very simple. Just type =SUM( into a cell, then drag the mouse down the column of cells you want to add.
The AVERAGE function works similarly. Just type =AVERAGE( in the cell, then drag the mouse down the column of cells you want to average.
The MAX function is useful when you have a very long list of numbers in a column and you need to find the largest value. Type =MAX( in the cell, then drag the mouse down the column of cells where you want to find the maximum value.
You can also use the MIN function in exactly the same way as the MAX function, except the result will be the minimum number in the list.
The PRODUCT function is good for those times when you have one or more numbers you need to multiply together, such as when you’re calculating an amount represented by a percentage.
Type =PRODUCT( in the cell, then drag the mouse down the column of cells you want to multiply.
Similar to the PRODUCT function is the SUMPRODUCT function. For this function to work, you need to select multiple lists of numbers in your spreadsheet. Once you’ve selected the multiple columns of data, the function will add every column, and then multiply the results of each for the answer.
Note: An alternative method of selecting multiple items to perform a math function on is to select only individual items by typing the start of the function, then holding down the CTRL key and selecting the cells you want to add or multiply one at a time. When you type ) to close out the function and press ENTER, the result will display in that cell.
Logical IF Functions
Another family of useful function in Excel is those that end in “IF”. This lets you perform calculations on cells only if certain conditions are met.
For example, imagine you have a column of numbers, but you only want to average the ones that are over a certain limit. You can do this with the AVERAGEIF function.
To use this function, select the cell where you want the results to go. Select Formulas from the menu. Select More Functions from the Function Library, then select Statistical from the list, and select AVERAGEIF from the function list.
When you select the function, a Function Arguments window appears.
Here you can select each field and then select the range of cells in the spreadsheet for each field. For AVERAGEIFS:
- Range: Select the range of cells you want to average.
- Criteria: Type a logical comparison with a limit; for example >15 will average only the cells over 15.
You only need to fill out the bold fields. All non-bold fields are optional.
Other IF functions you can use with criteria like this include:
- COUNTIF: Count only the cells the meet a condition.
- COUNTIFS: Same as COUNTIF, but can be used to count string data.
- MINIFS: Will return the minimum value in a range, only of the items that meet your criteria.
- MAXIFS: Same as MINIFS, but returns the maximum value in a range.
Logical Functions
Some of the most useful Excel functions are the logical functions that let you compare different cells to either return results based on the results of a comparison.
For example, the IF function will examine whether cells match any comparison conditions you set. If the comparison is true, the result field will return any value you specify for TRUE. Otherwise, it’ll return any value you specify for FALSE.
To use the IF function, click in the cell where you want the results to appear, then type =IF(.
- Click the cells you want to compare with a comparison operator between them. For example A2<B2.
- Type a comma, followed by the value you want to display if the comparison is true. For example, type “Yes” to return Yes if the comparison is true.
- Type another comma, followed by the value you want to display if the comparison is false.
You can fill the entire column to the last row to perform the same comparison on all rows.
An advanced approach to using the IF function is by nesting another calculation in the True or False fields. For example, if A2<A3, you can set the True field to A3-A2, and the False field to A2-A3.
Other logical functions in Excel include:
- AND: If all cells you list are True, the results will be True, otherwise False.
- NOT: This function can be used to reverse results in another cell; True to False, or False to True.
- OR: If any one of the cells you list are True, the results will the True, otherwise False.
- SWITCH: This lets you return any number of specific output values or formulas if an expression matches any number of corresponding specific values.
- XOR: If only one of the cells you list are True, the results will be True. If all are True or all are False, the result will be False.
Keep in mind that just like with the IF statement, you can replace the “False” or “True” output results with another formula. This makes logical functions in Excel very powerful.
Text Functions
There are many times in Excel when you may want to piece together a series of cells to form one longer cell. Or you may need to otherwise deal with string values. Excel offers a variety of string formulas you can use in these situations.
The CONCAT function lets you concatenate several text strings from several different cells in your worksheet, into one string. Using this function is as simple as typing =CONCAT(“string1″,”string2″,”string3”). The function can have as many strings as you need, and each of the strings can be replaced by the cell containing the string.
Other string functions you can use in Excel include:
- LEFT: Returns the number of characters you specify from the left side of a string.
- RIGHT: Returns the number of characters you specify from the right side of a string.
- MID: Extract characters from the middle of a string, just given
- TRIM: Trims any spaces either at the beginning or end of a string.
- TEXT: Converts a value from any cell to a string version of that value.
- LEN: Returns the number of characters in a string.
Using Excel Functions
There are Excel functions for nearly any purpose you may be able to dream up. The easiest way to learn about them all is to select the Formulas menu, select Insert Function, and in the pop-up window, change the Category to all. You can scroll down through all available functions and read the descriptions to learn more about how each function works.
The nice thing about using Excel functions is that it walks you through each parameter in the function while you’re using them. This way you’ll know what you need to insert and how many parameters are needed for the function to work right.
After a while, with practice, you’ll develop a regular list of functions you’re an expert at using and that you turn to frequently.