Pivot Table (blank) text

[Portucale]

Hi,
I am having an issue with removing the (blank) text from the Pivot Table when the field is blank/empty in the data source, I've tried the:
PivotTable Options > Layout & Format > Format > For Empty Cells show:
and nothing changes!!

Any ideas?

Any help and all the help is appreciated,

Kind regards,

 

[Chihiro]

That's because that option only applies to Value field and not to Row Label.

Just click on one of Row Label item containing "(blank)" and replace it with space. All the other ones will be replaced with single space as well.

See attached.

 

[chirayu]

Either what @Chihiro said or the below link I found

https://blogs.technet.microsoft.com...vot-table-without-using-filters-step-by-step/

 

[Chihiro]

Just as side note. I'd always recommend documenting any such change applied (additional sheet that tracks assumptions, Named Ranges, business rules etc). For business continuity/succession purpose.

 

[Portucale]

Thanks guys,

The issue is that I have a Userform showing some fields from the PT, and although I can "hide" the text (blank) in the PT itself the same is not possible in the Userform, hence the issue.

Is there any solution for the Userform? I didn't find any, so I had to create a reference column with the ISBLANK function and use that column to show as blank, hope this makes sense

Thank for the hints,

 

[chirayu]

I don't really know how your UserForm functions so not sure what type of solution (if any) that I can provide. You'd have to provide a sample file with the form.

 

[Portucale]

Thanks Chirayu

Is very kind of you to see this further, enclosed is the workbook with the Userform, which loads on double click in any cell within the sheet "pt", then you will see the data within the first three columns in that row. You also will notice that there is 2 PT, PivotTable3 is as "normal" just the Labels as they are in the source; PivotTable1 have a Conditional Format replacing the values (blank) by ";;;", so the text (blank) isn't visible.

Thanks in advance,

 

[chirayu]

I just stuck an IF condition on the bit that populates TextBox3

 

[Portucale]

I just stuck an IF condition on the bit that populates TextBox3

Hi Chirayu,

Why I always try to complicate things!!, in fact, after I update the post had a 'brain wave', thinking about the same solution, but didn't ry it, as I thought wouldn't work

Thanks very much for all your help.
Vitor da Fonseca