here's how to transpose rows to columns

Hi Hershel,

While that is, as described, a 'simple' method to transpose, it's not the only one, nor is it the easiest to set up.

Numbers will auto-adjust the row reference(s) of a formula when filling down, or the column reference(s) when filling right. Unfortunately, the method described in the linked post requires adjusting the column reference of a formula as it is entered down a column. That must be done manually.

Here's a method that uses a longer formula, and a second table, but has the advantage that the formula needs to be entered only once; after that it's simply Filled Down and Filled right to place the adjusted formula in all of the other cells.

Table 1 is the original table. Table 2 needs to have as many rows as table one has columns, and as many columns as Table 1 has rows. The limit on both of these is 255, the maximum number of columns in a Numbers table. (The example uses 10x10 tables to ft easily into a page in this discussion.)

Formula (in A1 of Transposed. Copied of Filed from there to all other cells on the Transposed table.)

=OFFSET(Original :: $A$1,COLUMN()-1,ROW()-1)

Regards,

Barry

tooth,

From Barry's post:

Formula (in A1 of Transposed. Copied of Filed from there to all other cells on the Transposed table.)

=OFFSET(Original :: $A$1,COLUMN()-1,ROW()-1)

"Original" is the name of the top table (shown in Barry's post earlier in this thread) and "Transposed" is the name of the table on the bottom.

Way 1:

select cell A1 of the table named "Transposed" and paste "=OFFSET(Original :: $A$1,COLUMN()-1,ROW()-1)" without the double quotes, then select cell A1 and fill to the right, then select row 1 and fill down

Way 2:

select cell A1, copy

select all cells using the menu item "Edit > Select All", then paste

Hi toothfairy,

"When your formula contains the word "Original", which is the title of your table, what are you actually putting in the formula in that spot?"

Actually, "Original is the name of the Table. When a formula references a cell on a table other than the one containing the formula, it is necessary to include enough information to identify the table containing that cell. In this case, that means the name of the table.

From my earlier post:

Formula (in A1 of Transposed. Copied of Filed from there to all other cells on the Transposed table.)

Apologies for the sloppy typing there. The second 'sentence' should begin: "Copied or Filled from there..."

The actual formula (as copied later in Wayne's post, is exactly as shown above, provided the first table is named "Oriinal"

=OFFSET(Original :: $A$1,COLUMN()-1,ROW()-1)

@crabpaws

You've made it clear that you're going (back) to MS Excel. That's OK. In fact, it's in line with advice that has been given several times in this community: Use the tool that does the job. If your perception is that Excel is the best tool for the job you want a spreadsheet to do, then Excel is the tool you should use.

Regards,

Barry

crabpaws wrote:

Note that it's taken a half-dozen posts of explication for Barry's "simple" formula.

I looked for and couldn't find a half-dozen posts explaining the formula. That's probably because there's not much to explain there. After all, OFFSET() works the same way in Excel.

As a long-time (and continuing) Excel user and a short-time Numbers user I think there are excellent responses here. None that I can find explain a supposedly over-complicated formula. Many respond constructively to general–seemingly uninformed–statements about what Numbers can and cannot do.

The 'ocean' and 'islands' metaphor Ian cites above is really helpful to understand the differences between the two programs. Once you understand how Numbers links the islands–rather than just trying to do things the Excel way in one big ocean–the app suddenly becomes a LOT more powerful and convenient. This transpose thing is just a simple example of that.

For those reading the thread to advance and share their Numbers skills, rather than make and read provocative statements, I would point out one small thing in Barry's example at the top of the thread that could momentarily trip up those new to Numbers: the shaded cells in the "Transposed" table there probably are not "true" Numbers header row and column. If they are set up as "true" headers, then on my machine, you can't just simply fill right and down from $A$1. If your table happens to be set up with a row and column header, copying from $A$1 and pasting works just fine, though.

SG

Hi SG,

I was thinking the same thing about Header Rows and Header Columns as shown in Barry's reply. I thought that the 'Transposed' table simply copied the grey fill from the 'Original' table without carrying the Header properties. As this discussion is evolving into data copied from Excel (where Headers are not recognised) I tried this. Two plain tables in Numbers with no Headers. The 'Transposed' table uses Barry's formula and follows his rule of 'the number of columns must match the number of rows in the original' and similarly for rows to columns.

Cell A1 in the Transposed table contains

=OFFSET(Original :: $A$1,COLUMN()-1,ROW()-1)

then Fill Right and Fill Down, or as Wayne said, Copy, Select All, Paste.

No Headers involved. Then convert columns and rows to Headers as required to use the power of Numbers.

Regards,

Ian.

Thank you barry for the formula. I just had to remove the space from it to have it work. Just as this:

OFFSET(Original::$A$1;COLUMN()−1;ROW()−1)

Cheers,

Puck

This is less about transposing complexities and more about getting used to general Numbers formula entry methods. You can copy/paste the formula from Wayne's post and then fill/copy.

If instead of copying and pasting you want to type in the formula yourself, it would look something like this:

Here I went to cell A1 of a blank table, typed the first part of the formula =OFFSET( and then clicked on cell A1 of the table to transpose, here named Original. Numbers then fills in the table name and cell reference into the formula automatically (this is very handy for getting names and references just right.) Here you then need to "anchor" the reference to cell A1, which you can do by clicking the little triangle and checking the 'absolute row and column' choice, as shown here:

Then you proceed to type in the rest of the formula, hit return or the green check mark, and copy/fill as Wayne describes. This applies not just to transposing but to doing all kinds of good things in Numbers (and, indeed, other spreadsheets.)

SG

Thank you all for the detailed discussion about transposing rows to columns in Numbers.

When I want to learn how to do it, I will definitely refer to this topic. In the meantime, it's easier for me to use Excel.

I say that with the greatest regret. Time was when Apple made such futzing unnecessary.

PS Thank you hershelfromnm in your post of Mar 17, 2012 6:41 PM re Transpose Rows to Columns template:

http://www.iworkcommunity.com/content?page=4

"Numbers lacks one of the most useful features of AW6 - a built-in way to transpose cell values from rows to columns. Here is a very easy method to work around that ‘missing’ feature by creating a simple template."

805 Downloads

Message was edited by: crabpaws

You can save the transposition solution Barry suggested as a template for quick access. That you only have to set up once.

Jerry,

Ah, no trip to the Inspector needed. Hidden in plain sight right there under those little triangles. Very nice.

Thanks!

SG

Hi ekbm,

If you expect Numbers to behave like Excel, you will be disappointed. Numbers is not an Excel clone. Excel uses sheets (of several acres) to display the occasional green oasis of data with deserts between. Barry, another contributor to this Numbers forum, has taught me the analogy of an 'ocean' of an Excel sheet with 'islands' of data. In Excel, it is difficult to link those islands. In Numbers, there are Tables that are easy to link.

SG wrote: 'Barry's one-liner workaround above is not at all complicated for Excel users coming over to Numbers who really do need to transpose a lot.'

I agree. In Numbers, I rarely need to transpose. Numbers gives me the ability to create small, discrete tables of data that link to each other. Organise from the beginning so that you do not need to transpose.

Repeating what SG wrote:

'In Excel one tends to do more Paste Special kinds of things onto different areas of one big worksheet'. I agree. In Numbers, data is generally organised around discrete tables that link to each other.

SG wrote: 'Probably most users (of Excel or Numbers) don't need to transpose all that frequently. I think Apple did well not to clutter up the Numbers user interface with this option, knowing there are easy workarounds.'

This thread has some useful answers. Please do not shoot the messengers. You may use the menu item "Numbers > Provide Numbers Feedback".

Regards,

Ian.

no need to Copy/Paste the content to move it into the new Header Row/Column.

And no need to "do cleanup detail" by deleting an extra row and extra column after copying. Much cleaner and simpler. It's really hard to miss, when you know about it.

SG

As a long-time Excel user and short-time Numbers user, I'm scratching my head reading this thread. This "transpose" thing really is no big deal at all. Sure, in Excel you can just Edit>Paste Special>Transpose without a formula. But Barry's one-liner workaround above is not at all complicated for Excel users coming over to Numbers who really do need to transpose a lot.

In Excel one tends to do more Paste Special kinds of things onto different areas of one big worksheet. In Numbers a sheet is generally organized around discrete tables, and Barry's formula transposes contents from an existing table into a separate table painlessly. Probably most users (of Excel or Numbers) don't need to transpose all that frequently. I think Apple did well not to clutter up the Numbers user interface with this option, knowing there are easy workarounds.

SG

Actually, that is not bad: Go to Excel, traspose them using your typical method and come back for a decent presentation.

With all due respect to those who have worked so valiantly to answer this question, this is too darned complicated.

I'm closing Numbers now and returning to Excel.

Shame on Apple for not fixing this. It's been a request since 2007.