This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?
Basic worksheets in Excel often require you to use formulas and functions, which are calculations based on designated values, cell references, and commands. Functions are pre-written commands provided by Excel, while formulas are written entirely by the user. While both methods are useful, functions often save time and energy when working with complex but common tasks (such as finding the sum or average of a group of numbers) by allowing you to customize a pre-created calculation instead of typing it yourself.
HINT: For more information on formulas, refer to Performing Calculations with Formulas (Win | Mac).
Functions have two basic parts which you should be aware of:
Functions that are inserted using the Insert Function (Windows) / Formula Builder (Macintosh) dialog or the Point and Click method provide empty equations, you must provide the values which will be used in the calculation. Depending on the calculation, you may choose to use several types of operands.
NOTE: While typing cell references, keep in mind that the calculations will be done using the values present in the particular cells entered, not with the cell references themselves.
Operand |
Example |
Value |
---|---|---|
Cell Reference | A1 | Calculates the function using the value(s) present in a specified cell. References can be relative or absolute. |
Cell Range | A1:A3 | Calculates the function using the values present in all cells specified. References can be relative or absolute. |
Named Cell Range | Quiz Scores | Calculates the function using a specific group of cells that you have previously named. If the function is copied or moved, it will still use the values present in the specified range. |
Value | 5 | Calculates the function using a specific value provided by you. |
There are multiple ways to create a function. You can insert functions manually (by typing them), or you can use the Insert Function dialog box in Windows and the Formula Builder dialog box in Macintosh. The dialog box option eliminates the possibility of a typing error, so it is the recommended method.
The dialog box option makes it easy to determine what functions are available, which function you should be using, and what you need to include in the function. It displays a listing of all functions or categories of functions available with Excel. As you select a function, a sample of the function appears at the bottom of the dialog box. As you make your selection, the dialog box will request certain types of information; you will simply need to select the cells where that information is located.
Windows:
Select the cell where the function should be added
From the Ribbon, select the Formulas command tab
In the Function Library group, click FUNCTION WIZARD
The Insert Function dialog box appears.
From the Or select a category pull-down list, select the appropriate function category
OR
Select
All
From the Select a function scroll box, select the desired function
HINT: A description of the selected function appears beneath the Select a function scroll box.
Click OK
The Function Arguments dialog box appears.
NOTES:
The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.
A function's arguments are the value(s) that the function is being performed upon.
Click RESTORE DIALOG
Click OK
The results of the function appear in the selected cell.
Macintosh:
Select the cell where the function should be added
From the Insert menu, select Function...
The Formula Builder dialog box appears.
In the formula scroll-box, double-click the desired function
HINT: In the Description section, a description of the selected function appears.
NOTES:
The Arguments section of the Formula Builder expands.
The appearance and options available in the Arguments section will differ depending on which function has been chosen.
A function's arguments are the value(s) that the function is being performed upon.
Complete the text boxes.
HINT: Type the data to be used in the function or click and drag the mouse to select the desired cells
Press [return]
Windows only:
Excel provides a multitude of functions for your use. While this ensures that functions exist for most of your needs, it can also make it very difficult to find a particular function. To make functions easier to find, they are divided into categories (e.g., math and trig functions, date and time functions, logic functions, etc.). If you are looking for a function that belongs in a particular category, you can access the Function Arguments dialog box from that category.
Select the cell where the function should be added
From the Ribbon, select the Formulas command tab
In the Function Library group, click the correct category » select the desired function
The Function Arguments dialog box appears.
NOTES:
The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.
A function's arguments are the value(s) that the function is being performed upon.
Click RESTORE DIALOG
Click OK
The results of the function appear in the selected cell.
The Function Arguments dialog box helps you to create functions. As you type information about the function, the Function Arguments dialog box displays the name of the function, the function arguments (i.e., the values that the function is being performed upon), a description of the function and its logic, and the result of the function. Once you have entered a function, you can further edit it using the Function Arguments dialog box.
Windows only:
Select a cell containing a function
On the Formula bar, click FUNCTION WIZARD
The Function Arguments dialog box appears.
Functions based on cell references can be created by clicking the cells rather than typing the cell entries. This "point and click" method can help reduce the chance of error in the functions and may be easier for some users.
The key to the point and click method is to click the cells to be included and type the operators where appropriate.
NOTE: All functions that can be accessed from the Insert Function (Windows) / Formula Builder (Macintosh) dialog box can be typed with a text-based command. If you choose to type your function into a cell, however, be sure that you know precisely how to enter information for the function, especially if you are working with a complex function.
The following examples provide step-by-step instructions for a simple addition of two cells and for adding a range of cells.
Select the cell where the results should be displayed
To start the function, press [=]
Click the first cell to be added
Press [+]
Click the next cell to be added
Repeat steps 4–5 as necessary
Windows: Press [Enter]
Macintosh: Press [return]
The sum appears in the selected cell.
Select the cell where the results should be displayed
To start the function, press [=]
Type SUM(
Click the last cell in the range to be added
Type )
Windows: Press [Enter]
Macintosh: Press [return]
The sum appears in the selected cell.