Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel Chart from variable data to ignore zero in 1 column

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Chart from variable data to ignore zero in 1 column

    Hi,

    I am trying to get data to plot in a chart which is taken from a Vlookup on the date, so only some of the dates have data beside them, others have #N/A (can change this to whatever I want).

    My aim is to produce a Bar chart that only shows the dates that have any data - for example my graph I envisage as below:


    5
    4
    3
    2
    1
    02/03/15 04/03/15 15/03/15 16/03/15 31/03/15


    currently I get every date of the month along the X axis due to my column having each date listed even if the value is zero, can anyone suggest a way to fix this without having to amend the source data?

    Thanks

    J

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Chart from variable data to ignore zero in 1 column

    My tutorial Dynamic Charts explains how to define Names based on whether cells contain numbers, and how to use these Names to create dynamic charts.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Chart from variable data to ignore zero in 1 column

    Hi

    I have had a look at this and it works for removing blank values from the end, however I also have blank values in the middle of my data set I need removed from the charting:
    Date Amount ()
    01/03/16
    02/03/16 -1298.07
    03/03/16
    04/03/16 1491.77
    05/03/16 -2410.04
    06/03/16
    07/03/16
    08/03/16 -742.67
    09/03/16 -9342.59
    10/03/16
    11/03/16 559.5
    12/03/16

    have you any suggestions?

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Chart from variable data to ignore zero in 1 column

    What kind of chart? Do you want all dates to appear on the axis, so the spacing is apparent, or do you want only the dates that have a corresponding amount, with spacing independent of elapsed time?

    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    New Member
    Join Date
    May 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Chart from variable data to ignore zero in 1 column

    Hi,

    I am only looking for the dates that have a corresponding amount - I can't

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Chart from variable data to ignore zero in 1 column

    Here's your data and a line chart. I've selected the data and pressed Ctrl+T to convert it to a Table, which is a powerful data feature in Excel.



    Here's how to apply a filter to the data. Click the dropdown on the Amount column header. Click Number Filters, then Custom Filter, then find "does not equal" in the left dropdown of the Custom AutoFilter dialog (I suppose you could have just selected Does Not Equal in the pop-up menu), and keep the second dropdown blank.



    Here's the table and chart with filtered data. Note that rows are hidden. By default, charts do not show data from hidden cells. Note also that charts resize when rows and columns under them are resized, so the chart has shrunk. You need to resize the chart. (You could have formatted the Chart Area of the chart, and changed the Properties so it moves but doesn't resize with cells.)

    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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