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

Posted on 2010-11-13
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 18

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 18

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!
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 18

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 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 18

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 300 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 200 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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 …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

773 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