Pivot table chart truncating displayed values when data grows too large.

Question

I often create pivot tables with thousands of row-labels. When the number of row labels grows sufficiently large (maybe 50-100k+) the pivot chart stops displaying all available values in the chart, and cuts off after maybe 5000 values. As far as I can tell its just related to amount of values, and not the actual values contained.

I'm using Excel 365 Version 1901 (Build 11231.20174) 64bit. I've replicated this bug on multiple of my coworkers systems, and have 32gb of memory in the system. I don't see it using up all memory or anything..

Has anyone else experienced this bug? It seriously impedes my normal workflow, and I haven't found a way around it to display everything.

Happy to expand on anything that could help solve this.

Answer 1

No problem, please let me know when you are able to further test.

Answer 2

Very sorry for the spelling error in my first paragraph. 

I was just kidding about the spelling error :), god knows I've made a ton...

Yes, I've tried recreating the pivot table, but run into the same issue, on the same rows. Indeed I would like the pivot chart to show all labels. If you create a pivot table from the data provided. do you get the same issue?

Thanks,

Brian

Answer 3

Hi ljump12,

Very sorry for the spelling error in my first paragraph.

I type the last display data of both issue related pivot tables in the Find window and I got the following result:

BAD_RowLabelsTruncatedInGraph stoped in 63664:

BAD_RowLabelsTruncatedInGraph2 stoped in 6065:

Do you mean that you want your pivot display all labels? If so, I'd like to suggest you create that pivot table again to make sure you have select all labels to display.

Besides, I need some more time to test this issue. I will post back once I get any updates. Thanks for your understanding.

Regards,

Neo

Answer 4

Thanks for the response Neo, its an unfortunate bug, but luckily this isn't causing any incontinence issues. ;)

 Please see the linked spreadsheet: https://1drv.ms/f/s!Asyty5fH9p6xaegpeEJ2xP66zio

Generally what I try to do is put a variable I want to analyze in the "Rows" section, then put what we consider a quantifiable +/- result in the values section. I then show the values as a running total of my row labels.  This allows me to see how the "row" variable affects our result. For instance do low values lead to good (+) results etc... I'll look for patterns in different Row variables.

I've created 3 pivot tables in the sheet. OK_allRowLabelsShown shows an example of where its fine. I'm graphing my result over time (dates). All 90 dates are shown, and the graph ends at the full result total.

The second pivot "BadRowLabelsTruncatedInGraph", has approximately 130,000 row labels, but you'll see the result stops at (~80,000). It's only displaying the first 30,000 labels. 

The third pivot "BadRowLabelsTruncatedInGraph2", again has approximately 137,000 row labels, but it only shows the first 6000 labels. 

In my experience its random, but usually fails when I'm looking at 50k+ row labels.

I understand this probably isn't the traditional use for pivot tables, but I've found it to be really effective at analyzing different variables, so I'm hoping whatever bug is causing this can be fixed.

Best,

Brian

Answer 5

Hi ljump12,

Sorry for the inconvenience caused by this issue and thanks for your sharing.

First of all, what I want to explain is that the row number for testing is too large for us. Could you please share with me more info as below to let me have a better understanding of that issue?

1. Can you use Pivot Table chart normally when the row number is less than 50K?
2. Screenshots of the version number of related computers and this issue.
3. Does this issue happen before? What have you changed before this issue appears? (e.g. Update OS/Office)

Besides, could you share with me a copy of your workbook to let me have a test of this issue? If so, you can share it with me in here or share it with me via Private Message if it contains the private message. Thanks for your understanding.

For your reference: Share OneDrive files and folders. You can upload your file into OneDrive then share it with me in here by anyone link. 

Regards,

Neo