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/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

<