Tips and Shortcuts 33 34 pg 218 219 220

33. Highlighting cells with formula

Sometimes, we want to ensure that cells that should contain formula must have formula in it. We may forget to copy down formula, for example. With the following steps, we can see formulas in the cells with formula, so we can quickly look for cells without formula. Check whether that cell should contain a formula.

Click menu Tools/Options. Click tab View in the dialog box that appears. Put a mark in the box at left of label Formulas. Click OK. You should see all formulas in cells with formulas. Review the cells without formulas to check whether they should contain formulas.

Once done, click menu Tools/ Option again. And click tab View. This time, clear the box on left of label Formulas. Click OK. You will see figures instead of formulas in the relevant cells.

34. Making sure cell address does not change when copying formula

Say we have the following data:

	A		B		C
1	20		15 		30
2	30		25 		40
3	40 		35 		10
4

In cell A4, we will write a formula =SUM(A1:A3) to get a total of the amounts in cells A1 to A3. If we copy the formula to cell B4, the formula in cell B4 will automatically become =SUM(B1:B3) which will get the total of the amounts in cells B1 to B3. If we copy the formula in cell A4 or B4 to cell C4, the formula in cell C4 will automatically become =SUM(C1:C3) which will compute the total of the amounts in cells C1 to C3.

218

The automatic change in cell address in a formula is fine for the above situation. But consider the following data:

	A		B		C
1	Product		Sales-RM 	%
2	Item A		25
3	Item B		35
4	Item C		10
5	TOTAL

70

In cell C2 we will calculate the percentage of sales value of Item A. We use this formula: =(B2/B5)*100 . If we copy this formula to cell C3, the formula in cell C3 will become = (B3/B6)*100. If we copy this formula to cell C4, the formula in cell C4 will become = (B4/B7)*100. The formulas in cell C3 and C4 are not what we want because it refers to row 6 and 7 in column B instead of row 5.

To make sure the row number in cell address B5 in the formula in cell C2 remains as is when we copy the formula to cells C3 and C4, put a dollar sign ($) in front of the row number in cell address B5 in the formula in cell C2, like this: =(B2/B$5)*100 before we copy.

If we the copy the formula, the formula in cell C3 would appear correctly as =(B3/B$5)*100 and the formula in cell C4 would appear correctly as =(B4/B$5)*100 .

Here we learn that by putting a $ sign in front of a row number of a cell address in a formula, the row number will not change when we copy the formula to another cell. This is useful in case like above.

If we put a $ sign in front of a column reference of a formula, e.g. $B5, the column reference will not change when we copy the formula to another cell in different column.

219

If we do not want either the column reference or the row number to change when we copy a formula, put $ sign in front of both, eg. $8$5.

An alternative of using $ sign is to use INDIRECT function. Just replace the $ sign with word INDIRECT, eg.

INDIRECT("B")&5		This is equivalent to $B5
INDIRECT(B&"5")		This is equivalent to B$5
INDIRECT("B5")		This is equivalent to $8$5

220

How to name a cell