+ Reply to Thread
Results 1 to 3 of 3

Hide blank data - but show axis (Pivot Chart)

  1. #1
    Forum Contributor
    Join Date
    Forney, TX
    MS-Off Ver
    Excel 2016

    Hide blank data - but show axis (Pivot Chart)

    Hey everyone!

    I have an issue here that is driving me absolutely mad. I have data that is based on Week Ending dates, and then I have a few values there.
    The chart needs to show all the weeks for this year, but I don't want to graph when there is no data (or when the date is in the future). I have tried a billion solutions and I cannot get this to work.

    If you look at the sample you can see how there is no data for the next 3 weeks, and the line graph is graphing those zeroes.

    I tried putting formulas in my data to not show a 0 when the date is past today, but the graph still graphed it as zero. (as shown in the attached sample)
    I also tried adding a helper column to check if the date was past todays date, then filtering the pivot table by that helper. This works, but it removes all the dates from the chart. I want to show the dates, just not the line graph part for those dates.

    I have attached a small sample, and in there I put an image below the chart of what the desired result is.

    Thanks in advance. Been on this for hours and has gotten me so frustrated.

    (My assumption is because the column (Total Calls) is a number, it will also map a 0 if there is anything else there. But there has to be a way to show all the dates without mapping the data..I am just too stupid to figure it out, lol)

    I uploaded the wrong sample so if the sample seems wrong, please try the updated one (oops)!
    Attached Files Attached Files
    Last edited by NewYears1978; 01-23-2023 at 04:54 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver

    Re: Hide blank data - but show axis (Pivot Chart)

    This is going to be a fun one. Read up on the Offset Command.

    It has two forms, the long form is used to define a range "from scratch" = Offset(Start Here, go down rows, go right columns, return # rows, return # columns)

    So Plot_Date =OFFSET(Pivot!$A$4,0,0,COUNTA(Pivot!$A:$A)-2,1) means
    - Start in A4
    - Go down zero rows
    - Go right zero columns,
    - Return a range COUNTA(A:A)-2 rows and 1 column.

    The produces the range A4:A8

    And Plot_Pct_Res =OFFSET(Pivot!$B$4,0,0,COUNTIFS(Pivot!$B:$B,">0")-1,1) means
    - Start in B4
    - Go down zero rows
    - Go right zero columns
    - give me a range COUNTIFS(B:B, ">0") -1 (Count of non zero Res Pcts minus one for the Grand Total Row) rows
    - 1 Column wide

    This is the range B4:B5

    The short version is used when you already have a range defined.

    Plot_Pct_Com =OFFSET(Plot_Pct_Res,0,1) which is give me the same range as Plot_Pct_Res except shift it down zero rows and right one column

    The rest of the definitions are shown in columns O:P - you can delete these if you wish.

    Note that I have a definition for Plot_Blank which is the blank column after the pivot table. This named range is used to force the dates.

    I created a blank chart that will use these ranges
    - Right Click on the chart and select Select Data
    - Click Add and give the series a name
    - in the series value box type 'pivot'!plot_pct_res

    Do the same for plot_pct_com and plot_total_Calls

    Then fill out the Horizontal Axis label with ='pivot'!plot_Date

    Now you have a chart with only two dates.

    Go back and add a 4th series: plot_blank

    Now your chart has all the dates but only information for non-zero percents.

    Blank will show up in the legend. Click on the legend and while it is selected, click on the blank series. Then press the delete key.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    Forney, TX
    MS-Off Ver
    Excel 2016

    Re: Hide blank data - but show axis (Pivot Chart)

    Wow that is in depth and complex. I will mull this over and see what I can figure out and report back. It might be a few days so don't think I am ignoring you if it takes a few days/weeks.

    Much appreciated!

    Edit 2-1-23:
    I started implementing this, then got pulled on another big project, still going to be testing it just didn't want you to think I forgot!
    Last edited by NewYears1978; Yesterday at 01:21 PM.

+ 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. Hide/Show Pivot Chart Filters
    By Beefhamburger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2021, 12:19 PM
  2. How to show % on horizonal axis of Pivot Chart
    By Lynneth in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-05-2020, 05:46 AM
  3. Replies: 1
    Last Post: 08-28-2019, 03:09 PM
  4. [SOLVED] Pivot table hide rows if all month values are NOT Empty, show if at least one BLANK.
    By saudi_red_neck in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-25-2019, 11:02 AM
  5. Hide X axis labels if its with a blank data
    By Rishi Rai in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-21-2012, 10:24 AM
  6. Replies: 2
    Last Post: 08-05-2010, 11:40 AM
  7. Replies: 0
    Last Post: 06-02-2009, 12:29 AM


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