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?

Microsoft Excel XP/X

Performing Calculations in Your Workbook

Excel performs its calculations through formulas and functions which are shortcuts for working with your document (a function is a pre-written formula). 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. The following topics will be discussed in this document:

return to topWriting 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.

Writing Formulas for Multiple Worksheets

While multiple worksheets can make working with your workbook more manageable, writing formulas that include information on different worksheets is a little complicated. However, when writing formulas, you can create a link between cells on one or more different 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 can be placed directly into the formula to complete its respective function.

Character Function
! Place between the worksheet name and cell reference
' Place around the file name and sheet name or when a sheet name contains a
space ('Budget 2001'!C4:C8)
[ ] Place around a file name
: Signifies a range (B3:C2 means B3 through C2)
+ Signifies a range (B3+C2 means B3 and C2)

The following examples assume that the worksheets Budget and Salary are located within the workbook 03Budget.xls.

Creating a Link to Another Worksheet (same file)

Excel allows you to create links to other worksheets. To do this, use the following steps:

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

  2. To create the formula, type the following:

    =function_name(worksheet_name!cell_reference)
    EXAMPLE: =SUM(Budget!C4:C8)
    EXAMPLE: (with space in sheet name) =SUM('Budget 2003'!C4:C8)

  3. Windows: To accept the formula, press [Enter]
    Macintosh: To accept the formula, press [return]

Creating a Link to More than One Worksheet (same file)

Excel allows you to create links to more than one worksheet at a time. To do this, use the following steps:

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

  2. To create the formula, type the following:

=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.

  1. Windows: To accept the formula, press [Enter]
    Macintosh: To accept the formula, press [return]

Writing Formulas for Other Workbook Files

You can create a formula to reference cells between workbook files:

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

  2. To create the formula, type the following:

=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.

=function_name('[file_path\workbook_name]worksheet_name'!cell_reference)
EXAMPLE: =SUM('[E:\Accounting\03Budget.xls]Salary'!D2:D10)
NOTE: There is no difference in the formula if your sheet name contains a space.

  1. Windows: To accept the formula, press [Enter]
    Macintosh: To accept the formula, press [return]

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 to use range names with. Refer to Working with Range Names for guidelines and instructions for assigning and adding 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

return to topCalculating Your Workbook

When you are working with large workbooks and several calculations, you may find data entry slowing down (especially on slower machines). 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 tell it to (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.

Changing to Manual Calculation

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

Windows:

  1. From the Tools menu, select Options...
    The Options dialog box will appear.

  2. Select the Calculation tab
    options dialog box, calculation tab

  3. From the Calculation section of the dialog box, select Manual

  4. Click OK

Macintosh:

  1. From the Excel menu, select Preferences...
    The Preferences dialog box appears.
    Preferences dialog box

  2. Select Calculation

  3. From the Calculation section of the dialog box, select Manual

  4. Click OK

Calculating Your Workbook Manually

To use the Manual Calculation feature, follow these steps:

Menu Option:

Windows:

  1. From the Tools menu, select Options...
    The Options dialog box will appear.

  2. Select the Calculation tab

  3. In the Calculation section of the dialog box, click CALC NOW (F9)

  4. Click OK

Macintosh:

  1. From the Excel menu, select Preferences...
    The Preferences dialog box appears.

  2. Select Calculation

  3. In the Calculation section, click CALC NOW (Cmd + =)

Keyboard Option:

Windows:

  1. Press [F9]

Macintosh:

  1. Press [Command] + [=]
Excellence. Our Measure. Our Motto. Our Goal.