Share via

How do I tell Excel to add 1 to the value of the previous cell ... ?

Anonymous
2023-11-29T15:11:25+00:00

Please excuse an Excel novice. I use Excel rarely and tend to forget how to do things in between uses.

I want an Excel column to keep a running count of its rows.

So I put a 1 in cell A1; then I put =A1+1 in cell A2 ... and so on down the column.

That works fine until I delete a row somewhere after the first and before the last -

then I get a bunch of bad cell references.

How can I tell Excel to compute the value of the current cell as the value of the previous cell+1 AND have Excel refer to the "previous cell" as the "previous cell" rather than a "specific cell"? So when a row is deleted, the formula continues to work for all remaining rows.

I presume that if I get the formula correct, I could add a new row somewhere after row 1 and before row n (n being the last row used); and Excel would automatically adjust the count for the new row and all subsequent rows.

Fancifully: Is there any way to have Excel "automatically" do the same thing when I add a new row after the last used row? I suppose I could do that by copying the formula down the entire column, well past the last used row, but I'd rather not see any count in rows after the last used row.

Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-11-29T17:04:15+00:00

    Now I'm thinking about recording the current date/time in column A when I add a row.

    Is there a "formula" that would do that automatically each time I entered a new row?

    I didn't phrase that quite right.

    I'd want something that recorded the date/time the row was added; not whatever the "current" date/time are.

    And if not automatic, perhaps a shortcut key combination. Or a dropdown menu to select from.

    0 comments
  2. Anonymous
    2023-11-29T16:55:27+00:00

    I want to be able to sort the data on various columns, but be able to "restore" the data to its original position: for that, I would sort on the column with the original row numbers in it.

    That won't work with a formula because you want it to both refer to the cell above it and not change its value when you sort, which is contradictory.

    Yes - I noted that too in my previous reply.

    As is often the case, once I state a problem to someone else, I discover the flaw in my plan. When it's written or spoken, it seems to become clearer than when it was just a thought floating around in my head.

    I take it there are no values in other columns that would enable sorting back to the original order?

    Now I'm thinking about recording the current date/time in column A when I add a row.

    Is there a "formula" that would do that automatically each time I entered a new row?

    0 comments
  3. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2023-11-29T16:22:52+00:00

    I want to be able to sort the data on various columns, but be able to "restore" the data to its original position: for that, I would sort on the column with the original row numbers in it.

    That won't work with a formula because you want it to both refer to the cell above it and not change its value when you sort, which is contradictory.

    I take it there are no values in other columns that would enable sorting back to the original order?

    0 comments
  4. Anonymous
    2023-11-29T16:01:29+00:00

    Since you want 1 in row 1 and 2 in row 2 and so on, you could simply use:

    =ROW()

    I tested that and it works, thanks.

    though I don't really know what the purpose of this is? (Excel already has row numbers after all ;))

    I want to be able to sort the data on various columns, but be able to "restore" the data to its original position: for that, I would sort on the column with the original row numbers in it.

    As I think of it now, it appears that when I sort, the formula will alter my original row numbers, so maybe I can't do what I want to do.

    If you make the data into a table (select all your data and press Ctrl+T), any new rows will automatically inherit the formulas as you add data.

    Does that include new rows added after the last used row?

    I've never used Tables before, so I'll check them out.

    But if I can't achieve my original goal, it seems like a table may not be any help.

    0 comments
  5. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2023-11-29T15:14:57+00:00

    Since you want 1 in row 1 and 2 in row 2 and so on, you could simply use:

    =ROW()

    though I don't really know what the purpose of this is? (Excel already has row numbers after all ;))

    If you make the data into a table (select all your data and press Ctrl+T), any new rows will automatically inherit the formulas as you add data.

    0 comments