Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from superuser.com Jun 28 '12 at 7:05

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

marked as duplicate by Al E., phwd Sep 17 '12 at 11:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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)

share|improve this answer
This is one of the most useful answers I've seen on a stack exchange site. – kartikluke Feb 3 at 11:00

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.

share|improve this answer