Moving or adding a column to a table trashes charts

Question

Moving or adding a column to a table trashes charts

Numbers appears to suffer from a rudimentary defect. If a chart displays a column of a table then, if I move the column, the data references stay where they were and thus the chart no longer displays the correct data. I would expect data references to be attached to the column object and to stay with the logical column and to go wherever that column goes so that moving/inserting columns to tables would have absolutely no effect on the appearance of charts. This is what happens with expressions in cells and it's what happens with charts in Excel.

The problem is then compounded by Numbers joining data references to adjacent columns together and further compounded by Numbers taking it into its head to add data references to newly inserted columns. All this is making me look for an alternative to Numbers... any significant alterations to tables will trash the charts.

I have an imac24 and have just downloaded the latest OS and Numbers version.

Posted on Apr 27, 2024 10:10 AM

Reply

Answer 1

Badunit

Level 7

26,068 points

Apr 27, 2024 11:08 AM in response to JustAnOldGaffer

I agree. Please provide your thoughts to Apple via the Numbers menu item Numbers->Provide Numbers Feedback.

It is a multi-step operation to move columns around in a table if they are part of a chart. First move the columns/data around in the table then "edit references" to fix any series that are not pointing to the correct places. With Edit Data References active, you can individually drag each series left or right to another column or select several and move them at the same time. You can also make changes to series ranges in the sidebar but I find that to be harder (though sometimes necessary).

First screenshot is original setup for the chart

Second screenshot is after moving column C to the right. The chart still points to the same columns as before but two of the three are not the ones we want.

Third screenshot is after clicking Edit Data References then dragging the green series to the right and the grey one to the left.

Reply

Answer 2

Badunit

Level 7

26,068 points

Apr 27, 2024 7:01 PM in response to JustAnOldGaffer

Some of it just takes getting used to it and figuring out how it works. For the most part I think it works as designed.

The black rectangle marks where the series name is. It can even be on a different table on a different sheet if that is where you tell it.

Numbers expect the series names to be in a header row when charting columns of data. If you have a header row, it uses the header labels for the series names and the black rectangle is up there. If you don't have a header row, it will put the black rectangle over the first row of data and all your series will be named "untitled #". If you want to select a different cell for a series name, you have to do it in the Format sidebar one series at a time (unless someone else knows a better way). It is easiest to do what Numbers wants you to do which is have a header row and put your series names in it but you can override that after the chart is made.

You can move an individual series to another column by selecting its black rectangle then click/dragging.

You can move all of the series ranges vertically or horizontally in the table by click/dragging the colored (or multicolored) selection box.

You can use the Format sidebar to move a single series to wherever you want, either by clicking on the range in the "value" box then dragging the range around on the table or reselecting a new range in the table or typing in a new range.

Reply

Answer 3

Badunit

Level 7

26,068 points

Apr 28, 2024 6:01 AM in response to JustAnOldGaffer

I completely agree with you. It is inconsistent behavior, and annoying. The series ranges and labels (and colors) should stick with the data, wherever it gets moved.

Reply

Answer 4

Apr 27, 2024 3:20 PM in response to Badunit

I've had to do such repairs to charts. I will draw Apple's attention to the situation as you suggest. As you will readily understand, I think the design should be that adding or moving columns never alters charts. Not only do you have to move the data series around, you may have to repair the value labels and change all the colours back as well. This is hugely tedious.

I am also not convinced the data series editing process always works. I have had difficulty with it sometimes putting the marker above the first selected cell and sometimes at the top of the column. I've had further difficulty with it sometimes being able to move the marker to where it's wanted and sometimes it just won't move... and etc

All told a very unsatisfactory experience.

Reply

Answer 5

Apr 28, 2024 5:25 AM in response to Badunit

What you say about how Numbers chart data series works is all true but does not address my complaint. My complaint is that simple rearrangement of a table (eg moving a column from one position to another) may change what a chart displays. This just isn't sensible or remotely acceptable.

If I have a table in which cells have expressions referring to cells in other rows and columns, I can rearrange the rows or columns to my hearts content... each term in each expression will stick to the same cell as before and the values in cells won't change. If this were not true it would be entirely impractical to insert a row or column except at the ends.

The principle in expressions is that a term in an expression is tied to a cell object not to the position of a cell. Where a cell is thus makes no difference to the expression. In any sensible system, chart expressions would work the same.

I am simply staggered that Apple could make such an elementary mistake in the first place. Maybe they assumed that people would design a table and then add charts. The real world isn't like that. My main table started 7 years ago and has grown from 7 columns to whatever A to AR is (getting on for 50) and from 2 rows to 404 as well as several other tables and a dozen charts being added as new and previously unimagined requirements have arisen.

I have raised this with Apple as you suggest and many thanks for your help.

Reply