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


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.

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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