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?
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.
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.
When writing a formula, there are three elements which must be included for it to work properly:
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.
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:
Select the cell where the results should be displayed
To begin the function, press [=]
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.
Type the first operator
EXAMPLE: Press [+].
Click the next cell to be included
OR
Type the cell reference to the next cell to be included
Repeat steps 4–5 as necessary to complete the formula
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.
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.
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.
Character | Name | Use within Formula |
---|---|---|
! | Exclamation point | Place between the worksheet name and cell reference |
' | Apostrophe | Place around the filename and sheet name or when a sheet name contains a space EXAMPLE: 'Budget 2007'!C4:C8 |
[ ] | Brackets | Place around a filename |
: | Colon | Signifies a range (B3:C2 means B3 through C2) |
+ | Plus sign | Signifies a range (B3+C2 means B3 and C2) |
NOTE: The following examples assume that the worksheets Budget and Salary are located within the workbook 07Budget.xls.
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.
Select the cell where you want the formula to appear
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)
To accept the formula, press [Enter]
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.
Select the cell where you want the formula to appear
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.
To accept the formula, press [Enter]
Formulas can also reference cells and worksheets from other workbooks.
Select the cell where you want the formula to appear
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)
To accept the formula, press [Enter]
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:
Cell Reference |
Range Name Reference |
=C24*5000 |
=INC*5000 |
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.
To speed up data entry time, you can choose the Manual Calculation option. To choose this option, follow these steps:
From the Ribbon, select the Formulas command tab
In the Calculations group, click OPTIONS » select Manual
You may choose to use the Manual Calculation feature in two ways: with the keyboard or from the Ribbon.
Press [F9]
OR
Press [Ctrl] + [=]
Your data is updated.
From the Ribbon, select the Formulas command tab
In the Calculations group, click CALCULATE NOW
Your data is updated.