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?


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.

  • 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 '16 at 11:53
  • You can probably make it dynamic using my code from chandoo.org/wp/2014/10/18/… – jeffreyweir Nov 2 '16 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.