Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Not to show zero data in the chart

  1. #1
    Join Date
    11th October 2005
    Posts
    54

    Not to show zero data in the chart


    Currently I had created a data for Jan-Dec 06 which I cover the whole
    data to create a chart.
    The reason I cover the whole year data so that the info is automatic
    updated in the charts whenever there is value in the particular month.(link from another book/worksheets

    Is There any way not to show the Zero value data in the Charts without removing the formula as I need it to update if there is a value.

    Anyone can assist or Have a better idea to do ?

    A sample is enclosed
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,286

    Re: Not to show zero data in the chart

    Change your formulas so that they return #N/A instead of formula blanks (""). For example, use...

    =IF($C$2<C5,"#N/A",Jan!B8)

    Then, use conditional formatting to hide #N/A...

    1) Select C6:N7, making sure that C6 is the active cell

    2) Format > Conditional Formatting > Formula Is

    3) Enter the following formula...

    =ISNA(C6)

    4) Choose 'White' as your font colour

    5) Click Ok

    Hope this helps!
    Last edited by Domenic; March 12th, 2006 at 01:21.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Not to show zero data in the chart

    Hi,

    In your formula replace "" with NA()
    VB:
    =If($C$2<C5,NA(),Jan!B8) 
    
    
    If you only want two months displayed then you need to look at dynamic named ranges.

    Cheers
    Andy


  4. #4
    Join Date
    11th October 2005
    Posts
    54

    Re: Not to show zero data in the chart

    Hi Domenic/ Andy,

    Thanks for the help .

    It's what I want

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Show Text Box On Chart
    By duffer4126 in forum EXCEL HELP
    Replies: 6
    Last Post: August 20th, 2008, 14:46
  2. Show Chart Data Series Labels On All Series. 2007
    By Joshua Ng in forum EXCEL HELP
    Replies: 4
    Last Post: June 1st, 2008, 18:10
  3. Show Data Labels On Bars Of Bar Chart
    By markc in forum EXCEL HELP
    Replies: 8
    Last Post: February 21st, 2008, 23:50
  4. Show Data With Chart
    By mikezang in forum EXCEL HELP
    Replies: 5
    Last Post: December 18th, 2006, 16:59
  5. CHART OPTIONS = Show Data Table
    By stupid1 in forum EXCEL HELP
    Replies: 1
    Last Post: March 31st, 2005, 16:27

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
  •  
porno