+ Reply to Thread
Results 1 to 15 of 15

Chart ignore non filled in columns

  1. #1
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Chart ignore non filled in columns

    Hi,

    I am trying to create a chart which will exclude all non filled in columns. I am trying to create something fool proof, so I have all the 52 weeks of the year already created, it is just still blank.

    I am trying to make the X axis pull data only until the data is filled.

    Do I have to go down the macro path?

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    Your explanation is a bit vague.... Try this out, though. Happy to explain as required.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Thank you Glenn,

    How would you do it the other way around. This is exactly what I need, but instead of the information is not filled in vertically, it is not filled in yet horizontally.

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    Try this. Your description WAS too vague!!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Thank you Glenn, it might have been vague, but I was not sure what I was looking for.

    This is great, although I am not sure if there is a solution if my reference table is feeding from an other table. I have functions in the reference table, resulting in "" (of course it can be 0 as well). Therefore It is finally solving my problem of being dynamic, but still trying to show all the 0s or empty cells.

    Also, I have more than 4 rows of information. Could you help me out how to add the rest of them in a quick way?

    I appreciate your help and time.

  6. #6
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  7. #7
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Attached. Thank you.
    Attached Files Attached Files

  8. #8
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    More vagueness.... I had hoped that you might have posted a file with raw data, your formula all in place and an attempt to create the dynamic chart. But... Now you want me to guess how your layout looks, create the data table from my imagination, create a graph.. again, create the named ranges... again.

    No. that's not how it works, I don't play guessing games forever. I have modified my file, to incorporate a formula in the data table and to train the functionality. Try to implement it yourself. YOU know what your data layout should be. I don't.

    I'm more than happy to help further if/when you hit a snag... but, for now, over to you to try to implement this.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    I hope this help. Appreciate the help.

    Trying to create the chart based on the bottom table. Raw data, and all the functions are in there.
    Attached Files Attached Files

  10. #10
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    I have done areas 1 - 3. Firstly, add the remaining named ranges (CTRL-F3) for the other areas. Then add the remaining datasets to the chart chart tools/design/add data usin variants of the formulae already in place.

    my only remaining concern is that, with 15 areas, it could look rather messy....
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    I have two further questions to you Glenn,

    1. when you add the named are with (CTRL+F3) how do you add the formula and the naming quickly. so I dont have to retype everything.

    2. How do you add them quickly based on the naming to the chart? The series values, it is based on the named range. I would type it out by hand and I suspect there is a more elegant way.

    Thank you!

  12. #12
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    I wrote area1 out and then copy pasted into area 2, manually adjusting the rows. Same for the additional datasets. Takes maybe 15 seconds per named range, a bit less for each dataset.

  13. #13
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Glenn, this is strange. I wrote the named areas, and build the chart step by step as well. And for some strange reason, it is still capturing my 0s on the X axis. Do I need to change some settings? MY master file looks the exact same.

  14. #14
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    It counts the empties in row 50. None of them are empty. The master file I have and the file I shared looks the exact same. I dont understand

  15. #15
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    18,130

    Re: Chart ignore non filled in columns

    Im away out for a couple of hours. You'd need to post your sheetas I can't see what I can't see....

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1