Google Sheets Formulas, absolute cell reference (Part 3)

Updated: January 14, 2020

We will learn about absoulte cell reference, percentage in this tutorial.
We have a google sheet file like this:

We will format G column, hightlight from F3 to F6 column and click Paint Format button and click on G3 cell. Therefore, format from F3 column will be replicated to G3 column

  • Name G3 column as Sale off and write formula for G4 as =F4*L5

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

  • As you see, G5 and G6 value is zero. What's happen?. Because we are using relative cell reference so G5 formula is =F5*L6 and G6 formula is =F6*L7

  • You can address these issues by changing G5 formula to =F5*L5 manually. Nevertheless, it is a tedious task. Luckily, Spreadsheet has absolute cell reference approach.

  • You change G4 to formula ==F4*$L$5. We are declaring L5 as absolute cell reference. it means that we lock this cell in this formula.

  • After that, you drag G4 to G5 and G6 formula.

  • We add a Total after sale off column, and enter formula =F4-G4 for H4 cell.

  • However, we might delete G column later, so we will use absolute cell reference again. H4 formula will be =F4-F4*$L$5 or F4(1-$L$5)

  • We add another Percentage of Total column, formula of I4 is =H4/H9. You can use Decrease decimal places and Increase decimal places to change number of precision. If you want to show data as percent, click on Format as percent

  • And then you click on I4 cell and drag it down to I6 and see the DIVIDE BY ZERO issue. The relative cell reference causes this issue because I5 formula is =H5/H10 and H10 is zero.
    .

  • Solution is that we will use =H4/$H$9 for I4

  • To summarize, absolute cell reference is a very helpful tool of Google Sheet

<