Get the free blank chart form

All Rights Reserved.

In this video we'll have a look at skipping blank cells in charts using dynamic formulas now this approach is not just for blank cells, but it's also for skipping error values as well so not only do we not plot them on the y-axis, but we also don't plot them on the x-axis let's take a look at option two of ignoring blank cells by doing this all in a dynamic way and by dynamic I mean we are going to add a data preparation table in the middle that our charge is going to refer to and in this data preparation table we are already going to filter out the blanks, so it's going to be a very nice dense data preparation table that's only going to include the cells here where we have a date and I need these two right here okay, so the first challenge is to come up with a formula that when I drag it down it's just going to exclude these lines the formula that comes to mind in this case is an index formula because index is great for returning whether you have text or numbers I'm in this case they're both numbers because date is pretty much a number but even if you had text here it has no problem to return any of these right, so it's a great lookup formula in that respect if you're not familiar with index I have a series on this I have the index match basic series and index measure advance I'm going to put a link to these videos in the descriptions below so make sure you check that out just so you fully understand the power of this formula ok, so index could work here what it needs as a first argument is the array and always think of array as the area where your answer is in this case where is the area that my answer is if I'm typing the formula right here I want to date right, so my answer can only be a date, and it could be a date anywhere in here, and I'm gonna pull this formula down a bit more because I could add in more stuff here, and I still want my charts and everything to work fine so depending on how much data you have you can pull this down further in many cases I'll just leave it to that I'm going to fix this now the next argument in here is the role argument and that's pretty much the only argument I need here because I just have one column not going to move anywhere I just need to get the right argument for the row argument so if I would put one here that means return the first in there right that should be my first date it's just not formatted as a date so let's get the formatting here if I pull this down I do want the second date to come and then the third date but only if it has a value I could do something like this make indexes here let's just pull this down and instead of saying this I could reference this out and go down, but obviously I need to make an exception and say the moment it's empty skip that that's something I need to do here for these indexes I need to give this index function the correct address every time I move down in the first instance this fine number two is fine but when it comes here when it actually comes to the...