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

spydey

Active Member
Joined
Sep 19, 2017
Messages
293
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
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,696
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

Active Member
Joined
Sep 19, 2017
Messages
293
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,696
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

New Member
Joined
Mar 1, 2011
Messages
19
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

Active Member
Joined
Sep 19, 2017
Messages
293
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
 

Forum statistics

Threads
1,082,306
Messages
5,364,410
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top