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 its calculations through formulas and functions (a function is a pre-written formula) which are shortcuts for working with your document. For example, rather than writing a formula to add numbers, count them, and divide to find the average, you can just use the Average function. It tells the computer what it needs to do to find the average of the numbers, so you do not need to type in all of the commands to find an average. This document has been designed to examine the usage of formulas as well as provide some existing formulas to make working with your workbook and worksheets faster.
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.
While multiple worksheets can make working with your workbook more manageable, writing formulas that include information on different worksheets is more complicated. However, when writing formulas, you can create a 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 can be placed directly into the formula to complete its respective function.
Character | Use within Formula |
---|---|
! | Place between the worksheet name and cell reference |
' | Place around the filename and sheet name or when a sheet name contains a space ('Budget 2001'!C4:C8) |
[ ] | Place around a filename |
: | Signifies a range (B3:C2 means B3 through C2) |
+ | 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 03Budget.xls.
Excel allows you to create links to other worksheets.
Select the cell where you want the formula to appear
To create the formula, use this format: =function_name(worksheet_name!cell_reference)
EXAMPLE: =SUM(Budget!C4:C8)
EXAMPLE: (with space in sheet name) =SUM('Budget 2003'!C4:C8)
To accept the formula, press [Enter] or [return]
Excel allows you to create links to more than one worksheet at a time.
Select the cell where you want the formula to appear
To create the formula, use this format: =function_name(worksheet range!cell_reference)
EXAMPLE: =SUM(Budget:Salary!D2:D10)
EXAMPLE: (with space in sheet name) =SUM('July 2003:June 2004!'C4:C8)
NOTE: Be sure to include a plus sign (+) or colon (:) between the worksheet range.
To accept the formula, press [Enter] or [return]
You can create a formula to reference cells between workbook files.
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(‘[03Budget.xls]Salary’!D2:D10)
NOTES:
If the two files you are working with are in different directories, you must include the file path in your formula.
There is no difference in the formula if your sheet name contains a space. Use the following format: =function_name('[file_path\workbook_name]worksheet_name'!cell_reference)
EXAMPLE: =SUM('[E:\Accounting\03Budget.xls]Salary'!D2:D10)
To accept the formula, press [Enter] or [return]
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 Working with Range Names.
When using a range name in your formulas, the name will replace the cell reference. For example, if the percent increase is stored in cell C24 and the cell is named INC, you can refer to the cell in a formula either way as demonstrated in the following examples:
Cell Reference |
Range Name Reference |
=C24*5000 |
=INC*5000 |
When you are working with large workbooks and several calculations, you may find data entry slowing down (especially on older computers). This slowdown occurs because each time you enter information, all formulas are recalculated, by default, in case they were being impacted by the change that you just made. To speed up data entry, you can change the calculation mode to manual so 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 change to 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:
Windows:
From the Tools menu, select Options...
The Options dialog box appears.
Select the Calculation tab
Under Calculation, select Manual
Click OK
Macintosh:
From the Excel menu, select Preferences...
The Preferences dialog box appears.
From the list on the left, select Calculation
Under Calculation, select Manual
Click OK
To use the Manual Calculation feature, you have two options: the keyboard or Options dialog box.
Windows: Press [F9]
Macintosh: Press [command] + [=]
Windows:
From the Tools menu, select Options...
The Options dialog box appears.
Select the Calculation tab
Under Calculation, click CALC NOW (F9)
Click OK
Your data is now updated.
Macintosh:
From the Excel menu, select Preferences...
The Preferences dialog box appears.
From the list on the left, select Calculation
Click CALC NOW (CMD+=)
Your data is now updated.