# Lock rows, columns and range - Google Sheet tutorial (Part 4)

##### Updated: January 14, 2020

In the previous tutorial about absolute cell reference https://www.2hac.com/help/google-sheets-tutorial-for-beginners-part-3/ ,we talk about how to lock cell. We will talk more about how to lock columns, rows and ranges in this tutorial.

• If you want to 10% sale off of total, you set formula =F4*G3 for G4

• Click on little box in the bottom right corner of G4 and drag it down

• Click on G5 cell and see the formula of G5 is F5*G4. You want to use G3 cell instead.
So we will change to F4\$G\$3* for G4 cell and drag it down again.

• The result looks good

• Next step, we want to calculate 20%, 30% and 25%. One way to do that is we can go H4 cell and lock H3 cell by entering formula =F4\$H\$3* . Similarly, you calculate the value of remaining saleoff of 20, 30, 40%

• The better way to do that we will change the original formula a little bit. The original formula of G4 is F4\$G\$3**. When you drag it down, we do not need to lock row number G because G3 will change to G5, G6. G still is the same. Therefore, we only need to lock 3 and the formula will be:

• If i drag it down, this formula still works well. But if we drag it right

• You see the problem. F4 is changed to G4. So we need to lock F

• And then drag G4 right and see the formula of H4 is =\$F4*H\$3. F is still intact. It is perfect.

• You drag G4 down to G6, G4 right to J4, J4 down J6, H4 to H6, I4 to I6, J4 to J6. You will fill table

• We calculate total saleoff of 10% by entering =SUM(G4:G6) for G9

• Click on little box at the right corner of G9 cell and drag it right to J9

• We calculate total after saleoff for G10, enter formula =sum(F4:F6)-G9 for G10

• Drag G10 right and see the formula of H10 is =sum(G4:G6)-H9. It is wrong because F move G so you need to lock range. So we will change the formula of G10 to =sum(\$F\$4:\$F\$6)-G9

• After that, you drag it right to fill the remaining cells

• Finally, you want to change 10% title to Saleoff of 10% by enter text and press Enter

• The below error occurs:

• We should use custom format for G4 by clicking on Format/Number/More format/Custom number format

• Enter text using quote

<