How do I keep Excel from automatically adding the last row to a formula?

Question

I want the last row in a range to be excluded from my formula, and the formula is typed in that way, but Excel keeps adding the last row when I input data. I've looked at formulas and proofing in Options, and I've tried unchecking several boxes, but it doesn't stop the problem.

![](https://learn-attachment.microsoft.com/api/attachments/6acac173-7d0c-4f97-b58e-3a18f763a383?platform=QnA

Answer 1

Excel does this to referring cells as rows are deleted etc

using $A$1 convention will minimize problems but several books are recommended

Thanks for answering. I didn't add or delete any rows. The number of rows remained the same, but Excel extended the formula to include the last row once I added data there. I don't know what $A$1 convention is. I did figure out a workaround by creating a formula that included all rows then subtracted the last row, but it's not really a satisfactory answer. A formula shouldn't just automatically change because I enter data in an adjacent cell.

Answer 2

Excel does this to referring cells as rows are deleted etc

using $A$1 convention will minimize problems but several books are recommended