Pivot Table with both % & Text - Showing 0 (zero)

[spydey]

My data table has several columns with both numbers, displayed as %, and Text. The text is: N/A.

Please note, it is not #N/A, just simply N/A, and is text, not the result of a formula/function.

When I pivot out the data table, the N/A are being shown as a 0 and thus 0.00%.

I need to carry over/display the N/A as such in my pivot table, instead of a 0.

I have gone through various menus, options, blogs, websites, etc, and can't seem to find a way to do it.

I have found references to #N/A, but that is coming from the result of a formula/function.

My end goal is to make a pivot chart. I need to have the data points in the chart, where there is a N/A, be blank, not 0 on the chart .... just blank, lacking data, nothing there.

Also, my chart will have a data table as one of the chart elements, and so I need the N/A to show up in there instead of the 0.

Any thoughts, suggestions, ideas, etc?

-Spydey

 

[SpillerBD]

My data table has several columns with both numbers, displayed as %, and Text. ...
-Spydey

You should not be mixing data types for a field.
However, did you look at the Pivot Table Options\Layout and Format tab?
"For error values show:"
&
"For empty cells show:"

 

[spydey]

You should not be mixing data types for a field.
However, did you look at the Pivot Table Options\Layout and Format tab?
"For error values show:"
&
"For empty cells show:"

Yes, I had tried those, but I think I figured it out.

I am going to change all the N/A to #N/A so that it is perceived as an error and will in turn leave it blank instead of a 0.

Thanks SpillerBD for your help. It got me on the right track.

-Spydey

 

[SpillerBD]

I am going to change all the N/A to #N/A so that it is perceived as an error and will in turn leave it blank instead of a 0. -Spydey

Just remember, it must be an actual error as opposed to be a text string of "#N/A"

 

[yuvastanza]

My data table has several columns with both numbers, displayed as %, and Text. The text is: N/A.

Please note, it is not #N/A, just simply N/A, and is text, not the result of a formula/function.

When I pivot out the data table, the N/A are being shown as a 0 and thus 0.00%.

I need to carry over/display the N/A as such in my pivot table, instead of a 0.

I have gone through various menus, options, blogs, websites, etc, and can't seem to find a way to do it.

I have found references to #N/A, but that is coming from the result of a formula/function.

My end goal is to make a pivot chart. I need to have the data points in the chart, where there is a N/A, be blank, not 0 on the chart .... just blank, lacking data, nothing there.

Also, my chart will have a data table as one of the chart elements, and so I need the N/A to show up in there instead of the 0.

Any thoughts, suggestions, ideas, etc?

-Spydey

My Response:

Hi
I guess if you are having five columns one of the column has no Values but NAs, if so, select the NA valued columnàCtrl + 1, àSelect Customàtype the following formula in the custom mode
General;-General;"NA" à click okay.

Now you will see the results you wanted.

 

[spydey]

Just remember, it must be an actual error as opposed to be a text string of "#N/A"

Interestingly enough, I changed the text string from N/A to #N/A and it is working now as hoped/expected.

I will keep in mind what you said though incase I come across any issues.

Thanks again!

-Spydey