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

[masplin]

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]

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]

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

Thanks for tip

 

[masplin]

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

 

[scottsen]

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]

[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]

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]

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]

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]

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