# How to stop (blank) appearing in a powerpivot 2013 Pivottable



## masplin (May 11, 2015)

When I product a pivot out of PP 2013 and arrange in tabular form I get (blank() in every cell where there is no data i.e. in the data model the field is blank.  I tried the layout setting "fro empty fields show", but even if i put zzz nothing changes. I even changed the formula in PP to be sure the cell really is a blank.  there must be a way to make this return a blank as look rubbish?

Thanks 

Mike


----------



## edkibb (May 11, 2015)

Hi Mike,

Try this How to Hide, Replace, Empty, Format (blank) values with an empty field in an Excel Pivot Table without using filters

I've used this before and I think I had the same problem you describe.

Thanks,

Ed


----------



## masplin (May 11, 2015)

ah good idea. Of course you wonder why the "show empty cells" as function doesn't actually do anything!!!

Thanks for tip


----------



## masplin (May 11, 2015)

actually doesn't work for me in Excel 2013 with pivot out of Powerpivot. Really irritating!


----------



## scottsen (May 11, 2015)

Unless i am crazy...

By default Excel will hide any rows where ALL columns are blank.  If you have a column that has data... the row is going to show.

Is that what is happening? (You are want to hide a row... where just 1 column is blank?)


----------



## masplin (May 12, 2015)

[No its where you say put 4 items in the rows and do a tabular layout. If on one row 3 fields have data and one is empty it puts (blank) in the empty field. Say like an address with Addr1,addr2,addr3, postocde. Addr 3 is blank so looks rubbish and you cant then cut and paste to use elsewhere

Mike


----------



## SpillerBD (May 13, 2015)

In your source data use the following code for Text/label cells

```
Sub Blank_to_Zero()
    Dim r As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    For Each r In Selection
        If r.Value = "" Then r.Value = " "
    Next r
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True 'Not necesary since Screen updating automatically restores at end of Macro
End Sub
```
For cells that should have a numeric value
Change  If r.Value = "" Then r.Value = " "
to: If r.Value = "" Then r.Value = 0


----------



## ryan_b3 (Mar 1, 2017)

scottsen said:


> Unless i am crazy...
> 
> By default Excel will hide any rows where ALL columns are blank.  If you have a column that has data... the row is going to show.
> 
> Is that what is happening? (You are want to hide a row... where just 1 column is blank?)



Hi Scott, do you know how I could accomplish exactly what you describe -hiding a row if it does not have data for one of the particular columns/fields?


----------



## steve07805 (Apr 27, 2022)

I had this problem (When creating a pivot from power pivot, any blank cells are shown as (Blank) rather than just being empty or 0). After much playing around I found that in powerpivot, when you create the pivot table with the Pivot table button, create it as a "Flattened Pivot table". Seemed to work for me


----------



## steve07805 (Apr 27, 2022)

steve07805 said:


> I had this problem (When creating a pivot from power pivot, any blank cells are shown as (Blank) rather than just being empty or 0). After much playing around I found that in powerpivot, when you create the pivot table with the Pivot table button, create it as a "Flattened Pivot table". Seemed to work for me


Also.... Change the power pivot column "data types" and "formats" to be Whole Number or Decimal Number before you create the Flattened Pivot Table


----------

