Microsoft Excel 2008: Performing Calculations in Your Workbook (Mac)

Performing Calculations in Your Workbook

Excel 2008 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.

• Writing Formulas for Your Workbook
• Calculating Your Workbook

 

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.

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 more complicated. However, when writing formulas, you can create a 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 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.

Creating a Link to Another Worksheet (same file)

Excel allows you to create links to other worksheets.

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)
   EXAMPLE: =SUM(Budget!C4:C8)
   EXAMPLE: (with space in sheet name) =SUM('Budget 2003'!C4:C8)
3. 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.

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

3. 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, 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)

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

Calculating Your Workbook

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.

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 Excel menu, select Preferences...
   The Excel Preferences dialog box appears.
   
2. In the Formulas and Lists section, select Calculation
   The Calculation dialog box appears.
   
3. In the Calculate sheets section, select Manually
4. Click OK

Calculating Your Workbook Manually

To use the Manual Calculation feature, you have two options: the keyboard option or the menu option.

Keyboard Option:

1. Press [] + [=]

Menu Option:

1. From the Excel menu, select Preferences...
   The Excel Preferences dialog box appears.
2. In the Formulas and Lists section, select Calculation
   The Calculation dialog box appears.
3. Click CALC NOW (CMD+=)Your data is now updated.

Make sure to let me know in the comments below or on our Facebook page how you've got with it or Do you have any questions