Functions are built-in formulas. They can perform mathematical operations quickly and easily. Like formulas, functions begin with an equal sign (=) and are made up of cell references, values, and operators. Unlike formulas, functions also have a function name that describes the operation to be performed. The name is followed by parentheses that contain the function arguments. The arguments specify the values or cells to be used by the function.
Functions make formulas easier to read, understand, and enter. For example, instead of typing the formula =E3+E4+E5+E6+E7+E8+E9, we could use the SUM function and simply enter =SUM(E3:E9).
Entering a function
Let's use the SUM function to find the total number of items sold. We want this total to be in cell C7.
To make cell C7 the active cell,
Click in cell C7
If we know the function name, we can simply begin entering the function. Even if we don't know the function name, Microsoft Excel will suggest possibilities as we type in the cell. Remember, all functions start with an equal sign (=).
To begin typing the function, in cell C7, type:
=s
As soon as we begin typing, the Formula AutoComplete feature displays a list of possible functions and corresponding ToolTips describing what the functions do.
We could scroll through this list until we find the SUM function. However, it may be easier to continue typing the function name.
NOTE: The Formula AutoComplete feature in macOS looks slightly different but the functionality is the same.
To enter the SUM function, on the keyboard, type:
um
Now that the function name is correct, we can begin adding the arguments, in this case, cell references, to the function. We want Excel to add all of the values in column C. We can type the cell range, C2:C6, or we can select those cells.
To begin adding the arguments, on the keyboard, type:
(
To apply the SUM function to cells C2:C6,
Press & Drag cells C2:C6
To complete the function, on the keyboard, type:
)
To accept the function, on the keyboard, press:
Enter key
The total number of items sold, 148, appears in cell C7.
Copying a function
Now that the SUM function is in cell C7, we can copy it to cell D7 to see the total amount of sales for the fundraiser. To do this, we will use the autofill handle. The autofill handle is the small box in the lower right corner of the active cell, as seen in the following image:
If we place our cursor on this box, the cursor shape changes to a crosshair.
Using this cursor, we can press & drag to copy the function in the cell.
To make cell C7 the active cell,
Click in cell C7
To use the autofill handle, in cell C7,
Point to the autofill handle in the lower right corner of the cell
To copy the function to cell D7,
Press & Drag the autofill handle to cell D7
The SUM function has been copied and the Total Item Sales has been correctly calculated: 1,155.00.
It may be necessary to format the total sales as currency.
Make cell D7 the active cell, if necessary.
To format cell D7 as currency, if necessary, on the Home tab,
Click , Click Currency
Using the Insert Function dialog box in Microsoft Excel for Windows
If we don't know the name of the function, we can use the Insert Function dialog box to choose one from a list. Let's use this feature to enter the MIN function in cell H1. The MIN function will return the minimum value in a range of cells.
NOTE: If you are working in Microsoft Excel for macOS, please move to the section Using the Formula Builder in Microsoft Excel for macOS.
To make cell H1 the active cell,
Click in cell H1
To open the Insert Function dialog box, next to the Formula bar,
Click
The Insert Function dialog box opens:
This dialog box gives us the option to search for a function by name or description. We can also select a category of functions. Currently, the dialog box is showing the most recently used functions.
Let's search for the MIN function. It may be necessary to select and delete the text in the "Search for a function" field before completing the search.
To delete the text, if necessary, in the "Search for a function" field,
Press & Drag to select the text, press: Delete key
To search for the MIN function, in the "Search for a function" field, type:
min, press: Enter key
Several functions containing the text "min" appear in the dialog box. The MIN function is at the top of the list.
To insert the MIN function, in the Insert Function dialog box,
Click MIN, Click
The Insert Function dialog box becomes the Function Arguments dialog box:
This dialog box gives us spaces to add the arguments we want to use with the MIN function. There is also a short description of the MIN function, "Returns the smallest number in a set of values. Ignores logical values and text." As arguments are added, the formula result will appear in the lower-left corner of the dialog box.
Let's add the arguments now.
We are trying to find the lowest price of all of the items. The item prices are in column B. Therefore, we will use the range B2:B6 in the function.
To add the range B2:B6 to the function, in the Number1 field in the Function Arguments dialog box type:
B2:B6
To accept this range and complete the formula, in the Function Arguments dialog box,
Click
The MIN function is entered and the result, $2.00, appears in cell H1.
Because we used cell references that were formatted as currency in our function, the result is automatically formatted as currency.
Let's add some other functions related to the item prices. We'll begin by using the MAX function to find the highest item price. We'll put this function in cell H2.
To select the correct cell, if necessary,
Click in cell H2
To enter the MAX function, type:
=max(B2:B6) Enter key
The maximum item price, $15.00, appears in cell H2.
Now let's use the AVERAGE function to find the average item price.
To select the correct cell, if necessary,
Click in cell H3
To enter the AVERAGE function, type:
=average(B2:B6) Enter key
The average item price, $8.20, appears in cell H3.
NOTE: If you are working in Microsoft Excel for Windows, you can move to the section Working with absolute cell references.
Using the Formula Builder in Microsoft Excel for macOS
If we don't know the name of the function, we can use the Formula Builder pane to choose one from a list. Let's use this feature to enter the MIN function in cell H1. The MIN function will return the minimum value in a range of cells.
To make cell H1 the active cell,
Click in cell H1
To open the Formula Builder pane, next to the Formula bar,
Click
The Formula Builder pane opens:
This pane shows the recently used functions as well as all available functions. We have the option to search for a function or to scroll to find a category and related functions.
The selected function is described at the bottom of the pane.
Let's search for the MIN function.
To begin searching for a function, in the Function Builder pane,
Click in the Search field
To search for the MIN function, type:
min
Several functions containing the text "min" appear in the Formula Builder. The MIN function is at the top of the list.
To insert the Min function, in the Formula Builder pane,
Click MIN, Click
The Formula Builder pane changes and now gives us spaces to add the arguments to the MIN function. There is also a description of the MIN function, "Returns the smallest number in a set of values. Ignores logical values and text." As arguments are added, the function result will appear in the pane.
Let's add the arguments now.
We are trying to find the lowest price of all the items. The item prices are in column B. Therefore, we will use the range B2:B6 in the function.
To add the range B2:B6 to the function, in the Number1 field in the Formula Builder pane, type:
B2:B6
To accept this range and complete the formula, in the Formula Builder pane,
Click
To close the Formula Builder pane, in the upper right corner,
Click
The MIN function is entered and the result, $2.00, appears in cell H1.
Because we used cell references that were formatted as currency in our function, the result is automatically formatted as currency.
Let's add some other functions related to the item prices. We'll begin by using the MAX function to find the highest item price. We'll put this function in cell H2.
To select the correct cell, if necessary,
Click in cell H2
To enter the MAX function, type:
=max(B2:B6) Enter key
The maximum item price, $15.00, appears in cell H2.
Now let's use the AVERAGE function to find the average item price.
To select the correct cell, if necessary,
Click in cell H3
To enter the AVERAGE function, type:
=average(B2:B6) Enter key
The average item price, $8.20, appears in cell H3.
Let's save the workbook.
To save the workbook, on the keyboard, press:
Control key + S
Let's move on and learn how to work with absolute cell references.
, 
