+ Reply to Thread
Results 1 to 9 of 9

Need zero values to not be shown on pivot chart please

  1. #1
    Registered User
    Join Date
    11-29-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Need zero values to not be shown on pivot chart please

    Hi,

    I have a pivot table and chart attached. I do not want the zero values to be shown on any charts. can anyone help?

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need zero values to not be shown on pivot chart please

    a simple solution would be to modify your calcs on row 56 on data tab, and replace the 0 (in the IF) with either NA() or 1/0

    if you repeat for each column, and refresh the Pivot you should find those data points are subsequently ignored in the Chart.

  3. #3
    Registered User
    Join Date
    11-29-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Need zero values to not be shown on pivot chart please

    Hi, sorry, I replied already but not sure if it got through. thanks for your solution - I copied both the NA() and 1/0 across all formulas but when I refreshed the pivot all the data was showing either NA or DIV/0 so no data on chart at all. I may have done something wrong. Any chance of making the change to my s/s and sending it through? thanks so much for your help.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need zero values to not be shown on pivot chart please

    refer attached - only change being modification of row 56 calcs (which propagate given Table), and refresh of Pivot.

    I am using O365 so, if this doesn't render correctly for you then this implies a difference in behaviour between the versions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-29-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Need zero values to not be shown on pivot chart please

    thanks for sharing. The solution somehow doesnt work for 'all' programs and projects - just gives NA for everything. Strange. Thanks for responding to me. Appreciate the support. Best, Shane

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Need zero values to not be shown on pivot chart please

    Shane,

    I see what you're saying - yes, where you end up with #N/As across the categories for given intersection the resulting Pivot won't display correctly.

    I am not sure there's an elegant way around this -- you might consider something ugly like the below ?
    (to test, right click on the Pivot Graphs tab, select Code and paste below into resulting window)

    This basically just:

    1. copies the Pivot Range (bar Page Fields) to AA1, whenever the Pivot is updated
    2. replaces the 0s in that range with #N/As before
    3. re-setting the source range of a new standard Chart (named Chart ALT)
    obviously, you'd need to setup axis limits / labels / headers etc per preference

    Of course, the major downside is that the new Chart may not prove quite as flexible as the Pivot equivalent - it would pick up data field additions / removals etc by default.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    76

    Re: Need zero values to not be shown on pivot chart please

    In Q56
    =IF(Q2="",#N/A,+P56+Q2)

    Instead of 0, drag across and down, then refresh your pivot/graph.

    Line graphs will not pick them up but if you use your raw data elsewhere that requires calcs then this isn't a good idea.
    If you like my answer please *Add Reputation

  8. #8
    Registered User
    Join Date
    11-29-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Need zero values to not be shown on pivot chart please

    thanks for the reply - much appreciated !

  9. #9
    Registered User
    Join Date
    11-29-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Need zero values to not be shown on pivot chart please

    thank you for the additional reply XLent and the extra details you have posted. Much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 02-04-2020, 03:47 PM
  2. Pivot calculated fields, summaries, and values shown on chart issue
    By LewisBosworth in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-14-2019, 07:23 PM
  3. Replies: 1
    Last Post: 01-03-2018, 12:51 PM
  4. [SOLVED] filtering a pivot chart / pivot table on x-axis values
    By Trebor777 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-20-2016, 01:28 PM
  5. access pivot table chart with multiple Ranges shown
    By superchew in forum Access Tables & Databases
    Replies: 1
    Last Post: 09-29-2014, 12:48 AM
  6. Pivot Chart displaying percentage values as decimal values
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-05-2013, 05:02 PM
  7. chart to be shown in new sheet
    By asdzxc in forum Excel General
    Replies: 3
    Last Post: 04-26-2012, 08:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1