1 Attachment(s)

Pivot table data series limit of 255 but using only two columns

Hello dear excel users,

I am having a problem which certainly has very simple solution but I can't find it because while i google this I always reach questions from people who want to pot multiple series on one graph.

I wanted to show simple line graph using power pivot. One column is showing position of the measurement and the second column is showing measured value.

Attachment 628160

Now I just want to swap axes, so that the position (mm) is ascending from top to bottom. When I click on "switch row/column" I get an error that maximum number of data series is 255. I don't understand why, I just have two columns of data so if I understand this right it should be two series, one representing position and one representing measured value.

Thanks for the help!

1 Attachment(s)

Re: Pivot table data series limit of 255 but using only two columns

Same as here. I would like just to swap axes.

Attachment 628168

Re: Pivot table data series limit of 255 but using only two columns

Quote:

Originally Posted by

**bculjak**
When I click on "switch row/column"

That is not swapping axes, it's plotting each value as a new series. All you need to do is select the series in the chart and then Format Data series and choose to plot it on a secondary axis.

1 Attachment(s)

Re: Pivot table data series limit of 255 but using only two columns

Sorry, but I don't think I understand this. Could you tell me step by step please?

However, now I can't edit data in the series when I open "select data" window

Attachment 628201

Re: Pivot table data series limit of 255 but using only two columns

With pivot chart the row field is plotted against the category axis and the column field determines the series.

The Switch Row/Columns has the affect of move the variable in the row field to the column field and creating multiple series.

You need to swap the variables used in the Row field and Values field of the pivot table. This will raise a problem in that all the identical values in the row field will be grouped together and the values plotted against the vertical axis will be summed. This is probably not what you want.

Note that the chart is a line chart and not a xy-scatter. This means the spacing on the horizontal axis is evenly spread according to the number of unique data values and not relative to their actual value.

In order to do a true xy-scatter chart from the pivot table you will need to use a standard chart and reference the ranges covered by the pivot table.

Re: Pivot table data series limit of 255 but using only two columns

I really don't understand why it is such a problem just to transpose this. Just to shift the data from x axis to y axis and from y to x

Re: Pivot table data series limit of 255 but using only two columns

It may help, I emphasize the word may, if you could upload a small desensitized sample of the data.

To upload a sample workbook (I assume that you have data on multiple sheets) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.

Let us know if you have any questions.