Performing calculations, whether simple or complex, can be tedious and time consuming. You can use Excel
functions and formulas to do these calculations for you. Take this tutorial to learn how.
What you'll learn
After completing this tutorial, you'll be able to:
Create a simple formula for basic mathematical calculations.
Update formula results automatically.
Use a function to simplify formulas.
Copy a formula.
Edit a formula.
Requirements
Excel for Mac 2011
Basic Excel
skills, including how to open a workbook, and select cells, columns, and rows.
You can calculate values on your sheet by using a
formulaA sequence of values, cell references, names, functions, or operators in a cell that produces a new value from existing values. A formula always begins with an equal sign (=).. For example, you might use a formula to calculate your monthly grocery expenses or to find the average amount that you pay for a stock.
Let's enter a formula to calculate the total amount due for an invoice item.
Open the practice file, which is located at the beginning of this tutorial.
Click cell D11.
Click in the formula bar, and then type an equal sign (=).
Tip If you don't see the formula bar, click View > Formula Bar.
Click cell B11, and then type an asterisk (*), which is the multiplication sign.
Tip To hide the ribbon while you work, on the right side of the ribbon, click .
Click cell C11. Cell B11 and C11 appear in different colors to indicate that they are part of the formula.
Press
RETURN.
Notice that the result of the formula ($13,988.00) appears in the result cell (D11).
Hints
Each cell on a sheet has a cell reference, or "address." A cell reference is the set of coordinates that a cell occupies. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3. You can use cell references in a formula instead of typing the number in the cell.
The cell reference for a selected cell appears in the Name box, located to the left of the formula bar.
All formulas begin with an equal sign (=).
The multiplication sign (*) is called an
operatorA sign or symbol that specifies the kind of calculation — such as addition, subtraction, or multiplication — to be performed on the elements of a formula.
Operators include mathematical operators; comparison, or logical, operators, which compare two values and produce the value TRUE or FALSE. The text operator & (ampersand), which concatenates two pieces of text into a single combined text value; and reference operators, which produce one reference to a cell or range of cells from two references.. The other basic mathematical operators are: the plus sign (+), minus sign (-), and the division sign (/).
Before you move on
Make sure that you can do the following:
Create a simple formula in the formula bar.
2. Update a formula result automatically
If you change the value of a cell that's used in a formula, Excel
can update the result automatically. You don't have to change the formula at all since cell references are used in the formula instead of the values.
Let's change the unit cost for an item and see how the result updates.
Click cell C11.
Type 199.99 in the cell, and then press
RETURN.
Tip Pressing
RETURN
"confirms" the entry or change and calculates the new result. To cancel out of edit mode without confirming a change, press
ESC.
Notice that the updated result ($10,399.48) in cell D11 reflects the change in cell C11.
Hints
To delete a formula or the results of a formula, click the cell that contains the result, and then press
DELETE
(or
FN+DELETE
on a MacBook keyboard).
Deleting a formula or formula result does not delete the cell formatting (for example, shading or a currency number format).
Before you move on
Make sure that you can do the following:
Change the value of a cell in a formula and confirm that the result updates automatically.
3. Add a range with the SUM function
In Excel, a function is a predefined formula. For example, the SUM function adds a
rangeTwo or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.
of values that you specify, so that you don't have to type a plus (+) operator between each value. In other words, you can use a function as a shortcut so that you don't have to create a formula manually.
Let's use the SUM function to calculate the total quantity of units on the invoice.
Click cell B15.
In the formula bar, type
=SUM(.
Select cells B11, B12, B13, and B14. A marquee appears around the selected cells.
Press
RETURN.
Notice that the result of the formula (92) appears in cell B15.
Tip To see the formula for the result, click cell B15 and look in the formula bar. The formula for the result is =SUM(B11:B14).
Hints
The notation B11:B14 is called a “range reference,” which means that the formula includes everything in the range from cell B11 through cell B14. You can type a range reference directly into a formula rather than selecting cells.
A range reference can span multiple columns and rows. For example, the formula =SUM(B11:C14) adds everything in cells B11 through C14.
You can include non-adjacent cells in a formula, such as =SUM(B11,C13,D11).
Before you move on
Make sure that you can do the following:
Use the SUM function in a basic formula.
4. Copy a formula with AutoFill
With AutoFill, you can copy a formula to cells in the same row or column so that you don't have to retype a formula in several places on a sheet.
Let's use AutoFill to copy the formula we created earlier to calculate the total amount due for each item.
Click cell D11, and then rest the pointer over the lower-right corner of the cell until it appears as a black plus sign.
Drag across cells D12, D13, and D14.
Notice that total amounts for each item appear in cells D12, D13, and D14.
Click cell D14. Notice that the formula bar contains the correct formula and cell references, =B14*C14.
Tip To verify that a formula contains all of the cells that you intended, click the formula in the formula bar. After you verify the formula, press
ESC.
Hints
When you use AutoFill to copy formulas, Excel
adjusts cell references in different columns or rows automatically, but it uses the same function in all new results cells.
Before you move on
Make sure that you can do the following:
Use AutoFill to copy a formula to several cells in the same row or column.
Identify a copied formula in the formula bar.
5. Use AutoSum to calculate quickly
By using AutoSum, you can use some of the most common functions, such as SUM, AVERAGE, MAX, and MIN, to do quick calculations. Excel
creates the formula for you and includes both the function and the cell references.
Let's use AutoSum to calculate the invoice subtotal without having to type the formula.
Click cell D16.
On the Standard toolbar, click AutoSum.
Notice that Excel
selects the range of cells to include in the formula, in this case =SUM(D11:D15).
Press
RETURN.
Notice that the result of the formula ($15,379.71) appears in cell D16.
Hints
To use other common functions, such as AVERAGE, MAX, and MIN, click the arrow next to AutoSum.
You can use AutoSum to create formulas in both rows and columns.
A green triangle in the upper-left corner of a cell indicates a possible error. To troubleshoot the error, click the cell that contains the error indicator, and then click for options.
Before you move on
Make sure that you can do the following:
Use AutoSum to add a range of cells.
6. Edit a formula
You can change a formula to include different cells or to use a different function. You can edit a formula either in the formula bar or directly in the cell.
Let's correct the formula that calculates the total amount due.
Click cell D20.
Click in the formula bar. Notice that the formula, =SUM(D19,D18,D16,D14), includes an extra cell reference (D14), which is incorrect because the value in cell D14 is already included in the invoice subtotal in cell D16.
In the formula bar, click to the right of D14, and then press
DELETE
(or
FN+DELETE
on a MacBook keyboard) four times. The new formula should be =SUM(D19,D18,D16).
Press
RETURN.
Notice that the correct total ($16,686.99) appears in cell D20.
Hints
To cancel changes while editing a formula, press
ESC.
To edit a formula directly in the cell, double-click the cell (or press
CONTROL+U), and then type the changes.
Before you move on
Make sure that you can do the following:
Edit a formula either in the formula bar or directly in the cell.
Quick Reference Card
To
Do this
Determine the cell reference (or address) for a cell
Click the cell, and then look in the Name box, located to the left of the formula bar.
View the formula for a result
Click in the result cell, and then look in the formula bar. If you don't see the formula bar, click View > Formula Bar.
Find other quick functions (for example, AVERAGE, MAX, COUNT NUMBERS, MIN)
On the Standard toolbar, click the arrow next to AutoSum.
Cancel changes while entering or editing a formula
Press
ESC.
Copy a formula by using AutoFill
Click the result cell that contains the formula that you want to copy. Rest the pointer over the lower-right corner of the cell, and when it appears as a black plus sign, and then drag across the cells that you want to copy the formula to.
Edit a formula directly in a cell
Double-click the result cell. The keyboard shortcut to enter formula editing mode is
CONTROL+U.
Delete a formula
Click in the result cell, and then press
DELETE
(or
FN+DELETE
on a MacBook keyboard). You can also press
CONTROL
and click the result cell, and then click Clear Contents.
More Hints
A formula is a sequence of commands (for example, cell references and operators) that produce a value. A function is a predefined formula, such as SUM or AVERAGE, that makes it easier to do calculations that might be difficult to create with a basic formula.
All formulas begin with an equal sign (=).
The four operators that indicate the type of math operation to perform are: the plus sign (+), the minus sign (-), the multiplication sign (*), and the division sign (/). You can type these operators by using the number pad on a keyboard.
The notation B11:B15 is called a “range reference." For example, the formula =SUM(B11:B15) means to use the SUM function to total all of the values that appear in the range B11 through B15.
You can include non-adjacent cells in a formula, such as =SUM(B11,C13,D11).
By using AutoFill, you can copy a formula to cells in the same row or column. Excel
adjusts the cell references automatically.
Related Content
If you liked this training, you might want to try the following:
