Pivot Table "Not Enough Memory" error when using "Show Items with No Data"

Posted on 2010-11-13
Medium Priority
Last Modified: 2012-05-10
I created a pivot table in Excel 2003 that recap sales.  The pivot table is filtered using the sales rep ID in the PAGE section.  The ROW includes sales type (for example, grocery, meat, produce, etc.) and customer (ID and name) while the COLUMN includes period number.  The DATA recapped is sales dollars.

The problem is that I want the pivot table to display all of the sales rep's customers (by sales type) even if they do not have any sales.  I want to be able to include those customers that have no sales with those that do.

I tried to select the "Show items with no data" box in the PivotTable Field for the customer ID; however, I receive the error "There is not enough memory available to create the PivotTable report.  To make more memory available, try the following: Reduce the number of column or row fields you include in the PivotTable report.  Close files or programs you aren't using".

The solutions offered do not work for me as I  can't reduce the number of rows because I want to be able to display all of the customers for the respective sales rep and sales type.

I appreciate any help that you can provide.


P.S. In my current project, I link the pivot table to data in Access; however, I have also had this problem when the data is in the same Excel spreadsheet.

Question by:jack3_99
  • 4
  • 2
  • 2
  • +1

Expert Comment

ID: 34129948
you could be at the limits of the pivot table. These are the limits:

Basically, what I gather from this article is that for your situation....
if you have let's say 100 unique customers, 12 unique period ids, 4 unique sales types, and 3 unique salesmen... that would be 100x12x4x3 = 14,400 column items.
Column items can't be more than 32,768, so if there were 10 salesmen for example, then you'd be over the limit because that's 48,000 (100x12x4x10)
I can't imagine you'd have too many row items, because the limit on that is 2.1 billion. So you probably have too many column items, because it seems pretty easy to get over the limit there.

I don't think excel 2007 has this issue, because it has the power-pivot addin which uses sql server express ... and you can manage a ton of data with that. So if you have access to excel 2007, that might be a option for you.

Otherwise, the article recommends reducing the column fields in your pivot table to only those fields that are absolutely necessary. ... I'm thinking that's salesmen and customers.  Then as long as (unique salesmen x unique customers) < 32,768 the report should run.
You can put the other fields (Sales type and period) in the page field.. You'd have to change the page field to get the information for each sales type and period, but it might keep you under the 32k memory limit if you don't have those fields as column items.

LVL 19

Expert Comment

by:Richard Daneke
ID: 34130947
More importantly, Excel 2007 (and 2010) has many more rows (255 vs 16,384) and columns(1,048,576) and NO PROGRAM LIMIT on worksheets (Excel 2003 was limited to 255 worksheets plus 1 for macros)   Some limits are now set by your computer and not just Excel.
LVL 19

Expert Comment

by:Richard Daneke
ID: 34130957
Two more quick notes:
1. Excel 2010 offers a new feature called Web Slices that offers dynamic filters for your data
2. Store your data in Access or a separate closed Excel file.  Since Pivot Tables work with the data in memory, should you also have your data in a worksheet, you are unneccessarily duplicating the data - reducing the available memory for the pivot table!
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Expert Comment

by:Richard Daneke
ID: 34130976
More importantly, Excel 2003 vs Excel 2007 (and 2010) has many more columns(255 vs 16,384) and rows(65,536 vs 1,048,576) and NO PROGRAM LIMIT on worksheets (Excel 2003 was limited to 255 worksheets plus 1 for macros)   Some limits are now set by your computer and not just Excel.
Weak excuse: as a former Multiplan users I still think Rows and Columns when references go Columns and Rows!!
SO, PivotTables cannot show more columns or rows than are available in a worksheet EVEN IF the pivot table field limits referenced earlier http://support.microsoft.com/kb/820742 are not exceeded.

Author Comment

ID: 34131994
I don't believe there is a problem with the number of rows or columns.  Attached is sample data with a corresponding pivot table.  I get the memory error when selecting the "Show items with no data" box in the CustID field even though this is only data sample.

Included also are a couple of mock-ups of how I would like to display the information. As an example, I would like to display Customer #10035297 even though there are no dollars for the customer.

The reason we want to do this is so that we can review customers with no sales along side customers that do have sales.

If this is beyond the realm of pivot tables, are there any alternatives?

Thank you for your help,

LVL 85

Expert Comment

by:Rory Archibald
ID: 34135656
If you select that option, then it will attempt to display all customers for each of your Slsperid and Type combinations, which is why it runs out of memory.

And FYI, XL2003 is *not* limited to 255 worksheets; like 2007, it is limited only by available memory.
LVL 19

Expert Comment

by:Richard Daneke
ID: 34135919
I am corrected in the limit on Excel 2003 worksheets.  Mea Culpa.

I have used the sample data in Excel 2010 without error.  You can download a free trial copy of 2010 from Microsoft.  Be warned, creating Pivot Tables looks different in the 2010 product.

Accepted Solution

J79123 earned 1200 total points
ID: 34136150
The name is directly related to the ID, plus it's informational. (and yes I'm assuming you actually have names in there on your original version, and its not the redundant "Customer ID:+ID" data you posted, so this takes that into account).
But I would think that the name field is what is putting you over the limit. Like customer IDxCustomer name is eating up the memory. So since you can get the info from one field by looking at the other maybe you can create a field in your data that combines these 2 fields into 1 field
Like add a new column called CUSTIDNAME with a formula of
=c2&" "c3
Then drop the cust ID field and customer name fields from your table and use CUSTIDNAME instead.
Unfortunately, I'm not sure if this will solve your issue because I don't have 2003 installed to check.
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 34136233
To clarify my earlier response:

Say you have data with three fields: Type, Customer and Value.
The Type field has two values, A and B
The customers field has Customers 1 to 10, with Customers 1 to 9 belonging to Type A and Customer 10 belonging to type B.
Now you create a pivot with type and customer as row fields - by default Customers 1 to 9 appear under A, and Customer 10 appears under B. Now change the Customer field to display items with no data. You will see that EVERY customer appears under BOTH types. (sample file attached). Now extrapolate this to your sample - for EVERY combination of Slsperid and Type, you will get EVERY customer listed. That's potentially a lot of data, and a lot more rows than you may be expecting!

Author Closing Comment

ID: 34144191
Thank you for all of your time and patience in explaining your answers.  I appreciate your help very much.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question