3

I'm using the Numbers app to make a database or catalogue of stuff. For example, a list of all wines I'll try this year. And for each column, I'll have the name of the wine, a rating, the kind of wine, and a few other items.

If the first row of my spreadsheet is a header, and the first row is 1, then every item in the spreadsheet will be off by 1. To me, this makes absolutely no sense. Can I start the spreadsheet with the first row being 0 instead of 1? Or, can I have Numbers not use a row number for the header row?

If I could name the columns, this wouldn't matter. But since Numbers names the columns A, B, C, etc, I have to use a header row to name (or label) the columns, and this leads to the first actual item being in row 2 instead of row 1... and this leads to every single sort being off by one, since the top item is a label in the header, which gets listed as being #1.

Using column A for an index won't work unless there's a way to lock the order of column A, because the index would get sorted each time any other column gets sorted.

  • 1
    All spreadsheet applications have rows that start at 1 and columns that start at A. These are references for use in calculations and do now show when printing or even when the table is not selected. Do you need numbering? If so, why not use Column A and start numbering 1 - … at the first row that is not a header? Perhaps you can explain a bit further how everything will be off by 1? Do you have a sample screenshot to share with fake data? That may help us to understand your viewpoint. Thanks! – bjbk Jan 2 '16 at 23:31
  • Also, are you using the header for just a title area, or data field names? The row numbers do not affect the data in any way, they are just references. Cheers – bjbk Jan 2 '16 at 23:37
  • I'm using the header for column titles. I'm not doing any calculations. As I explained in my post: "I'm using the Numbers app to make a database or catalogue of stuff. For example, a list of all wines I'll try this year. And for each column, I'll have the name of the wine, a rating, the kind of wine, and a few other items." If I use Column A for numbering, the numbers will be wrong every single time I move an item (a row) up or down the list. – 2oh1 Jan 2 '16 at 23:44
  • Here's an example of a fake spreadsheet: Rows would be Wine Name, Vinyard, Year Bottled, My Rating, Extra Info. Obviously, I'd want to put that info at the top, in a header row, as labels for each column. And obviously, I don't want that row of header labels to count as #1 when I sort. – 2oh1 Jan 2 '16 at 23:50
  • 1
    You're just going to have to get past the offset issue and using a Index in column A helps. Additionally, Numbers will recognize the first Row as a Header Row when it Sorts, so that shouldn't be an issue. – user3439894 Jan 3 '16 at 0:32
4

So, this is easy to solve in Excel... I don't have access to Numbers at the moment, but I'll give you the solution I'm aware of and you can confirm whether it works in Numbers like it does Excel.

You can create a column with the following formula:

=Row() - 1

This will always give you one less than the "default" row number, which is what you are looking for. It also won't be affected by sorting.

A complete reference of functions that are available in Numbers can be found HERE, including the ROW function.

enter image description here

  • Interesting. How please? Can you edit your answer with the steps? – bjbk Jan 3 '16 at 3:06
  • @bjbk In any cell, you just enter =Row() - 1 for the value. That will give you the row value, minus one, which is what the OP is looking for. – Charlie74 Jan 3 '16 at 3:13
  • 1
    I wouldn't call a column with a formula like that an "Index". The purpose of an Index is to maintain the integrity of the record set or in this case the info in that given row especially when sorting info. If the Index doesn't sort with the rest of the record set or row then you can't maintain the integrity of the record set or row from a database perspective. I'd still have a proper Index, hide it you want, and then have a column called Item Count or something to reflect it's static info referring to the number of records/rows vs the record/row number and use that formula there instead. – user3439894 Jan 3 '16 at 17:25
-1

= row()-1 works but if you insert a line above your list it will need to be adjusted for each row added (for example, " = row() - 2 "). I recommend starting with typing the number " 1 " in a column for your list separate from the numbers on the side numbers (as recommended for the row function). Then, in the box below, type " = " then click on the box where you put the " 1 " (right above) and then type " + 1 " and then hit enter. You can do this for each subsequent box or just click on the second box, use the copy function (ctrl c or command c if you use mac), and then use the paste function (ctrl v or command v if you use mac) in the box below. And repeat. Then you can insert lines above your list and it will keep the numbers straight. DO NOT TYPE THE QUOTES - they are used to highlight what you type in.

  • 2
    Can you break this up into paragraphs – Mark Mar 4 '18 at 0:01
-1

=A2+1 works fine so long as you don't do operations such as Sort or cut and insert rows. Of course, even with =row()-1 or some variant thereof, you will have operations which will require you to fix the column using a fill down type operation. So it really depends on what type of manipulation of your data your likely to do in the future as to which is easier. If you use merged cells then the +1 method would be better since you might have two or three rows merged and the row()-1 method would key off the first row in the merged cell. Depending on how complex your header row is you could use a text box or other element which floats above the cells - and then make row one tall enough to contain your headers as well as your data - and then row 1 would be the first data row and your headers would also appear. Setting the text box formula to = would even allow you to have them be dynamic and easily referenced by pointing at the same source the text boxes point to.

You must log in to answer this question.

protected by bmike Dec 4 '18 at 13:35

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .