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.

  • i need the same. i know this works fine for MS Excel. couldn't figure a way to do that on Google Spreadsheet though. – Lucas Pottersky May 18 '12 at 11:16

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
    This is one of the most useful answers I've seen on a stack exchange site. – kartikluke Feb 3 '16 at 11:00
  • unbelievable! Google Sheets (& Excel) is so powerful when using it right 🎉 – ProblemsOfSumit Mar 30 '18 at 16:41

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