here's how to transpose rows to columns

Agreed. Can I figure this out? Sure. Do I want to explain it to an entire department that we're trying to wean off of Excel? Of course not.

For iWork to be taken seriously as a productivity suite, it needs to match at least Office's basic features.

And just to contribute something useful - Google Docs does this with a much simpler formula.

I cannot tell if you are being sincere or facetious . I am not sure what you want from your fellow users. We cannot change Numbers. We attempt to solve problems and have no way to effect a change. Sometimes we all just bump into real limitations of the software.

All the Best,

Wayne

ekbm writes:

"Agreed. Can I figure this out? Sure. Do I want to explain it to an entire department that we're trying to wean off of Excel? Of course not."

Did you originally 'want to' explain to an entire department how to use the features supported by Excel that you wanted the department's members to use? Of course not.

"For iWork to be taken seriously as a productivity suite, it needs to match at least Office's basic features."

If its designers intended Numbers to be an exact replacement for MS Excel, or intended it for the same group of users as MS Excel, it would indeed make 'match(ing) Office's basic features' a 'need.' An in depth study is hardly necessary to make it clear that the target audience for numbers is different from the target audience for Excel.

"And just to contribute something useful - Google Docs does this with a much simpler formula."

Always good to see a useful contribution. A common recommendation here has been to use the tool that fits the job. If you need all (or specific) features of MS Excel, then that's the tool you should use. If Google Docs provides the features you need, then use Google Docs.

Wayne's suggestion was also a useful contribution; If you want to see changes made to Numbers, then communicate that directly to Apple. Apple does not regularly monitor these forums except for compliance with the TOU. The direce comunication route is via the Feedback channel, and the doorway to that channel is through the menu item Provide Numbers Feedback, found in the Nmbers menu in Numbers '09.

Regards,

Barry

I agree with ekbm, Numbers comes off as very lightweight compared to Excel.

I've found the need to transpose most often occurs with importing data from other sources.

Reluctantly, I've invested again in Microsoft Office and so it shall be until iWork is upgraded.

I've found the need to transpose most often occurs with importing data from other sources.

Ironically, in such situations (recurring imports) I've found to my surprise that Numbers is more convenient than Excel. You import data into your usual "Source" table. You've got your "Transposed" table already set up with Barry's one simple formula so it updates automatically. No need to be worry about finding a truly empty cell range to paste without overwriting data, then fiddle around with Edit/Paste Special, remember to click the Transpose box, and all that. It's just paste and done.

So this is a bad example for trying to demonstrate how "light-weight" Numbers is. Numbers can't do everything Excel does. But in this particular aspect it's actually a champ.

Parts of this discussion strike me as rather like criticizing a light car for not being able to do everything a heavy truck can do. In many situations the light car is more convenient, and gets you where you want to go faster in more style with less fuel. In other situations you do need (to mix a metaphor) the heavy artillery. Excel is an excellent app. Numbers is too (and does well on an iPad). Perhaps improvements are on the way.

SG

Barry, this is a basic question, but it's where I'm at in this process. 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? I've never used a title in a formula, but you have cell locations specified via $A$1. I'm lost on what actually goes there. It must be some identifier for that table, but can you help?

And I thought transposing would be so simple...

Thanks in advance!

OK, I see my first mistake. I was wondering how you connected the name "Original" to that table, which I'd never done. I'd only used headings to title/head sheets , and I knew there was no way something typed in a heading would connect to a chart via a function. Figured out in inspector how to assign a name to the particular chart and things make more sense. I'm just beginning to need these more advanced functions of numbers, so too much is new.

Thank you, Wayne, for the more detailed explanation. It made me realize there had to be some way to attach a name to a table! Forums are a great help due to the people willing to take the time to help us beginners!

Hi Ian,

Like your clear, simple, and complete presentation!

One question, not related to transposing but more generally to Numbers:

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

To do this kind of conversion I've been doing this:

1. Go to Inspector>Table>'Headers and Footer' and pick the number of header rows/columns.
2. Copy row 2 (old row 1) and then 'Paste and Match Style' into the newly created header row above
3. Copy column B (old column A) and 'Paste and Match Style' into the newly created header column to the left
4. Delete the "extra" row and column.

The way I've been doing it is not onerous. But I thought I'd ask just in case there is an even simpler, Numberseque way to accomplish the same thing. Is there?

SG

SGIII wrote:

Jerry,

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

Thanks!

SG

Right, and more importantly, no need to Copy/Paste the content to move it into the new Header Row/Column.

Jerry

This is a great trick - thanks. I often need this (when I get it wrong in the first place). It is a pain that Numbers can't quickly flip it but this works fine.

Can we preserve the formatting as the transpose happens?

Jim Leask wrote:

This is a great trick - thanks. I often need this (when I get it wrong in the first place). It is a pain that Numbers can't quickly flip it but this works fine.

Can we preserve the formatting as the transpose happens?

Hi Jim,

This is an old thread. In Numbers 3.5.2

Regards,

Ian.

I got confused on which post was the latest

The syntax for a cell reference is:

[[<SHEET>]::<TABLE>::]<ROW1><COLUMN1>[:<ROW2><COLUMN2>]

the items in square braces are optional depending on the situation.

I will work my way through the possible combinations:

assuming we have two sheets named "Sheet 1" and "Sheet 2"

and assuming each sheet has two tables named "Table 1" and "Table 2"

1) a single cell in the same table, same sheet

e.g. A1

2) a range in the same table, same sheet

e.g. A1:B2

3) formula in a cell of "Table 1" referring to a cell in "Table 2" on the same sheet

e.g. "Table 2"::B1

4) formula in a cell of "Table 1" referring to a range in "Table 2" on the same sheet

e.g. "Table 2"::A1:B2

5) formula in a cell of "Table 1" of "Sheet 1" referring to a cell in "Table 2" on sheet "Sheet 2"

e.g. "Sheet 2"::"Table 2"::B1

5) formula in a cell of "Table 1" of "Sheet 1" referring to a range in "Table 2" on sheet "Sheet 2"

e.g. "Sheet 2"::"Table 2"::A1:B2

In Barry's formulas he assumes that the data in table name "Original" is the data that is NOT transposed.

Each cell in the table named "Transposed" contains a formula which retrieves data from the table "Original" such that the data is transposed.

You are venting on people who are fellow users. If you have feedback and want to provide to Apple you may use the menu item "Numbers > Provide Numebrs Feedback"

I've seen you post that a lot. So helpful. No wonder you're level 6!

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