# How to overcome Excel Series Limitation

I am trying to create an excel sheet for tracking capacity on various pieces of equipment in our shop. I need to plot out over 2,000 series to chart.
I have read various reports that the limit in excel for these is 250 in a single chart.
Is there a way to overcome this limitation?
###### Who is Participating?

Commented:
I'm pretty sure the PivotChart can do what you need. Since you didn't like the first chart, I modified it for two other types of charts in the workbook attached to this Comment.

If none of those are what you are looking for, please post a visual representation that better describes your aim. It would be sufficient to show two jobs and two different dates with some mixture of categories. With something that simple, you should be able to stack rectangles to show what you are looking for.

Normalizing the data is a mandatory first step if the PivotChart is the solution to your problem. I assume that you are getting your data from another source--and don't have control over its formatting. For this reason, I wrote the macro to produce a normalized data table.

RevisedCapacityQ26927489.xlsm
0

EngineerCommented:
One way is to plot all or many series in one series

Saqib
0

Commented:
nurbs,
Even Excel 2010 has a maximum of 255 series in one chart.

You can see the limit for your version of Excel by searching for "specifications" in the Help. There is a section on "Charting specifications and limits" that will mention the maximum number of series.

Excel 2003 had a limit of 32,000 points on a chart. Excel 2010 can handle any number of points (limited by only by memory).

With this in mind, you will definitely need to follow Saqib's advice and reduce the number of series being displayed on your chart. What is the reason you think you need 2000 of them?

0

Author Commented:
I have no problem combining many series into one series, but have never done it.
Here's summary of what I'm trying to do:
I have an excel sheet that has 14 days of the week across the top.
Each day has a column for "Tentative", "Open", and "On-Proof" entries of hours.
The hours are associated with Job Numbers that I have running down the first coulmn of the sheet.
(There up to 50 available "Job" number entries.)
The resulting chart I am trying to create is a stacked bar chart that shows the individual jobs color coded by classification and labeled with the individual job number.
So that's 14 days X 50 possible jobs X 3 different catergories. (2100 series)
0

EngineerCommented:
That is only either 14 series or 50 series.

Can you upload some sample data and we shall try to show you how to do it in excel?
0

Commented:
I'm thinking 150 series (50 jobs * 3 categories), with up to 14 days of data in each series.

If you reorganized your data into a four column list (Project number, category, date and hours), then you could produce the desired chart as a PivotChart. Doing so would give you the advantage of being able to use PivotChart controls to select/deselect projects and categories to make the data convey its message more clearly.
0

Author Commented:
My file attached.
Any input would be most appreciated
RevisedCapacity.xlsx
0

Commented:
Sub to normalize the data on Sheet2:
``````Sub Normalizer()
Dim rgDest As Range, rgFilt As Range, rgSource As Range
Dim i As Long, j As Long, jj As Long, k As Long, n As Long
Application.ScreenUpdating = False
Set rgSource = Worksheets("SM74").Range("A7:AM57")
Set rgFilt = rgSource.Offset(1, 0).Resize(rgSource.Rows.Count - 1, rgSource.Columns.Count)
Set rgDest = Worksheets("Sheet2").Range("A:D")
n = rgSource.Columns.Count
j = 2
rgDest.Rows(1).Value = Array("Job", "Hours", "Type", "Date")
For i = 2 To n
rgSource.AutoFilter Field:=i, Criteria1:="<>"
If rgSource.Columns(i).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
k = (i - 2) Mod 3
rgFilt.Columns(1).Copy rgDest.Cells(j, 1)       'Job #
rgFilt.Columns(i).Copy rgDest.Cells(j, 2)       'Hours
jj = rgDest.Cells(Rows.Count, 1).End(xlUp).Row - j + 1  'Number of rows of data copied
rgDest.Cells(j, 3).Resize(jj).Value = rgSource.Cells(0, i)       'Type
rgDest.Cells(j, 4).Resize(jj).Value = rgSource.Cells(-4, i - k)  'Date
j = j + jj
End If
rgSource.AutoFilter
Next
rgDest.Columns(4).NumberFormat = "m/d/yyyy"
Application.ScreenUpdating = True
End Sub
``````

Sample workbook with PivotChart

RevisedCapacityQ26927489.xlsm
0

Author Commented:
I appreciate the sub to normalize the data, but it still is not what I am hoping to achieve in the output on the chart.
I have made references to the entered data further to the righ in the excel sheet so no matter what I enter in the left most cells, it would change on the chart.

On 3/28, If I enter 1 job that is 1 hour in the "Tentative" category, another job that is 2 hours in the "Open" category, and a third job that is 1 hours in the "On Proof" category.
The I enter the same amount of jobs on 3/29 with the same or varying amount of hours.

I am trying to get a stacked bar chart representing each day next to the next with each job stacked on top of eachother to give the total capacity of the alloted 16 hours for the given day.

If this is totally confusing, I could sent a visual representation of what I'm trying to achieve.
I was able to get this representation, but the number of distinct series is what limited me.

If anyone has any ideas, I would be most greatful.
0

Author Commented:
I have revised the information needing to pe charted and utilized various portions af all the feedback given to come up with a custom solution of my own.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.