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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
One way is to plot all or many series in one series

Saqib
0
byundtMechanical EngineerCommented:
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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.