Let me start off by emphasizing that this is NOT an excel question. This is a Google Sheets question.

  1. On line 19 I have entered a formula into the cell of a google sheets spreadsheet that looks like this:

    =(E19/B5)

  2. I then drag/stretch down the formula to populate the cells below it in the same column.

  3. Here is the issue. As I drag the cell down to populate the cells below it, the following cells should automatically populate as such:

    Line 20 should have the following formula: =(E20/B5)

    Line 21 should have the following formula: =(E21/B5)

    Line 22 should have the following formula: =(E22/B5)

    Line 23 should have the following formula: =(E23/B5)

    ...

Notice how a reference to B5 should be in each cell.

Instead, this is what I get. Please understand that this is what I do not want to happen:

Line 20 incorrectly has the formula: =(E20/B5)

Line 21 incorrectly has the formula::=(E21/B6)

Line 22 incorrectly has the formula:=(E22/B7)

Line 23 incorrectly has the formula:=(E23/B8)

...

To flog a horse, all formulas should contain the reference to B5. What formula do I need to enter so that it always references B5 as I drag/stretch the formula down the column?

migrated from superuser.com Dec 5 at 21:20

This question came from our site for computer enthusiasts and power users.

up vote 1 down vote accepted

You need to "lock" the row portion of your B5 reference with a dollar sign in the row-19 version:

=(E19/B$5)

However, since the formula is repetitious, you can construct it as a single array formula without the need to drag-copy it down. If you'd like to explore that approach, share a link to your sheet (or a copy of it) here. In the meantime, the above change to the formula will allow you to drag-copy without the B5 reference changing.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.