+ Reply to Thread
Results 1 to 9 of 9

Don't want chart to display empty cells

  1. #1
    Registered User
    Join Date
    12-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Don't want chart to display empty cells

    Hi All

    I have created a chart that I can use to monitor fuel consupmtion of my new car. I have a chart that shows how many litres of fuel I use over 100kms.

    In my data table I have about 30 rows. 3 of those rows are filled in with data and the rest are blank. When I want to display the next row of data, which will be the 4th, I have to go into the "select data source" and change where the chart is reading from. I am trying to find a way to have the entire 30 rows of data selected as the data source with only the rows that have been filled in displaying in the chart.

    At the moment when I select the entire column as the data source the chart displays all of the empty cells.

    Is there a way I can do this?

    Cheers

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2007, 2010
    Posts
    30,744

    Re: Don't want chart to display empty cells

    Welcome to the forum.

    See http://peltiertech.com/Excel/Charts/Dynamics.html and http://www.tushar-mehta.com/excel/ne...rts/index.html
    Microsoft MVP - Excel
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Don't want chart to display empty cells

    Thankyou very much for that. It is exactly what I needed.

    However I have run into another problem. I am trying to run a couple of graphs from the same spreadsheet using the same "X" axis values.

    I managed to get the first equation in and it works perfectly. However, Any subsequent graphs are not working. I can type in all the correct references and hit enter and nothing happens. The cursor keeps flashing within the formula bar.

    I don't know how to further explain myself on this problem. I am just hoping you know what I mean

    Thanks again for your time.

    Cheers

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Win 2003-2013
    Posts
    10,215

    Re: Don't want chart to display empty cells

    Hi Dakota,

    can you upload a sample? This is hard to remote troubleshoot, but I'd be happy to have a look

  5. #5
    Registered User
    Join Date
    12-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Don't want chart to display empty cells

    Thanks Teylyn

    Please find attached the file I am working on.

    I need all series of data on the "Fuel Consumption" and "Cost per Litre" charts to grow in length everytime I add additional data.

    You'll see that the blue line in the "Fuel Consumption" chart is good and working fine. The other 2 data series do not work.

    Thanks for your time

    Cheers
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Don't want chart to display empty cells

    Just had a look at the file from this thread and it doesn't seem to be displaying the same as the file on my computer.

    It says it is something to do with the defined names not being available.

    I'm sorry I don't know how to solve this either . Is there a format I should be saving this file in?

    Cheers
    Last edited by Dakota27; 12-09-2009 at 02:01 AM.

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Win 2003-2013
    Posts
    10,215

    Re: Don't want chart to display empty cells

    opens up fine for me, no errors. Gimme a bit ...

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Win 2003-2013
    Posts
    10,215

    Re: Don't want chart to display empty cells

    Some of your series still reference to the absolute cells instead of the dynamic range.

    Go to the Select Data dialog and edit the series.

    Enter this in the Series Values box

    ='Fuel Data'!CperL
    ='Fuel Data'!Lper100

    Excel will also store the full file name with these names, but when you enter them in the dialog, it is sufficient to use the syntax

    ='Sheet name'!RangeName

    hth

  9. #9
    Registered User
    Join Date
    12-08-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Don't want chart to display empty cells

    Thanks heaps for that.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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