Is there any way to exceed the maximum # of data series per chart?

[Feather11]

I'm tracking the daily productivity for over 300 employees and 250 working days.

Currently, the date/working day is the Legend Entry (Series) and the employee name is the Horizontal (Category) Axis Label.

I need a way to switch them around so that the date/working day will be the Horizontal (Category) Axis Label and the employee name will be the Legend Entry (Series).

I know it's a lot of information for one chart, but unfortunately none of the data can be split up, combined, or removed. I have tried this with a regular table, pivot table, and pivot chart and each time I try to switch the row/column, it gives me the "The maximum number of data series per chart is 255" prompt.


Thank you in advance for any suggestions and/or help!!!

P.S. - I'm using Office 2013

 

[Worf]

Welcome to the Board

We are limited to 255 data series per chart. Is your chart readable?
Anyway, you could:

- Split the employees into two or more groups, or
- use one series to display information about two employees, as shown below:

 

[Feather11]

Thanks for the welcome and response!

In regards to your suggestions:

Is your chart readable?

It is not readable with every person on the chart, but it becomes readable after it has been filtered down to specific information.
After the report is distributed, different people filter the information in different ways which then makes it readable for the information that person wishes to see.

- Split the employees into two or more groups, or
- use one series to display information about two employees

None of the data can be split up, combined, or removed.
I'm also not able to combine information about two employees since I need to see everything on an individual basis per employee per day.

The issue is that after being distributed, each person will want to see different information and will therefore filter differently. Hence why I must have all of the information on an individual and per day basis.

Is there any way to exceed the 255 limit for the data series?

 

[Worf]

I do not think we can have more than 255 series per chart.

One solution would be to present all the information:

- On a worksheet table
- On a control contained in an user form

Then, the user would filter the information. Only after that, the chart would be generated, based on filtered information.
If you like this method and provide some details, I can prepare an example.

 

[Feather11]

I'm not sure if what I have is the same thing you're speaking of. I created a table with the information I needed (same as example below) and then added a pivot table. The pivot table is what they would be filtering for specific information, which they would then be able to view more clearly on the pivot chart.

If this is not what you meant, an example would be wonderful!

Here is a small sample of the information I am working with.

[TABLE="width: 564"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Date[/TD]
[TD]Supervisor[/TD]
[TD]Department[/TD]
[TD]Employee[/TD]
[TD]Productivity[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/1/2015[/TD]
[TD]Kalvin Klein[/TD]
[TD]Accounting[/TD]
[TD]Charles Schwab[/TD]
[TD]118[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/1/2015[/TD]
[TD]Merrill Lynch[/TD]
[TD]Office Services[/TD]
[TD]Debbie Lehman[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/1/2015[/TD]
[TD]Morgan Stanley[/TD]
[TD]Solutions[/TD]
[TD]John Deere[/TD]
[TD]231[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/2/2015[/TD]
[TD]Kalvin Klein[/TD]
[TD]Accounting[/TD]
[TD]Charles Schwab[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/2/2015[/TD]
[TD]Merrill Lynch[/TD]
[TD]Office Services[/TD]
[TD]Debbie Lehman[/TD]
[TD]221[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/2/2015[/TD]
[TD]Morgan Stanley[/TD]
[TD]Solutions[/TD]
[TD]John Deere[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/3/2015[/TD]
[TD]Kalvin Klein[/TD]
[TD]Accounting[/TD]
[TD]Charles Schwab[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/3/2015[/TD]
[TD]Merrill Lynch[/TD]
[TD]Office Services[/TD]
[TD]Debbie Lehman[/TD]
[TD]142[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/3/2015[/TD]
[TD]Morgan Stanley[/TD]
[TD]Solutions[/TD]
[TD]John Deere[/TD]
[TD]143[/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

You could use the following procedure:

- Use the create chart routine to generate a blank chart
- Filter the pivot table
- Use the add data routine to populate the chart; this can be done by clicking a button.

Note 1 – I tested it with a small pivot table; if you have the limit problem with the big one, we will have to copy the filtered data to another location and populate the chart from there.

Note 2 – Alternatively, you can have a preexisting chart and just change its source data, instead of creating a new one from scratch.

Dim sh As Shape

Sub CreateChart()
' add a blank chart
Set sh = ActiveSheet.Shapes.AddChart2(201, xlColumnClustered)
End Sub

Sub AddData()
' L18 is upper left corner of pivot table
sh.Chart.SetSourceData Source:=Range("L18").CurrentRegion
End Sub