Workaround for flawed XY (Scatter) pivot charts!

GfE · December 25, 2021, 11:48pm

This time, no noob question but a noob workaround that I’d like to share, as I didn’t find it anywhere in over two hours of troubleshooting. Hope you are as happy about finding it here as I would have been.

The problem:

If you create a “XY (scatter)” chart from a pivot table, e. g. in order to get a presentation that preserves linear scales in both dimensions (X and Y), the chart misbehaves as if it couldn’t numerically evaluate the values in the first pivot column, resulting in equidistant X axis lables, regardless of the real X values. There is a bug report on this misbehaviour, but sadly, it’s three years old by now and still not been worked on.
Moreover, the data range of a pivot chart doesn’t even appear to be editable - the option “Data Ranges …” that appears in the context menu of other charts is missing.
I have encountered suggestions to manually copy the data of the pivot table into a plain table elsewhere and create a plain chart based on that table, but the static nature of this workaround, including the need to manually copy the data over and over on every pivot update, feels like a cumbersome limitation to me.

GfE · December 27, 2021, 5:14am

The workaround:

If your pivot table employs filters, make them show “all”, so the pivot table expands to its maximum size.
For the time being, mark any rectangular data range at the pivot table sheet apart from the pivot table. It doesn’t matter if there is content, the range just must not include any pivot table cells.
Start creating a chart from that range, e. g. via menu: “Insert” → “Chart …”
In part 1 (“Chart Type”) of the dialog that opens, configure the desired “XY (scatter)” chart type. Make sure to enable “sort by X values” unless you really mean the graph to possibly go back and forth.
Now in part 2 (“Data Range”), correct the data range so that it matches the real pivot table, excluding the “technical” rows above the column headers as well as the totals row (if any) at the bottom. If you do so using the mouse, start from the lower right corner and drag it up to the leftmost column header (it’s easier this way).
Continue and finish parts 3 and 4 of the dialog.
You now have a fully fledged normal chart based on your pivot table, with all the usual chart options being present and editable.

Please note that there are some drawbacks to this workaround that distinguish it from a real solution (i. e. fixing the bug):

The chart setup procedure is somewhat less intuitive. You have to memorize and remember it when needed, as the LO GUI provides no guidance.
There are no filters in the chart. However, you can use filters in the pivot table and the chart will adapt just fine.
If the source range of the pivot table is changed and the pivot table refreshed, the chart will NOT follow. To give room to future row insertions, you may set a data range in step 5 that extends beyond the lower border of the actual pivot table. In order to make this work without clutter, the pivot table should not have a totals row. As the chart allows to edit all options including data range, you can also adjust these things later when needed.

(I really hope this workaround doesn’t make the devs “fix” such “ex post” pivot charts by imposing the same incumbering restrictions on them but rather repair the broken abscissa handling of proper pivot charts. As some options of proper pivot charts are disabled in order to reliably sync with the pivot table, there should be a way for the user to convert a pivot chart into a non-synced normal one and back.)