How to always show line numbers and column letters

Hi ozRob,

You will have to devote a column and a row to get most of wht you want.

ROW() will return the row number of the cell it is in. If you delete a row, it will recalculate. I filled down in column A.

Unfortunately, for the columns we do not have as robust a solution. Above, I typed "B" in B2 and filled across. As long as I do not delete a column everything will be great.

The white space around your table is not there as decoration. It is a canvas that will receive additional tables if you want to have more than one on a sheet. It will expand to accommodate as you add tables.

quinn

Just as a follow up to what Quinn said, there is, in fact, a solution equally robust as what he used for row labels, just not a simple one. It’s a bit more complicated and requires additional tables, but you can just copy my formulas, and hide those tables behind your main table (you’ll want to hide some rows in one of them, as it is very long). 

First, I set up two additional tables (on the left), one with 26 rows and 3 columns, the other with 255 rows and 2 columns.  In the first table (26x3), I enter numbers and letters as shown. You can just enter the first few and fill down until the final row, which you will have to enter manually as it deviates from the pattern as you can see. 

Then, in my 255x2 table, I enter numbers from 1 to 255 in the first column.  Again, use auto-fill for this. In the second, use this formula: 

A1=INDEX(Table 1::A:B,MATCH(QUOTIENT(A1−1,26),Table 1::A),2)&INDEX(Table 1::A:C,MATCH(MOD(ROW(),26),Table 1::A),3)

Fill that all the way down to 255. You’ll know it worked correctly if the text “IU” appears in row 255.

Then, in the main table, in the first column (Row labels) use ROW() as Quinn suggested.  In the first row (column labels), use this formula:

B1=INDEX(Columns::$A:$B,MATCH(COLUMN(),Columns::$A,1),2)

Note that because the first row and column are used for labels, your first non-label cell in the table is B2. If you want this marked as AI, then your formulas would be this:

In the first column, for your row labels, use the formula:

=ROW-1

In the first row, for your column labels, use the formula:

=INDEX(Columns::$A:$B,MATCH(COLUMN()-1,Columns::$A,1),2)

This is a lot of hassle, but it will work just fine even if you remove or add columns later.

Just as a follow up to what Quinn said, there is, in fact, a solution equally robust as what he used for row labels, just not a simple one. It’s a bit more complicated and requires additional tables, but you can just copy my formulas, and hide those tables behind your main table (you’ll want to hide some rows in one of them, as it is very long). 

First, I set up two additional tables (on the left), one with 26 rows and 3 columns, the other with 255 rows and 2 columns.  In the first table (26x3), I enter numbers and letters as shown. You can just enter the first few and fill down until the final row, which you will have to enter manually as it deviates from the pattern as you can see. 

Then, in my 255x2 table, I enter numbers from 1 to 255 in the first column.  Again, use auto-fill for this. In the second, use this formula: 

A1=INDEX(Table 1::A:B,MATCH(QUOTIENT(A1−1,26),Table 1::A),2)&INDEX(Table 1::A:C,MATCH(MOD(ROW(),26),Table 1::A),3)

Fill that all the way down to 255. You’ll know it worked correctly if the text “IU” appears in row 255.

Then, in the main table, in the first column (Row labels) use ROW() as Quinn suggested.  In the first row (column labels), use this formula:

B1=INDEX(Columns::$A:$B,MATCH(COLUMN(),Columns::$A,1),2)

Note that because the first row and column are used for labels, your first non-label cell in the table is B2. If you want this marked as AI, then your formulas would be this:

In the first column, for your row labels, use the formula:

=ROW-1

In the first row, for your column labels, use the formula:

=INDEX(Columns::$A:$B,MATCH(COLUMN()-1,Columns::$A,1),2)

That formula I showed above looks pretty intimidating, but really all it does is generate a 2 character string for our lookup table that corresponds with the column names, from A to IU, without having to compile the list manually. Most users never need to go all the way to 255, but I like to be thorough.

As the work has already been done, you can access it yourself here: https://www.icloud.com/numbers/0ktoC40fSnqcJqmcS7kzGLQDA#Column/Row_Labels

Export, and save as a template. Copy that column with the ugly formula and paste values. You can then safely delete the 26x3 table, as it was simply a lookup table to allow us to build another lookup table. You won’t need to use it again.

Hi Bismarck,

You inspired me to figure out a way myself. I liked your lookup table idea.

I started with a single column table with 255 rows. Called it "Column lookup"

I filled A down to Z (row 26)

A27=INDEX(A,QUOTIENT(ROW(cell),27),1,area-index)&RIGHT(A1,1)

this fills down the rest of the column.

QUOTIENT is a new function for me. I will try to remember it in the future.

Once this column is complete, we can copy and Paste Values back into the column to eliminate the formula.

This table can be hidden in another sheet.

On the working table:

A1=INDEX(Column lookup::$A,COLUMN(cell))

This is filled across.

Adding/deleting columns no longer an issue.

Saving as a template is the way to go.

quinn

Numbers undeniably allows for the creation of the most visually pleasing spreadsheets around. In this area it has an advantage over its competitors, and little things like you describe are core to its design and, in true Apple fashion, are not customizable.

That being said, you could always reserve Row 1 and Column A for Row and column labels, in whatever font, colour, or size you want, if having this information available is important to you. You could also add text to the canvas, so a not to occupy a cell, and lock it in place. There are ways around just about any limitation if you’re determined.

ozRob wrote:

What do I have to do to make the row numbers and column letters always visible without having to put focus in a cell?

For the numbers to be visible you need to have the focus on that table. That can be done either by clicking a cell in the table or selecting the table by clicking the canvas near it and dragging your cursor over part of the table.

Note that Numbers is designed to easily allow you to have multiple tables in the same sheet (check out the templates at File > New for great examples of this). If you do have multiple tables in the same sheet then you will of course get different column lettering and row numbering depending on which table you select. At least for me that's much more visually appealing than having the clutter of column letters and row numbers always visible in every table on the same sheet.

SG

That one formula looks pretty intimidating, but really all it does is generate a 2 character string for our lookup tabld that corresponds with the column names, from A to IU, without having to compile the list manually. Most users never need to go all the way to 255, but I like to be thorough.

As the work has already been done, you can access it yourself here: https://www.icloud.com/numbers/0ktoC40fSnqcJqmcS7kzGLQDA#Column/Row_Labels

Export, and save as a template.

That formula produces the same result with 4 functions as mine does with 7. Brilliant. 👌

Thanks, Bismarck.

QUOTIENT was key.

quinn