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?
nurbsAsked:
Who is Participating?
 
byundtCommented:
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.

Brad
RevisedCapacityQ26927489.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
One way is to plot all or many series in one series

Saqib
0
 
byundtCommented:
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?

Brad
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
nurbsAuthor 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
 
Saqib Husain, SyedEngineerCommented:
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
 
byundtCommented:
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
 
nurbsAuthor Commented:
My file attached.
Any input would be most appreciated
RevisedCapacity.xlsx
0
 
byundtCommented:
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

Open in new window


Sample workbook with PivotChart

RevisedCapacityQ26927489.xlsm
0
 
nurbsAuthor 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
 
nurbsAuthor 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.

All Courses

From novice to tech pro — start learning today.