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