How to disable auto formula update in Excel

[Aarti C]

Hi friends, I have a number of multi-year spreadsheets for collecting and analyzing monthly costs for a small business. Every month, I delete the oldest month and insert a new month (delete a row or column and add a new one at the opposite end of the spreadsheet). But recently Excel, however, automatically changes our formulas (total cost, average cost, total one year ago, etc.) because the data moved on the spreadsheet; and we have to manually update all the formulas. Is there any way to stop auto formula update? If yes then please let me know

[JyotiT]

Include an extra blank row/column in your formula, this way, when deleting the last row/column on data; the actual last row/column will not be affected. This is one of the ways that you can use to avoid this thing in Excel.

[BillieJ]

Here's a suggestion - try using =offset () in your formula.

For example:
If your data has column headings in row 1 add a new row above it. Then in cell A1 enter =count ($a: $a)-2 (the -2 is to account for the cell itself and the heading) this will give the number of rows of data and will update when rows are added or deleted.
For your formulae you then use this number of rows in an offset function =offset (reference,rows,cols,height,width) so if column B has monthly sales starting in B3 the last 12 months total is: =SUM(OFFSET(B2,$A$1,,-12)) No matter how many rows are added or removed this will always sum the last 12 rows

hope it helps