Page 237 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

You don't need a sheet notation if you're referring to a cell in the same worksheet.
Similarly, when you reference data in a different workbook, the name of the workbook and the worksheet are
included in the reference along with the cell location.
To reference a cell on a different sheet, preface the cell reference with "Sheet [number]" with an exclamation point
after it, and then the name of the cell. So if you want to pull info from Cell A1 in Sheet 3, you'll type, "Sheet3! A1."
A notation referring to another workbook in Excel also includes the name of the book in brackets. To use the
information contained in Cell B2 in Sheet 2 of Workbook 2, you'll type, "[Book2] Sheet2!B2."
Cell Range: A Quick Primer

While references often refer to individual cells, such as A1, they can also refer to a group or range of cells. You
identify ranges of cells by the starting and ending cells. In the case of ranges that occupy multiple rows and
columns, you'll use the cell references of the cells in the upper left and lower right corners of the range.
Separate the limits of a cell range with a colon ( : ), which tells Excel or Google Sheets to include all the cells
between these start and end points. So to grab everything between Cell A1 and D10, you'd type, "A1:D10."
To capture an entire row or column, you still use the cell range notation, but you only use the column numbers
or row letters. To include everything in Column A, the range will be "A:A." To use Row 8, you'll type, "8:8." For
everything in Columns B through D, you'll type, "B:D."
Copying Formulas and Different Cell References
Another advantage of using cell references in formulas is that they make it easier to copy formulas from one
location to another in a worksheet or workbook.

Relative cell references change when copied to reflect the new location of the formula. The name relative comes
from the fact that they change relative to their location when copied. This is usually a good thing, and it is why
relative cell references are the default type of reference used in formulas.
At times, cell references need to stay static when formulas are copied. Copying formulas is the other major use of
an absolute reference such as =$A$2+$A$4. The values in those references don't change when you copy them.
At other times, you may want part of a cell reference to change, such as the column letter, while having the row
number stay static or vice versa when you copy the formula. In this case, you'll use a mixed cell reference such
as =$A2+A$4. Whichever part of the reference has a dollar sign attached to it stays static, while the other part
changes when copied.
So for $A2, when it is copied, the column letter is always A, but the row numbers change to $A3, $A4, $A5, and
so on.
The decision to use the different cell references when creating the formula is based on the location of the data
that the copied formulas will use.

Toggling Between Types of Cell References
The easiest way to change cell references from relative to absolute or mixed is to press the F4 key on the
keyboard. To change existing cell references, Excel must be in edit mode, which you enter by double-clicking on
a cell with the mouse pointer or by pressing the F2 key on the keyboard.
To convert relative cell references to absolute or mixed cell references:
• Press F4 once to create a cell reference fully absolute, such as $A$6.
• Press F4 a second time to create a mixed reference where the row number is absolute, such as a$6.

• Press F4 a third time to create a mixed reference where the column letter is absolute, such as $A6.
• Press F4 a fourth time to make the cell reference relative again, such as A6.

224

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77