# How to add a row without changing formula in Numbers app on Mac

I have rows of data sorted by date (descending). Therefore it is important that a new row can be added above all other rows but before the "header" row.

However, when I select "Add row above" the formulas are updated. I would like the cells referenced in the formulas to not be updated. I know this is possible in Excel but is this possible in Numbers? Any help would be much appreciated.

You can accomplish this using `INDIRECT` and `OFFSET` functions.

To give you an example, let's assume that you have just one column of five data points, with the first row being the header and second row onward being the data. Now you want to sum the column up.

``````  || A
==||======
1 || Sale
2 ||  1
3 ||  2
4 ||  3
5 ||  4
6 ||  5
``````

and you want `SUM` from the first value to the last value always. For a fixed set like the one shown above, you could just do `=SUM(A2:A6)` in the `A7` cell.

But you want to be able to add rows to the top. And you expect the function to be able to sum from the first data row to the last data row just before where the formula is used. So you would use `INDIRECT("A2")` as the starting index (A2 doesn't change even if you add a new row), and `OFFSET(A7, -1, 0, 1, 1)` as the last index (A7 will change to A8 if a new row is added, and the offset will make sure that you're still summing until one cell above A8).

``````=SUM(INDIRECT("A2"):OFFSET(A7,-1,0,1,1))
``````

Now, even if you add a new row at the top, your formula will work just fine.

I hope this helps.

While you tagged it mac, I haven't tried a spreadsheet on a mac, but you've also tagged your question microsoft excel so i'll answer for microsoft excel. Try sticking a dollar sign before any letter or number that you want to remain constant e.g. to make `a` or `1` constant for `a1`, try `\$a1` or `\$a\$1` or `a\$1`

• Thanks for your response @barlop. I've tried this but unfortunately adding a row still updates the formula. And yes, I tagged Excel because I know there is common functionality this is a more "fundamental" spreadsheet question than it is about Numbers specifically. Commented Oct 25, 2016 at 18:18
• I don't see an add header row in excel but there are options to spread a formula across or to spread it down..e.g. highlight the cell and some cells to the right and do ctrl-r it edit..fill..right or highlight the cell and some cells below and do edit..fill...down or ctrl-d. or clicking the little box at the bottom right of any selected cell and dragging, copies the formula. Or just clicking a cell doing ctrl-c and clicking another and doing ctrl-v to copy/paste.Then if a cell has the value =\$A\$2 then whether copying that formula across or down, or just to another cell, it remains the same. Commented Oct 26, 2016 at 2:27
• you have to have the dollar before both the letter and the number, to ensure both remain constant Commented Oct 26, 2016 at 2:30
• I understand @barlop, thank you for your thoughts however when prefacing both (e.g. \$A\$2) and adding a new row above (which I imagine is a very common practice), the formula still gets updated. Very frustrating. Commented Oct 26, 2016 at 18:17
• @JohnDoe I just tried selecting an entire row and right clicking and left clicking insert, and it inserted a row above and \$A\$2 remained so. Maybe you'd find it works in excel and not on your mac.. Have you tested it in excel? Commented Oct 26, 2016 at 21:10