• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Query

BharathBabu

New Member
Hi,


I have a query in the pivot table...

How to get the blank cell in the pivot table. i used the formula

=IF(ISERROR(VLOOKUP(Data!C2,Data!$C$2:$C$30,1,0)),"",VLOOKUP(Data!C2,Data!$C$2:$C$30,1,0))


Data:

A1: Company Name, B1---> Date, c1-----> Gold, D1---->Silver & E1----> Status


Customer Name-----Date------Gold------- Silver--------Status

TCS---------------1-Jan-13----2 --------Nil-----------pending

APS---------------1-Jan-13----Nil--------20-----------completed

INDUSTRIAL--------1-Jan-13-----15--------Nil----------pending

TEXAS FIRST-------2-Jan-13-----25-------Nil-----------pending

CIRCLE------------2-Jan-13-----Nil-------10-----------completed


When I create the Pivot table for the above data...it show the below table

Row Labels-------------completed-------pending------Grand Total

TCS

Sum of Gold------------blank cell----------2---------2

Sum of Silver----------blank cell----------0---------0


My query is, how to get pending "Sum of sliver" should be blank cell instead of "0"....

Please advise....
 
Hi BharathBabu,


You use GETPIVOTDATA() instead of this formula and format cell to
Code:
#;#;""
to hide zeros.


Regards,
 
Hi Bharath,


Yes we do face this problem in Pivot tables when we use Formula's in our data.


A better way is to show them as "-" which means No Data.


Please follow below instructions in case you need "-" instead of Zero's.


Select your Entire Pivot Table.

Press Ctrl + 1 - This will openup Format Cells Window

Under Number tab, click on Custom

Select 5th last option from bottom under Custom Window which will look like :

_-* #,##0.000_-;-* #,##0.000_-;_-* "-"??_-;_-@_-


Press OK. Now all Zero's will be converted to "-" which depicts clear picture in your Pivot table.


---------------------------------


I use the above method but if you still need to show blank cells in Pivot tables, you can follow method.


Change your formula to show figures as error if not matched.

Change from =IF(ISERROR(VLOOKUP(Data!C2,Data!$C$2:$C$30,1,0)),"",VLOOKUP(Data!C2,Data!$C$2:$C$30,1,0))

To

VLOOKUP(Data!C2,Data!$C$2:$C$30,1,0)


Your table will show Error values where Vlookup could not find Match

Create a fresh Pivot on this table.

Right Click inside your Pivot table & select Pivot table options(2nd last Option).

Under Layout & Format tab - Under Format

Tick Option For Error Values show:

Click Ok.

Now the Pivot table will show blank cells where we had Error values.


Hope this helps.
 
Back
Top