Page 220 - DCAP101_BASIC_COMPUTER_SKILLS

Unit 11: Operations of MS Excel

Cells in other Worksheets Notes

To use a reference to a cell in another worksheet in the same workbook, use the following
format
= Sheet Name! Cell Address

= A5 * Sheet2 ! A5
Here is a formula that refers to a cell on a sheet named All Depts

= A5 *’All Depts’ ! A5
Cells in Other Workbooks

To refer to a cell in a different workbook, use the following format
= [Workbook Name] Sheet Name ! Cell Address
= [Employee.xls] Sheet 4 ! A2

If the Workbook name in the reference includes one or more spaces, enclose it in a single
quotation marks.

= A2 * ‘[Employee PF] Sheet1’ ! A2

11.2.1 Tips for Working with Formulae
Don’t Hard Code Values Use cell references or named constant rather than constant
values. Doing so makes it easier to modify and maintain the worksheet.
Using the Formula Bar as a Calculator To perform a calculation. use the formula bar
as a calculator. You might prefer to store the formula’s result rather than the formula.
To do so, press F9 followed by Enter. Excel stores the formula’s result rather than the
formula.

Making an Exact Copy of a Formula When you copy a formula, Excel adjusts its cell
references when you paste it to a different location. Sometime you may want to make
an exact copy of the formula. Here’s a step-by-step example of how to make an exact
copy of the formula in A1 and copy it to A2.
Double-click on A1 to get into edit mode.

Drag the mouse to select the entire formula.
Click on the Copy button on the Standard toolbar.
Activate cell42.

Click on the Paste button to paste the formula into cell42
Press Enter to edit mode. This copies the selected text to the Clipboard.
Converting Formulae to values Assume that range A1:A20 contains formulae that
have calculated a result and that will never change. To convert these formulae to
values:
Select A1:A20.
Click on the Copy button.

Select the Edit > > Paste Special command. Excel displays the paste Special dialog
box.

LOVELY PROFESSIONAL UNIVERSITY 213