Line chart showing line of those cells who have zero value

Hi Guys,
I am trying to create Line graph from a range but i am using vlookup formula to collect data from other sheet. My original file is too large, i attaching without formula file but showing Zero cells and Line graph.

If i have data till March or any month line chart will show Line of Actual From Jan to that particular month. but this chart is showing Zero sell after March.

Hope you guys understand.

My usual approach to this is to use a helper range. See the cells with red text in the attached.
By using an IF formula to check for a zero value and returning an error value if zero is found, then plotting the graph on that range you will get your desired result as Excel will not plot errors on a chart.

You can hide this range away so it's not seen and looks messy.
You will possible need to change a chart setting to allow hidden data to be plotted. You can do this by right clicking the chart, selecting "Select Data", clicking on the button that says "Hidden and Empty Cells" and putting a tick in the box next to "Show data in hidden rows and columns".

BSB

But i have this code in cells which are showing Zero. How and where to place (your) above mentioned formula.

Please Login or Register  to view this content.

You leave your formula as it is now and add a new row with my formula and hide it away.
Have a look at the attached version. Looks the same as your original sample (except the chart does what you want). My formula is in row 5 which I've hidden.

BSB

Thank you dear.