Q&A

Question:

Are we able to lock only certain cells instead of the whole worksheet?

 

Answer:

Yes. Here is how:

1) Select the cell or range you want to unlock

2) right click the mouse button

3) Select "Format Cell"

4) Look for the tab "Protection"

5) Uncheck the option "locked"

6) Protect the worksheet

 

You can now edit those selected cell/ranges when the sheet is protected.

 

 

How to use the offset function (Part II)

Offset function can be used to sum up a range. So what if it can sum up ranges? It means that you could do wonderful things with this. For example, with the offset function, I could reduce the calculation of depreciation to a single line. Without it, you would need to a worksheet just to calculate depreciation. First let me give you the basic introduction, continue from yesterday.

 

Enter this formula "=SUM(OFFSET(A1,0,0,4,4))" say in row F2. It will show the results as 9. Excel will sum up the numbers appearing in A1 to D4 since we specific in the offset function that it should add up from cell A1 (Offset(A1,0,0,4,4) and 4 columns to the right (A1,0,0,4,4) and 4 rows down (A1,0,0,4,4). If you were to change the formula to 3 rows down, like this, (A1,0,0,3,4), you will get the results 5.

 

	A	B	C	D
1	5
2
3
4				4

 

Now that you have learnt how to sum up the range using offset, here is the application. When you calculate depreciation, you need to calculate the residual value of your fixed asset. In this case you can apply the offset function to the row on Depreciation. THe formula is "=SUM(OFFSET(D11,0,0,1,-4))/4". The offset function will sum up the net asset value of the last 4 years in row 11.

 

9	C	D	E	F	G
10		Year 1	Year 2	Year 3	Year 4
11	Asset Value	10,000
12	Depreciation	2,500	2,500	2,500	2,500
13	Accumulated Depreciation	2,500	5,000	7,500	10,000

 

Some of you must be wondering, I could be using relative cells and it will sum up the value in the last 4 cells. Same as what offset is doing. You are right if yo have thought of that. Then consider this. I will now change the formula to reference the number of years of depreciation to reference to a cell (say J9). The formulat would have changed to "=SUM(OFFSET(D11,0,0,1,-J9))/4". With this, I could change the deprecaition year easily at Cell J9, Try doing this with the relative cells range you have suggested. Are you now convinced?

 

 

 

 

 

How to use the offset function

Offset Function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. Depending on context, you can make use of it to reduce your administrative needs to update information.

 

Offset can be applied to

1) Summing a range

2) Make chart more dynamic

3) Create a always updated validation listing

4) Update the range for pivot table

 

In its simplest form, offset could be used to return the value in a cell. e.g

 

	A	B	C	D
1	5
2
3
4				4

 

If you apply the fomula offset =OFFSET(A1,3,3,1,1), you will reference from A1, count 3 rows down and 3 rows to the right and reference only one row and one column. The result is 4.

 

If you apply the fomula offset =OFFSET(D4,-3,-3,1,1), you will reference from D4, count 3 rows up (first -ve3) and 3 rows to the left (second -ve 3) and reference only one row and one column. The result is 5.

 

The next time, I will cover summing a range.

 

 

 

1 minute Excel Challenge

Here is a challenge for you.

http://www.synergyworks.com.sg/Excel%20Test.html