How-to Create a Dynamic Excel Chart that Only Displays Non-Blank Values

Below is a video that may help you as well.

How-to make a Dynamic Excel chart that doesn’t show blank values so you don’t have to use filters.

It uses a combination of Index and Match functions along with an Offset function.

Here are other links to posts that involve Index/Match and Offset Function techniques to build Excel Charts:

This is the Bomb: or How I came to love the Offset function

 





Video Demonstration:  http://youtu.be/JA2r_9RjLCs

 

Steve=True





7 COMMENTS

  1. Hi Am Roderick and i found the “How-to Create a Dynamic Excel Chart that Only Displays Non-Blank values” create however i need to use that for multiple columns meaning if i have A B C D E as Axis and have multiple Data point as G H I J and in all of them i have data except for G the formula doesn’t show the name of the axis.

    I hope you understood what i meant hope i hear from you.

    Regards Glynn

  • Hi Glynn, I think I understand what you are doing. If you require all the data points from several columns, then I would suggest the following:

    1) Create another helper column that counts the data in the G-J. If your total in the helper column = count of 4.
    2) Change your formula so that it only looks at your helper counting column and hides if it doesn’t equal 4.

    Does that make sense?

    Steve=True

  • Hi Steve,

    Same issue here. I am trying to present certain data in a bar chart only without the gaps. I just found your video on Youtube – the single column solution works like charm. But I would like to present multiple ‘data points’ as columns.

    I’m afraid I can’t quite understand your response to the previous comment citing an another helper column.

    If you could just tell how should I modify the formulae exactly that would be great.

    Thanks in advance,
    Gergely




  • If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  • Excellent video. I followed the steps you described – works like charm. At work, I’m facing a similar issue but instead of having to have to deal with a single column I’ve got 4 of them set as filters (that’s as if you’d have to deal with ‘Peso Bruto’ ‘Total km’ and ‘Zona’ columns to deal with as well).
    I’d like to present these figures in a single stacked bar chart ,without gaps of course. As of now, I can’t arrange figures in a single column.

    Your help would be greatly appreciated as I’m chasing a strict deadline.

    Thanks,
    Gergely

    • Hi Gergely, sorry for the late reply. I sent you an email so that you can send me your data. Let me know if you already figured it out.

      Steve=True

  • Hello Steve, I have watched your How-to Create a Dynamic Excel Chart that Only Displays Non-Blank Values video numerous times with awe. It does exactly what I am needing for a major project. Unfortunately, having entered the array formula into where I need it I get a #NUM! error.

    Although I have been working and programming in Excel for a couple of decades there are ares where I am obviously week. Index and Match are two of those areas. I have tried pulling the mega formula apart to get snippets of it to work and then try piecing it back together to no avail.

    My formula – {=IF(COUNT($M$2:$M$20)>=ROWS(M$2:M2),INDEX($J$2:$J$20,MATCH(SMALL(IF(LEN($M$2:$M$20)>0,ROW($M$2:$M$20),””),ROWS(M$2:M20)),ROW($M$2:$M$20),0),1),””)) where Column J is the X Axis information on the Chart and Column M holds the data points.

    Do you have a moment to review for any obvious mistakes, or have any suggestions?

    Thank You,
    Mark

    • Hi Mark, sorry for the late reply. I don’t see anything obvious. I would recommend removing the ELSE part of your IF(LEN($M$2:$M$20)>0,ROW($M$2:$M$20),””) part of the formula as this may be causing the problem. You want a false probably which you will get if the else hits vs a blank which could be the problem. Did you find the answer?

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here