Are mathematical equations used to perform calculations in an Excel spreadsheet?

This article is based on legacy software.

Excel performs calculations using formulas and functions. A formula is a series of commands instructing Excel to perform calculations based on designated values, cell references, and commands. A function is a pre-written formula. A common example of a function is Average, a pre-written formula which finds the sum of a set of numbers, counts how many numbers are in the set, and then divides to find the average. To save time and work, functions can be embedded within formulas.

This document focuses on formulas and provides some formulas that will make working with your workbook and worksheets faster. For more information on functions, refer to Calculating with Functions.

Writing Formulas for Your Workbook

Formulas can be used not only for small mathematical operations within cells and worksheets but also to link to other worksheets and other workbook files.

Basic Formula Requirements

When writing a formula, there are three elements which must be included for it to work properly:

• An equals sign [=] at the beginning of the formula.
• Operand(s) (e.g., values or cell references that will be used to create the result of the formula).
• Operant(s) (e.g., commands to add, subtract, or perform other actions upon the operands).

Formulas may be extremely simple or extremely complex, and some even contain functions within them. A very basic formula could appear like this:

=A1 + A2

In this example, the cell references A1 and A2 are the operands, the [+] is the operant, and the selected cell would contain the sum of the values in cells A1 and A2.

Creating a Formula for a Single Worksheet

Formulas can be basic or complex, depending on your needs.
HINT: If you are creating a complex but fairly common formula (such as finding an average), it may be helpful for you to insert a function instead of creating the entire formula from scratch.

All formulas must be created following these steps:

1. Select the cell where the results should be displayed.

2. To begin the function, press [=].

3. Click the first cell to be included.
   OR
   Type the cell reference to the first cell to be included.
   NOTE: Be sure to stay aware of whether you will need to use a relative or absolute cell reference.

4. Type the first operator.
   EXAMPLE: Press [+].

5. Click the next cell to be included.
   OR
   Type the cell reference to the next cell to be included.

6. Repeat steps 4–5 as necessary to complete the formula.

7. When finished, press [Enter].
   HINT: Be sure to press [Enter] before clicking away from the cell. If you don't, each cell that you click will be added to your formula.

Writing Formulas for Multiple Worksheets

While multiple worksheets can make your workbook more effective, writing formulas that include information across multiple worksheets is more complicated. The advantage is that you can link between cells on one or more worksheets within the same workbook.

Using Special Characters

Five symbols can be used when creating a formula that links two separate worksheets. Each of these symbols must be placed directly into the formula to complete its respective function.

Creating a Link to a Single Worksheet in the Same Workbook

Excel allows you to create links to other worksheets, which enables you to include values from other worksheets in your formulas. These instructions focus on linking to a separate worksheet within the same workbook.

1. Select the cell where you want the formula to appear.

2. To create the formula, use this format: =function_name(worksheet_name!cell_reference)
   EXAMPLES:
   (with no spaces in sheet name) =SUM(Budget!C4:C8)
   (with space in sheet name) =SUM('Budget 2007'!C4:C8)

3. To accept the formula, press [Enter].

Creating a Link to More than One Worksheet in the Same Workbook

Excel allows you to create links to more than one worksheet at a time. This method allows you to make calculations based on a selected range of worksheets.

1. Select the cell where you want the formula to appear.

2. To create the formula, use this format: =function_name(worksheet range!cell_reference)
   EXAMPLES:
   (with no spaces in sheet name) =SUM(Budget:Salary!D2:D10)
   (with space in sheet name) =SUM('July 2006:June 2007'!C4:C8)

   NOTE: Be sure to include a plus sign (+) or colon (:) between the worksheet range.

3. To accept the formula, press [Enter].

Writing Formulas for Other Workbook Files

Formulas can also reference cells and worksheets from other workbooks.

1. Select the cell where you want the formula to appear.

2. To create the formula, use this format:=function_name(‘[workbook_name]worksheet_name’!cell_reference)
   EXAMPLE: =SUM(‘[07Budget.xls]Salary’!D2:D10)
   NOTES:
   There is no difference in the formula if your sheet name contains a space.
   If the two files you are working with are in different directories, you must include the file path in your formula. Use the following format:=function_name('[file_path\workbook_name]worksheet_name'!cell_reference)
   EXAMPLE: =SUM('[E:\Accounting\07Budget.xls]Salary'!D2:D10)

3. To accept the formula, press [Enter].

Using Range Names in Formulas

Using range names for some cells can be especially helpful if you cannot remember the cell location. Range names should not be assigned to every group of cells. Special groups, like assumptions and key totals, are good groups with which to use range names. For guidelines and instructions for assigning and adding range names, refer to Referencing Cells with Names.

When using a range name in your formulas, the name will replace the traditional cell reference. For example, if the desired value is stored in cell C24, which is named INC, either reference can be used in a formula, as shown in the examples below.

EXAMPLE:

Calculating Your Workbook

By default, Excel recalculates all formulas every time you enter new information, in case the formulas are affected by the new information. When working with large workbooks and several calculations, you may find data entry slowing down (especially on older computers). To speed up data entry, you can change the calculation mode so that the workbook is recalculated only when you specify (and optionally, when you save your file).

WARNING: Excel has an option for automatically calculating before you save your work, but not before you print. If you set up manual calculation, you will need to make sure that you recalculate before you print, and especially before others view your document.

Changing to Manual Calculation

To speed up data entry time, you can choose the Manual Calculation option. To choose this option, follow these steps:

1. From the Ribbon, select the Formulas command tab.

2. In the Calculations group, click Options » select Manual.
   

   

Calculating Your Workbook Manually

You may choose to use the Manual Calculation feature in two ways: with the keyboard or from the Ribbon.

Keyboard Option:

1. Press [F9].
   OR
   Press [Ctrl] + [=].
   Your data is updated.

Ribbon Option:

1. From the Ribbon, select the Formulas command tab.

2. In the Calculations group, click Calculate Now.

   
   
   Your data is updated.

Was this article helpful? Yes No