When using Excel functions play an important role in finding values for a range of cells. Learn all about using functions in Excel.
A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.
Optional: Download our practice workbook.
Watch the video below to learn more about working with functions.
In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.
Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.
For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.
Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.
There are a variety of functions available in Excel. Here are some of the most common functions you'll use:
The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In the example below, we'll use the SUM function to calculate the total cost for a list of recently ordered items.
The AutoSum command can also be accessed from the Formulas tab on the Ribbon.
You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To use this shortcut, hold down the Alt key and then press the equals sign.
Watch the video below to see this shortcut in action.
If you already know the function name, you can easily type it yourself. In the example below (a tally of cookie sales), we'll use the AVERAGE function to calculate the average number of units sold by each troop.
Excel will not always tell you if your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.
While there are hundreds of functions in Excel, the ones you'll use the most will depend on the type of data your workbooks contain. There's no need to learn every single function, but exploring some of the different types of functions will help you as you create new projects. You can even use the Function Library on the Formulas tab to browse functions by category, such as Financial, Logical, Text, and Date & Time.
To access the Function Library, select the Formulas tab on the Ribbon. Look for the Function Library group.
Click the buttons in the interactive below to learn more about the different types of functions in Excel.
The AutoSum command allows you to automatically return results for common functions, like SUM, AVERAGE, and COUNT.
The Recently Used command gives you access to functions you've recently worked with.
The Financial category contains functions for financial calculations like determining a payment (PMT) or interest rate for a loan (RATE).
Functions in the Logical category check arguments for a value or condition. For example, if an order is more than $50, add $4.99 for shipping; if it is more than $100, do not charge for shipping (IF).
The Text category contains functions that work with the text in arguments to perform tasks, such as converting text to lowercase (LOWER) or replacing text (REPLACE).
The Date & Time category contains functions for working with dates and time and will return results like the current date and time (NOW) or the seconds (SECOND).
The Lookup & Reference category contains functions that will return results for finding and referencing information. For example, you can add a hyperlink to a cell (HYPERLINK) or return the value of a particular row and column intersection (INDEX).
The Math & Trig category includes functions for numerical arguments. For example, you can round values (ROUND), find the value of Pi (PI), multiply (PRODUCT), and subtotal (SUBTOTAL).
More Functions contains additional functions under categories for Statistical, Engineering, Cube, Information, and Compatibility.
If you're having trouble finding the right function, the Insert Function command allows you to search for functions using keywords.
In the example below, we'll use the COUNTA function to count the total number of items in the Items column. Unlike COUNT, COUNTA can be used to tally cells that contain data of any kind, not just numerical data.
While the Function Library is a great place to browse for functions, sometimes you may prefer to search for one instead. You can do so using the Insert Function command. It may take some trial and error depending on the type of function you're looking for; however, with practice, the Insert Function command can be a powerful way to find a function quickly.
In the example below, we want to find a function that will calculate the number of business days it took to receive items after they were ordered. We'll use the dates in columns E and F to calculate the delivery time in column G.
Like formulas, functions can be copied to adjacent cells. Simply select the cell that contains the function, then click and drag the fill handle over the cells you want to fill. The function will be copied, and values for those cells will be calculated relative to their rows or columns.
If you're comfortable with basic functions, you may want to try a more advanced one like VLOOKUP. Check out our article on How to Use Excel's VLOOKUP Function for more information.
To learn even more about working with functions, visit our Excel Formulas tutorial.