19

Possible Duplicate:
'Lock' part of formula when copying from one cell to another

Is there a way to control which variables in a formula get incremented when you use auto-fill in a Google Docs spreadsheet?

What I'm trying to do is convert a column of counts to a column of proportions. So, for the first cell in the proportions columns, I wrote "=E2/sum(E2:E17)". When I drag down the box in the lower right corner to auto-fill the other cells in the column with the appropriate formulas, I get the unintended effect that "sum(E2:E17)" does not stay constant -- in the next cell it is "sum(E3:E18)" and so on. What I'm looking for is a way to make auto-fill leave that part of the formula unchanged.

Improve this question
1
  • i need the same. i know this works fine for MS Excel. couldn't figure a way to do that on Google Spreadsheet though. Commented May 18, 2012 at 11:16

2 Answers 2

Reset to default
38

You should use the magic $ sign ;)

If you want that the column stays sticky, do something like sum($E2:$E17)

If you want that the row stays sticky, do something like sum(E$2:E$17)

If you want that both row and column stay sticky, do something like sum($E$2:$E$17)

Improve this answer
2
  • 3
    This is one of the most useful answers I've seen on a stack exchange site. Commented Feb 3, 2016 at 11:00
  • unbelievable! Google Sheets (& Excel) is so powerful when using it right 🎉 Commented Mar 30, 2018 at 16:41
1

You can define an alias for a range in Google Spreadsheets » Data » Mange Ranges.

Then use that alias in a formula instead of XX:YY and then auto-fill won't mess it up.

Improve this answer