9
1
This is really a cosmetic question. Excel by default starts counting from line one which is the header. Is there some way to have it start counting from the real line one (the line after the header). In the attached screenshot socks is row one but since the header is counted it has become row two.
Gullu
Posted 2011-08-01T20:52:38.070
Reputation: 838
12
Simply put, the answer is no; that feature does not exist in Excel.
If you needed it for some reason, you could easily implement your own ID column and populate the cells with the numbers you want represented.
Like so:
Id Description 1 Socks 2 Boxers 3 Shoes
Then, you simply removing the column and row headings. That way you get the illusion that the rows are starting with your data.
Just a thought.
-Justin
Justin Self
Posted 2011-08-01T20:52:38.070
Reputation: 236
1
This formula worked for me. I inserted a new column for A in my spreadsheet, then used this formula in cell A2:
=ROW(2:2)-1
Then I went to "Page Layout" to uncheck View Headings, as discussed in previous answers.
This formula keeps the row numbers accurate when sorting, and does not count the first row for the headings.
Bill Cooksey
Posted 2011-08-01T20:52:38.070
Reputation: 11
(1) You don't need ROW(2:2); all you need is ROW(), at least in current versions of Excel. (2) The use of ROW() in formulas can be a useful trick, but why do you think you need it for sorting? Just don't select Column A when you sort. – Scott – 2016-04-16T00:11:27.873
0
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!
user522468
Posted 2011-08-01T20:52:38.070
Reputation: 1
This is not an answer to what was asked in the question. It appears to relate to adding a column that you use instead of Excel's row numbers and subtracting 1 for the header row. However, it is not even written in an actionable format. Charlotte's answer from a year ago already covered this, and provides an understandable explanation. This post doesn't add anything useful to the discussion. – fixer1234 – 2015-12-05T16:33:23.207
Actually, Charlotte's answer is a pale shadow of Justin Self's answer.
– Scott – 2016-04-15T23:52:14.7900
I just tried to perform this and this is the first forum I came across after searching "microsoft excel how to start row numbers from a different row" in Google. This might be what you're after, which is acceptable for my spreadsheet for personal use.
Basically, I doubled the row hight of the first row and ticked the box "wrap text" in format options for my used cells in the first row. I then entered spaces after the word which I wanted to be the header for each column so they dropped down to the second line (sitting below my title text) and made them bold and underlined like your example. The result was the row number sat in-line with the second line of text in the first row cells, in your case, "Socks". All that is missing is a cell number and gridline but the result is almost there. Adam
Adam
Posted 2011-08-01T20:52:38.070
Reputation: 11
1So, if I enter =A2 and =B2 somewhere else in the sheet, I will get Pants and 2, but if I enter =A1 and =B1, I will get Description (newline) Socks and Quantity (newline) 3? This would break the spreadsheet if it is used as a spreadsheet, rather than just a text file that happens to have rows and columns. – Scott – 2013-03-12T18:42:42.533
Yes, hence why I stated it is just a quick fix and only acceptable, in my case, for personal use. – Adam – 2013-03-12T18:50:50.423
0
I made my left hand column start with one as number one of the actual first row. Then I unchecked the 'view headings' box in the page layout view. That took out the left hand auto numbered column and the ABC etc out of the header.
cl
charlotte
Posted 2011-08-01T20:52:38.070
Reputation: 1
This is little more than the accepted answer, from three years ago. In fact, this is less.
– Scott – 2016-04-15T23:49:34.790
ok. Never used superuser. Will look into that site. Moderator please delete this question if you think it is useless. thanks – Gullu – 2011-08-19T13:03:59.393
It isn't useless -- it's off topic. – Jean-François Corbett – 2011-08-19T13:52:07.130
This functionality is unlikely to have in Excel, IMO. It could only eventually create problems for less experienced users without adding a great payback value. – Tiago Cardoso – 2011-08-01T22:52:56.870
@Issun, Dont think in terms of List starting at A2. In 99% of cases the first line in excel is always the header. Items starts from A2 so ideally MS should provide some checkbox option somewhere that says something like "First row is header" which will put rows numbers starting with one from A2 row.. thanks – Gullu – 2011-08-02T02:35:48.840
@Gullu, I'm not sure about your 99% percent of headers in row 1... I'd say that I've seen many spreadsheets with headers coming into rows 2 or 3, keeping row 1 as padding. Again, there's no rule about row 1 contain in 99% of cases a header. Please, feel free to prove us we're wrong. – Tiago Cardoso – 2011-08-02T03:03:57.537
1@Tiago, As I stated in my question this is really a cosmetic issue. If multiple lines span the header dont you think it makes sense to have some input in options which says "enter no of lines in header" which will put row numbers starting at one skipping header lines. I know there is no such thing and looking for a trick or hack if someone has done it before. thx – Gullu – 2011-08-02T13:03:46.763