Pivot table - remove "Blank"

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.

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.

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

Susie, just type over the value, "(blank)",
with a space character.

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

Barrie, PivotTables allow you to "rename" items.

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

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

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.

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

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.