0

I have a pivot table showing how often a certain "speed" is found in the table.

row labels  count of Id
1           31
2           29
3           39
4           66
...
77          2
90          1
92          1
108         8
135         1

This is shown in a pivot chart. The issue: the row labels are treated as text labels instead of number values. As a result: in the chart there is no space between e.g. 92 and 108.

enter image description here

The expected behavior is that there is a space between non-consecutive numbers. This works fine when I create a diagram manually.

enter image description here

To be sure -even though this makes no difference in the manual example- in the data table (and in the pivot table) I have specified that the speed column is of type Number.

Format Axis of the chart has for Axis Type: "automatically select based on data". Also available: Text axis and Date Axis. Not: number. For the manual chart it clearly is treated as numbers, with the option to set minimum and maximum bounds. Using Excel 2016 (Windows).

Any suggestions?

1 Answer 1

0

The problem isn't related to data type. It's due to the fact that ScatterPlots aren't supported for PivotTables, meaning you can only get a simple line chart, which has a completely different behavior than the ScatterPlot you have created manually.

The workaround is to create a blank scatterplot (or use the one you created manually), and then repoint the series of that existing scatterplot to your PivotTable data.

2
  • Interesting, when I created the manual diagram I selected a regular line chart. Did not know it turns it into a scatterplot. Thanks. What is best practice to define source data? The number of rows is dynamic, so I can't select a fixed range.
    – Wivku
    Nov 2, 2016 at 11:53
  • You can probably make it dynamic using my code from chandoo.org/wp/2014/10/18/… Nov 2, 2016 at 19:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .