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
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!
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Industry Leaders: 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!

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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