0

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

1 Answer 1

2

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.

2
  • 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 Mar 7, 2013 at 19:57
  • @kingsoft If you are happy with my answer, please accept it by clicking on the check-mark. Thanks.
    – hdhondt
    Mar 9, 2013 at 10:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .