1

The question How to get excel row numbers to skip the header asks about how to change the way Excel numbers its rows. The author's usual practice was to use Row 1 for column headers and wanted to have Excel start numbering the rows from Row 2 (labeled as "1").

User522468 posted this answer:

Old-fashioned I know, but I just used the "row" function and added minus 1: (A2:A93)- 1 that's worked for numbering my rows anyway!

So I entered: =ROW(A2:A93)-1, into cell A1 and all that happened was A1 showed the numeral 1.

I'd like to know if I did something wrong in implementing user522468's answer.

Community
  • 1
Lachlan Stewart
  • 23

2 Answers2

0

I'll post an answer just to provide closure. Yes, you did something wrong in implementing that answer--you tried to implement that answer.

It appears that the answer's author misunderstood the question; the answer was not a solution to what was asked, and was not in an actionable form. The answer shows pseudo-code for the generic concept of subtracting 1 for row calculations to adjust for the missing header row. There really isn't anything useful you can do with that answer as it was written.

Our apologies for having wasted your time pursuing this.

fixer1234
  • 28,064
0

As mentioned, typing that literally would not be something you SHOULD do. However, it actually would work typed literally as stated. More on that in a moment.

First though, the real problem is a bit different. For the thing you are trying to do, you do NOT enter anything at all into cell A1. Cell A1 will either have the chosen header name for this column of sequential numbers, or perhaps no header at all. But it does not get one of these entries ever, for any reason at all, unless you want it to read "0" ("zero") which seems rather unlikely.

The reason you got "1" when entering it in cell A1 is that many formulas in Excel WILL accept a range (like A2:A93) in their arguments, but will only act on what could be called the "upper left corner" of the range, just that single cell. It will always be the first cell shown in the range, so here it read what you typed and said "Nah. I'll just take 'A2' from what he typed and go with that. Won't tell him anything about ignoring the rest either..." and the row number for cell A2 is 2 and subtracting 1 gave you the "1" you saw.

So, that taking of just the starting cell for the range is why it would actually work if you entered it in A2, then copied A2 and pasted down your column. It would change the A2:A93 to A3:A94, A4:A95, and so on. And so the cell it was in would always be the first cell mentioned in the range given and so it would always be using the correct cell for this work. So... it'd be wrong to do, really, and a more complex version would lead to troubles, probably, or typing this in cell A3 to begin... lots of reasons not to do it, even though it would have, funnily enough, worked out this time.

So type just the following (literally) in any cell in your column EXCEPT the first one:

=ROW() - 1

Any column. Start in any row, except Row 1 where you have column headers anyway.

Copy the cell after hitting Enter and paste it down as far as you like. (If you are creating an actual "Table" it will do that itself.)

As a sidenote, you subtract the number of rows above your starting point. In this case, that was one row so you subtracted 1. If you had, oh, four rows above your starting point for the row numbers, you'd subtract 4 instead.

So the reason it did not work was you tried to put it into the ONLY cell in your column you couldn't. But you wouldn't have wanted to either!

(If you DID want a zero in Cell A1, you can put the formula I showed a moment ago in A1 also and it will give you a "0".)

Roy
  • 11