Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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!
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

618 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