Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

Pivot table - remove "Blank"

Posted by Susie on May 17, 2001 11:25 AM
A Pivot Table report has many entries that are blank and will be filled in as info. becomes available. The problem is the Pivot Table displays the word "Blank". Is there any way to prevent that from displaying and it just shows an entry line if there's nothing in the field?

Re: Pivot table - remove "Blank"

Posted by Barrie Davidson on May 17, 2001 11:35 AM

Double click on the row/column field when you are in the pivot table wizard. The bottom portion of the menu box will enable you to specify what items you want to hide.

Hope this helps,
Barrie.


Re: Pivot table - remove "Blank"

Posted by Susie on May 17, 2001 3:03 PM

Some clarification on my problem. I don't want to hide anything. I just don't want the word "blank" to display in the pivot table column. I want the whole row to display because there's data in other columns. Let's say, for example, Column A is Customer Name, Column B is Salesperson 1, C is Salesperson2, etc. If the database has no entry in Column B for a particular customer, then the pivot table returns the word "blank". That's what I want removed. I just want to see an empty entry in that column, not the word. Thanks for any insight.


Re: Pivot table - remove "Blank"

Posted by Dave Hawley on May 17, 2001 8:48 PM


Hi Susie

Right click on the "Field" that shows the word "(Blank)" and select "Field Setting". Now in the "PivotTable Field" dialog tick "Show Items With No Data". Click Ok.

Is this what you mean ?

Dave

OzGrid Business Applications


Re: Pivot table - remove "Blank"

Posted by Mark W. on May 18, 2001 6:55 AM
Susie, just type over the value, "(blank)",
with a space character.


Re: Pivot table - remove "Blank"

Posted by Barrie Davidson on May 18, 2001 6:56 AM

Now I understand. Bad news, I don't think there is a way to remove the word "blank" from your pivot table. A work-around for this would be to replace those fields that are blank with a space " " in the cell(s). I don't know, however, if this would work for you.

Sorry I couldn't provide more help for you.

Barrie


Re: Pivot table - remove "Blank"

Posted by Mark W. on May 18, 2001 6:59 AM
Barrie, PivotTables allow you to "rename" items.


Re: Pivot table - remove "Blank"

Posted by Barrie Davidson on May 18, 2001 8:17 AM
I know, but it appears to me that she is referring to "row" or "column" data (i.e., the fields that you drag into those areas in the pivot table wizard). I'm not aware of a way to filter out "blanks" in those circumstances. If you know how to do this please let me know, I'd like to know how to do it too.

Regards,
Barrie


Re: Pivot table - remove "Blank"

Posted by Susie on May 18, 2001 8:30 AM

That worked. I just hit the spacebar one time in the database if there's no entry and hit enter. When I refresh the pivot table it does exactly what I want it to -- it continues to display the whole row, but the word "blank" is now gone. That's much better than a work-around I tried where I copied the whole column to another column on the database and then in the main db column I used an if function to say if there's nothing in that column, then return nothing. That worked, but more complicated than your solution. Thanks again. By the way, you just have to remember that if you change the drop-down box to select just one or two items and then you need to change again to see everything be sure to click on the item in the drop-down box that contains just the checkbox with nothing beside it.


Re: Pivot table - remove

Posted by Susie on May 18, 2001 8:35 AM


Hi Dave, I'm new to this board so when I responded to Barrie not sure if everyone else was able to see. Barrie's solution worked. I tried your suggestion to select show all data, but I keep getting message something about the database having too many rows/columns.


Re: Pivot table - remove "Blank"

Posted by Mark W. on May 18, 2001 10:54 AM
> you just have to remember that if you change
> the drop-down box to select just one or two
> items and then you need to change again to
> see everything be sure to click on the item
> in the drop-down box that contains just the
> checkbox with nothing beside it

Yes! And it should be mentioned that there are
downsides to wholesale redefinitions of these
items -- You could easily lose track of which
items corresponded to your underlying data
values; there's no way (that I know of) to
retore the original item values (I believe
you'd have to create a new PivotTable from
scratch); and if you assign a new value to
an item and it later appears in your data list
then you'll end up with an item with an
appended number (e.g., "Apples2").

Bottom line: Use it sparingly! : I know, but it appears to me that she is referring to "row" or "column" data (i.e., the fields that you drag into those areas in the pivot table wizard). I'm not aware of a way to filter out "blanks" in those circumstances. If you know how to do this please let me know, I'd like to know how to do it too. : Regards,


Re: Pivot table - remove "Blank"

Posted by Mark W. on May 18, 2001 11:14 AM
No, she's referring to the items themselves not the
fields. The field labels can be changed quite easily...
Just double-click the field button and enter an new
value in the "Name:" field. The item change is more
subtle, and I might add is there for a reason: You
can group items together to create a new item. Suppose
you want to summarize "Oranges" and "Lemons" as "Citrus".
Grouping "Oranges" and "Lemons" will create a Fruit2
field with an "Group1" item. You'd then type "Citrus"
over this "Group1" item value. I know, but it appears to me that she is referring to "row" or "column" data (i.e., the fields that you drag into those areas in the pivot table wizard). I'm not aware of a way to filter out "blanks" in those circumstances. If you know how to do this please let me know, I'd like to know how to do it too.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.