Spreadsheet fill down for a formula

Question

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

Answer 1

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.