Get legend in right spot

HI DRR,

You are following an MS Excel model, where your spreadsheet consists of an immense ocean of cells with island of data scattered here and there.

Numbers is designed around individual tables, placed onto a sheet. The table locations are independent of each other and of any (not visible) grid of cells covering the whole sheet.

Make an independent table containing eleven columns and 14 rows.

Convert column A to a Header column. Enter the names of the months in cells A2 through A13.

Convert row 1 to a header row.. Enter the years in row 1 of columns B through K.

Convert row 14 to a Footer row. Enter the formula =SUM(B) in cell B14, then fill that formula right to cell K14.

When you make a bra chart using this information, Numbers will get the legend labels from the Header row of the appropriate column(s).

For examples and some further explaination, open the Charting Basics template and study the charts there, with particular attention to the arrangement of the chart in terms of the table containing the data and any formulas you can see that might give you some insight to the relationship between the data layout and the appearance of the final chart.

Regards,

Narry

Hi DRR,

You wrote: "… to Excel. It really is intuitive…"

Only if your definition of "intuitive" is "what I'm used to doing" Your expansion on this in your reply to SGIII comes closer: "Charting in Numbers is very non-intuitive to those bred under Excel."

To particulars:

"So how did I make the original chart without any headers about this little block of numbers?"

I suspect you didn't. Numbers '09 (or Numbers '08) translated the Excel file into a Numbers document when you first imported it, and was able to interpret the table embedded in the Excel document as the source of a bar chart, including the labels. But, as indicated by what you describe as happening on your attempt to add a new column to the chart, that translation (or the translation into a post '09 version of Numbers) was not capable of supporting an extension of the table.

To extract that embedded table and place it as a separate table (on a second sheet, if desired):

(assumes J45 is the cell above "January" and left of "2014", and the lower right corner of the data block is T58, containing $1216.33)

• Click once on J45 to select it.
• Press the shift key and click on T58 to extend the selection to the entire data block.
• Copy.
• Click once in a blank area of the Sheet on which you want to place the table.
• Paste.
• Click in cell A1 of the new table
  • Place the pointer near the row reference tab for row 1, click the v between the row and the table, and choose Convert to Header Row.
  • Place the pointer in the column reference tab for column A, near the right end of the tab, click the v that appears, and choose Convert to Header column.
  • IF you want to include "Electric Cost" in a row in the table, press option-up arrow to add a new (header) row above the current row 1.

Recreate the bar chart using this table.

Regards,

Barry

Excel is a very good app on the Mac. So if you're comfortable making charts in Excel and have to do it often you may be better off with Excel. However, making charts in Numbers, though it takes experimentation, is not too difficult. Recommend having a look at the 'Charting Basics' template at File > New in your menu to see examples of the Numbers approach to charts.

SG

Have you had a chance to look at the examples in the 'Charting Basics' template at File > New in your menu as I suggested above?

Keep in mind that in Excel you do not have to have one big sheet with data spread out in different places, though legacy documents sometimes are structured that way.

In fact, you are often better off using Excel Tables in Excel. (Yes, Excel has tables too, though they're not as easy to use as Numbers tables, and they are not mandatory, as they are in Numbers.)

If you open an Excel document in Numbers and find that things are scattered about, rather than compact and simple as in the template, then usually you will save yourself a lot of frustration and time by rebuilding charts in Numbers the Numbers way rather than trying to make do with, and rejigger, an imported Excel chart.

SG

How to proceed depends on how the original data is laid out.

You said: "The old (cell) P45 references B45 and so on."

A lot depends on the details of 'and so on.'

Assuming my description of the 'data block' is correct, P45 would display '2014', and contain a formula consisting of only a cell reference to cell B45. In the new table, Cell P45 becomes cell G2, and the cell reference in the formula adjusts to a (non existing) cell nine columns to the left of B45. That cell reference should still be to B5 on 'Table 1' (the old table).

If Q45 (original location) references C45, R45 references D45, etc., the easiest solution is to reconstruct the new small table, making cell references in the relocated cells pointing to the same cells as they did before, and remembering include the table name when the referenced cell is on the part of the old table not copied to the new table.

Easier than it sounds, but detailed descriptions require more information regarding where the new table is to pick up each datum.

Regards,

Barry

"Barry, never mind the paste question."

Oops! Too late for that. :-)

I happened to see the notification of your post about 2 minutes after you posted, and started the answer above shortly after that. Was just finishing my closing when I saw the Solved notice flash by, so sent it.

B