Super User is a question and answer site for computer enthusiasts and power users. 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

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell).

When I used that all the cell references in the formula are incremented by 1 as you drop a row. But I have a cell range in it and I want this to stay the same for the whole series i.e. cells B1:B100, not B2:B101, B3:B102 etc because this just then starts to look at part of the column that does not contain my data range.

is there away around this? to force the feature to keep the data range the same always but just increment the criteria field which I want to increment

share|improve this question

Instead of using B1:B100 try using $B$1:$B$100

The $ symbol tells Excel not to increment the row or column when copying the cell. You can use $ for only the row (as in B$1) so that the column can increment, but not the row. So, if you only want to stop the rows incrementing, use B$1:B$100. The inverse happens if you use $B1.

share|improve this answer
Thank, I ask a guy at work today as I know he's good with spreadsheets and he told me exactly the same. Very useful that will be. Cheers – kingsoft learner Mar 7 '13 at 19:57
@kingsoft If you are happy with my answer, please accept it by clicking on the check-mark. Thanks. – hdhondt Mar 9 '13 at 10:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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