Return values from the row above to the current row

Question

I am trying to do simple thing: I just need to set some cells to values that are on the previous rows. I have tried =A(Row()-1) but it does not work.

score 101 · Accepted Answer · edited Sep 13 '21 at 00:48

101

To solve this problem in Excel, usually I would just type in the literal row number of the cell above, e.g., if I'm typing in Cell A7, I would use the formula =A6. Then if I copied that formula to other cells, they would also use the row of the previous cell.

Another option is to use Indirect(), which resolves the literal statement inside to be a formula. You could use something like:

=INDIRECT("A" & ROW() - 1)

The above formula will resolve to the value of the cell in column A and the row that is one less than that of the cell which contains the formula.

edited Sep 13 '21 at 00:48

pevik

4,523
3
33
44

answered Aug 23 '10 at 15:36

Ben McCormack

32,086
48
148
223

"Then if I copied that formula to other cells": it's the simple things, sometimes… I've been looking for that answer for hours. The most implicit is always the harder to find. So there you go, that's how you copy the same formula in a row to process the data from the previous row. Great! – Clément Dec 08 '16 at 17:52
Just adding more details for the noobs: Apply the formula to one cell and just drag the fill handle (the small square dot at the bottom right corner of the cell) and drag it down to apply to all the required cells. – Zeeshan Aug 22 '17 at 16:34

kevinbatchcom · Answer 2 · 2020-02-25T18:02:30.330

43

This formula does not require a column letter reference ("A", "B", etc.). It returns the value of the cell one row above in the same column.

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

edited Feb 25 '20 at 18:02

answered Aug 20 '16 at 16:36

kevinbatchcom

587
5
9

1

I was able to create row indexes (numbering) by typing a literal "1" in the first row, then using the formula above, modified with a "+ 1" in the the rest of the fields directly below. It works perfectly! `=INDIRECT(ADDRESS(ROW()-1,COLUMN())) + 1` – Eric Hepperle - CodeSlayer2010 Mar 25 '17 at 14:35
2

Thanks. This saved my day. – Jagz W Jun 27 '18 at 02:32

score 7 · Answer 3 · edited Jan 04 '17 at 12:11

7

You can also use =OFFSET([@column];-1;0) if you are in a named table.

edited Jan 04 '17 at 12:11

FelixSFD

6,052
10
43
117

answered Jan 04 '17 at 12:10

Andre Morata

307
3
12

score 4 · Answer 4 · answered Jan 12 '15 at 12:18

I followed BEN and Thanks and Lot for the Answer,So I used his idea to get my solution, I am posting the same hence if some one else has similar requirement, then you also can use my solution as well, My requirement was something like I want to get the sum of entire data from the first row to the last row, and I was generating the spreadsheet programmatically so don't and can't hard code the row names in sum as the data is always dynamic and number of rows are never constant. My formula was something like as follows.

=SUM(B1:INDIRECT("B"&ROW()-4))

please, consider editing your answer so it would only answer the original question (many details could easily be omitted) — arghtype, Jan 12 '15 at 12:43

score 1 · Answer 5 · answered Aug 20 '16 at 19:05

1

Easier way for me is to switch to R1C1 notation and just use R[-1]C1 and switch back when done.

answered Aug 20 '16 at 19:05

Slai

22,144
5
45
53

Return values from the row above to the current row

5 Answers5

Linked