Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Pivot Table - show items with no data

I'm creating a pivot table and only want to show items with data. When I
leave the "show items with no data" unchecked, it still shows all data. The
help pages indicate that some types of source data does support this option.
Which types would they be?


Post your answer or comment

comments powered by Disqus
Hi - hope someone can help.

I have a problem with the "show items with no data" feature.

For example, I have an excel workbook that is about 4 MB. It has one
worksheet, and on it is one pivot table displaying data from an external
source (via MS Query). The pivot has 7 row fields, no page fields, and one
column field. When I check the "show items with no data" checkbox (for any of
those fields) Excel displays a message "There is not enough memory
available..." When I uncheck the "show items with no data checkbox on that
field, the pivot functions perfectly. The pivot when fully "expanded"
requires only 10,000 rows to display.

This issue w/ the "show items with no data" isn't limited to just one
workbook. This problem occurs on many pivots in many different workbooks.

Anyone have any idea why this is happening?

Thanks so much,
gt

Hi,
I have a pivot table for which I always need to displays rows items.
eg) I have a column named Age bucket. (< 1 day, < 1 week, 1-2 weeks etc)
& region (London, NY etc.)
I alwayd need to display the Age buckets & regions irrespective of whether
they appear in the pivot data source or not. The age buckets which doesnt
have data can show "0" against them.
I read something about doing it via SQL, but is there nay other wya?
I have turned on the "show items with no data" under field settings for Age
bucket & region.
Thnaks,
--
Tausif Mohammed

The attached pivot table is 'showing items with no data' even tho that option is not ticked under the Pivot table field options. I need to hide the names where there is no total. It is possible to copy and paste values only, then apply a filter to hide blank rows but this is obviously not ideal when the source data changes.

Sum of Sales
Name Total
Anna 5.5
Dave
Ian 26.65
James
Maria
Neil
Nicola 39.3
Tim 37.34
Grand Total 108.79

Thanks!

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...22120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

Greetings

My data is in financial year, July to June. The date field is in the format
DD/MM/YYYY which is in the column area of the pivot table. I have this
grouped by month and year, so it displays Jul-Dec 2003, followed by Jan-Jun
2004.

Some items in the "Row" area of the table do not have entries for all 12
months, but I still want to display the month for that item (with no data).
In the "field settings" of month I have selected "show items with no data".
Unfortunately this then displays Jan-Jun for 2003 and Jul-Dec for 2004, all
blank because those dates are not in the financial year I am reporting on -
and I don't want to see those months at all.

how can I just show 03/04 months (even if there is no data in them) and not
02/03 or 04/05 months?

thanks in advance

Dan

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...2120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

Hi,

first of all I would like to thank everybody involved in this forum answering questions and solving problems.
I have learned a lot from the posts and replies.

I have a problem in excel 2007 with pivot tables.

DATA
The underlying data is a list of items which have been created in the database and the actual date the item was created on.
So basically two fields, date (DD/MM/YYYY) and summary.
At the bottom of this post I listed some demo data

FUNCTIONALITY
I want to present this data in a bar chart, grouped by month, showing for the months where no items where created an empty bar, and to have the posibility to select a date range ...

WHAT HAVE I TRIED
I create the pivot table and set the values to Count of Summary and set the row label to Date.
This creates the pivot table with for every unique date a count of items on this date. Perfect

I want to see the data grouped by month so I right click one of the date fields in the pivot and select "Group ..."
There I select Months & Years and leave the grouping to auto.
This groups the items by month, however months with no items in the underlying data are not displayed.

To display those, I set on the field settings of the Date Row, the option under layout and print : "Show items with no data".
This generates for every month with no underlying items an empty row.

At this moment everything looks ok, but now I want to filter the pivot within a certain date range.
Therefore I select again "Group ..." and change
the "starting at" to eg 01/07/2010 (DD/MM/YYYY format)
& "ending at" field to eg 01/07/2011 (DD/MM/YYYY format)

The pivot table shows a grouping field < 01/07/2010, another field > 01/07/2011, and for 2010 all the months (jan to dec) and for 2011 all the months (jan to dec).

I however would expect that
- jan / feb / mar / apr / may / jun is not shown in the 2010 group
- aug / sep / oct / nov / dec is not shown in the 2011 group.

I have the ability to deselect via a filter the < 01/07201, and also eg 'jan'.
However when I deselect 'jan' via the filter it dissapears from the 2010 group (which is good) but also from the 2011 group (which is not good)

Is this something that is possible with pivot tables?

I control the underlying data as well, so if needed I can change the layout of the datasource.
However a workaround to create for every grouping a dummy empty record in the underlying data is not an option as this would generate over 1 million records.

Demo data

Date Summary
1/01/2010 Item 1
1/03/2010 Item 2
1/05/2010 Item 3
2/01/2010 Item 4
5/05/2011 Item 5
9/07/2011 Item 6
10/12/2009 Item 7
10/12/2010 Item 8
10/12/2011 Item 9
21/01/2010 Item 10
3/01/2010 Item 11
4/01/2010 Item 12
8/01/2010 Item 13
14/01/2010 Item 14
22/01/2010 Item 15

Hopefully everything is clear ...
If extra info in needed, do not hesitate to ask ...

Thx in advance,

Hi all,

I'm having an issue with my pivot table. I would like to keep all row elements visable even when there is no data. I have set the field setting "show items with no data" to true.

Still, when I update the pivot row elements with no data disappear.

How can I have all items showing?

Thanks in advance!

Can anyone help with some bizarre results I'm experiencing with the show
items with no values check box in the field settings menu for pivot tables.
It appears to be showing field headers that don't exist in my data????

I'm using Excel 2000.

Hello,

I have used from time to time the field option "Show items with no data".
However, most often I prefer to see no entry if there is no data.

Today, I would even like to go further: I would like to see no entry (row of column header) if the (all) data are zero for a given item.
Very often I have a lot of zeros in my PT, that I would prefer not to see.
And I would even more like not to see them on a pivot chart.

Do you think there would be a way to avoid the display of "zero" items?
Of course, I would like to do that without changing the original data base.

Thanks

I am working for a petroleum company, I have created a Sales Pivot
Table which can be used to analysis sales information. The pivot table
allows the users to look at total sales by category like Retail,
Consumer and Competitor, and under each category it shows the list of
customers who has purchased and what amount of fuel. The problem is,
Pivot Table doesnt list all the customers who have not purchased from
us. I mean, lets assume i have a list of 100 customers grouped as
Retail 30, Consumer 60 and Competitor 10. Every month all these 100
customers are supposed to purchase from me, the pivot table that i
created only lists the customer who has made a purchase, if i want to
see all the customers in pivot table, what should i do??

I tried Show Items with No Data in Field Settings of Customer Field but
it shows all the customers in each category, i mean it shows all the 100
customers in all the categories of Retail, Consumer and Competitor.
Please help.

Currently using Excel 2003

I have two pivot tables based upon same data source. Both are summarised in
rows by two data items, but second table may not necessarily contain same
number of rows because of page selection criteria.
In older version of Excel I could "show items with no data" and this would
ensure that both pivot tables would always have the same number of rows.
When I try to build this sort of table and "show items with no data"
a) First data item shows all the data items for irrelevant categories (i.e.
these are categories that can not (should not) appear because they are
definitively excluded by page selection item.
b) Second data item is attempting to show empty data lines for data items
that only belong to particular sub-categories of the first data item. (e.g.
if first data item had category "Nurse" and "Administrator", and second data
item had category "Staff Nurse" which will only ever be a valid category if
the first data item is "Nurse", the pivot table is now showing this group
under "Administrator" as well.)

I can deal with problem "a" by manually unflagging the unwanted categories.
However this approach doesn't work for problem "b".

Hi,

I am using excel 2010 and in that a pivot table for getting data from a cube.Now when i retrieve data from the cube it is not showing data for which the values are null or zero.I have tried pivot table options >display>ticking both show items on rows and columns with no data...but that doesn't seem to work.Even in pivot tools>options>field settings>print & layout the check box is greyed out leaving me no option to check it.Can anyone tell me what could be the possible workaround for this?

Thanks

Hi,
My pivot table has five COLUMN fields, one PAGE field, and three ROW
fields.

I want to output a report from this pivot table. For some of the
column pivot items, I want to hide the detail and just output the
subtotals, and for other columns pivot items I want to show the detail
and NOT the subtotals.

For some columns I want to show items with no data and for others I
don't.

To try to illustrate this, three of the column fields are (in order of
listing on the Pivot Table):
1. ApplicationOutcomeStatus
2. ApplicationStatus
3. ApplicationOfferResponse

For ApplicationOutcomeStatus, there are the following values:
Reject
Offer
Waitlist
Pending

I want the TOTAL of Reject and WAITLIST (ie hide the details). If the
PageItem has no data, I still want to see these columns.

For those records with an ApplicationOutcomeStatus ="OFFER". I need
some details on the ApplicationStatus: I want the total of only those
records with an ApplicationStatus="PENDING" and
AppOfferResponse="COMPLETE". Again, if the PageItem changes and there
is no data, I need to see these columns.

My problem is that while I can fix the columns when looking at one
Page Item, changing the PageItem hide the empty columns. I also want
to be able to configure the visible/hidden details. Am I right in
thinking that the only way to do this is with pretty complex VBA?

Thanks
Andy

I have a pivot table and they have asked me to build out an example of a product. (i.e. XYZ Phone). Internally they want me to show all systems designed to support this phone. (i.e. - Billing system A, Billing System B, Customer Care system C, etc....)

So I produced a pivot table that shows all the systems that support it. But the problem is that they can't see what's not supporting it. They want more of a matrix so they can look through and say, "Hey, you mean Billing System X doesn't support XYZ Phone?"

Question: Is there a setting that will make it so that when I do my pivot table, the left column values all show whether they have a value or not.

I have Excel 2007 pivot tables that refresh on data sets that sometimes have
no data for a particular item. I want to display all items, even zero or
empty. Under Pivot Table Options there is a selection "Show items with no
data on rows". It is not checked but it is also greyed out. Is there some
other setting or condition that is keeping me from utilizing this option?

Thanks in advance,

J Austin

Hi all, and thanks for your help in advance.

I'm having trouble with a calculated item in a pivot table that when created adds records to the pivot table that have no data. Any help would be greatly appreciated.

My pivot table has the following data:

PivotField1 | PivotField2 | Type | Data
---------------------------------------
A           | D           | One  |    1 
A           | D           | Two  |    2
A           | E           | One  |    3
A           | E           | Two  |    4
A           | F           | One  |    5
A           | F           | Two  |    6
B           | G           | One  |    7
B           | G           | Two  |    8
B           | H           | One  |    9
B           | H           | Two  |   10
B           | I           | One  |   11
B           | I           | Two  |   12
C           | J           | One  |   13
C           | J           | Two  |   14
C           | K           | One  |   15
C           | K           | Two  |   16
C           | L           | One  |   17
C           | L           | Two  |   18
I made the column pivot the Type field, with the row pivots the PivotField1 and PivotField2 fields. I also made my data the sum of the Data field. The resulting pivot table looks like this:

Sum of Data                 | Type      
---------------------------------------
PivotField 1 | PivotField 2 | One | Two
---------------------------------------
             | D            |   1 |   2
A            | E            |   3 |   4
             | F            |   5 |   6
---------------------------------------
             | G            |   7 |   8
B            | H            |   9 |  10
             | I            |  11 |  12
---------------------------------------
             | J            |  13 |  14
C            | K            |  15 |  16
             | L            |  17 |  18
---------------------------------------
So far, so good. I then try to add a calculated item to the Type field, named Three, which is defined as "= One * Two". I would then expect the following:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
A            | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
-----------------------------------------------
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
-----------------------------------------------
             | J            |  13 |  14 |   182
C            | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------
Unfortunately, I get the following:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
             | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
             | G            |     |     |     0
A            | H            |     |     |     0
             | I            |     |     |     0
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |     |     |     0
C            | H            |     |     |     0
             | I            |     |     |     0
             | J            |  13 |  14 |   182
             | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------
Is there any way to remove the rows with no data? I've tried using an if function to check if the value is > 0, but that doesn't seem to help.

Thanks again for your assistance.

In Excel 2007 I created a pivot table from a set of data. How can I hide the
items in the pivot table that contain no data?

I want to be able to have a standard set of lookup formulas in a worksheet that reference specific columns in a pivot table. However, when I refresh the pivot table with updated data, some of the columns go out of view because they have no data in them. I'd like those columns to show in my pivot table, even if they contain no data so that my lookup formulas continue to work.

In my pivot table options, I have the following already checked:
1. Display item labels when no fields are in the value area.
2. For empty cells show ...

I noticed though, that the 'Show items with no data on rows', and 'Show items with no data on columns' are greyed out - I cannot check these boxes. These may resolve this issue so if they do...how do I un-grey them?!

I had a dummy row in my pivot table, and now I have deleted it, it continues to show up. After some investigation it seems the only way I can make it disappear is to uncheck "Show items with no data". only trouble is I want to show show OTHER items with no data.

It seems it has been cached somewhere and all the refreshing in the world isn't helping me.

Is there a way to really kill the deleted data?

Hi there!
I face a problem with my Pivot Table. I would like to hide automatically the rows where there is no data in the TOTAL column (the last one). Is it possible?
Thanks in advance for your help!
Bebert

I have a pivot table consisting of two instances of test scores. The column on the left is grouped by increments of 10, in a range of 0-100, and the column on the right is a 'count' of the number of tests within each increment.

There are no test results in the increments 1-10 and 11-20 but by ticking the 'Show items with no data' box in Field Settings/Layout and Print dialogue box, for the left-hand column, I can ensure that the first two increments on the left column are displayed.

However the 'Count' entries on the right-hand column for these increments is blank, instead of displaying zeros. There is no 'Layout and Print' tab in the 'Values Field Settings' dialogue box, so I can't 'show items with no data' specifically for the right-hand column (which is a values column).

Hope that makes sense.

thanks,
Mark

For a current project I need to use a variety of pivot tables that draw data off of another worksheet.

I have other tables and charts that run off of the pivot tables and therefore their cells are linked to the pivot table cells where the data is located. In order to keep that from changing when the pivot table data adjusts, I had to select "Show Items with no Data" in the Field Settings. For instance the values might be A, B, and C. Whether B has a value or not, I need it to show.

If someone makes a mistake with the items in the raw data, the pivot table shows those items, and once correct, it leaves them at 0. I want to be able to fix mistakes like that. Going with my example, let's say someone mistakenly adds in the item J. If they remove it after the pivot table has refreshed, it will still say J, but show a value of 0, but I want to remove J completely. How would I go about doing this?

I hope this was clear, please post any questions you have about my explanation.

Hi,

I'm going to make a lot of pivot tables that have dates in the row field and dollars in each of one to three data fields. Often, one of the data fields will have data from earlier dates than the others. I want to be able to display only the dates where data exists in all three data fields. If any of the three is blank, I want to exclude that date.
...Does anyone have a good way to do this? I can't seem to figure it out, but thinking it would involve using vba to uncheck the display option for a bunch of the row field items. (btw I'm using 2003, I think 2007 might have a display range function..)

Also, I'm not getting the intended effect when I uncheck "show items with no data" in the row field. For example, I am seeing a pivot table with three rows (instead of two, i.e., not displaying the middle row) when I use this for the pivot table area:
date / $$
1/1/1990 / 100
2/1/1990 /
3/1/1990 / 200

I'm beating my head against the wall on this; thanks a lot for any help on either of these two problems.

Tai


No luck finding an answer? You could always try Google.