• Drag Down Cell in Google Sheets

    • This topic has 5 replies, 3 voices, and was last updated 1 month ago.



    Hi all,

    I suspect the solution to what I want to do is simple, but I can’t figure it out, and Google searches didn’t help.

    Take a look at the attached screenshot from one of my Google Sheets. I manually fill in column B; Google auto calculates column C. When I add a new row, I want to drag column C down to auto calculate the new cell. Simple, right?


    Here’s the problem: Only 1 of the 2 things must change in each row. So, if I add a row below Microsoft, add $2,500 for the amount, then drag 23% down, I get “#DIV/0!” That’s because Google wants the Microsoft “percentage” to be “=sum(B5/B9).”

    But because I want to change only 1 thing, the formula should be “=sum(B5/B8).”

    In other words: The B8 (the “total”) always stays the same; it’s the B5 (the “amount”) which changes.

    Any idea how to make this happen? Thanks a ton!

    Viewing 1 reply thread
    • #2524858

      The B8 (the “total”) always stays the same; it’s the B5 (the “amount”) which changes.

      Use a $ ahead of the cell location you want stay put.

      Solution: =sum(B5/$B8)


      1 user thanked author for this post.
      • #2535792

        Hi @TechTango,

        Many thanks for your reply! (Unfortunately, the Lounge didn’t notify me via email that you had done so; hence my delay).

        Thanks also for your answer! With a small teak — to =sum(B5/$B$8) — this did the trick!

        All the best,

      • #2537567

        Dear @TechTango,

        Hi again. May I trouble you with a follow-up question?

        In another spreadsheet, I’m trying to “drag down” the following formula:

        =sum(C16*'2022 Key'!B5)

        Do you know how I can tweak this formula, so that C16 becomes C17 but ‘2022 Key’!B5 stays the same?

        Thanks so much!

    • #2537580

      To anchor a cell you prepend the dollar ($) to either the row, column or both. This also applies to references to cells in other sheets.
      You want to stop the row count increasing on a drag down, so anchor the row number.
      =sum(C16*'2022 Key'!B$5)

      cheers, Paul

      1 user thanked author for this post.
    Viewing 1 reply thread
    Reply To: Drag Down Cell in Google Sheets

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: