Line graph to ignore plot area with zero values

[heytherejem]

Hi experts,

Attached is one of several line graphs that need to be updated each month. They use the range which I've highlighted in yellow which is a percentage. At present, we manually move the plot range forward one each month. There are about 100 line graphs so this takes some poor shmuck half day of dragging range lines.

So... I'm trying to get excel to do it all for me.

So far, I've updated the range till the end of 2017 and created an iferror formula so the percentage cell is blank if there is nothing to calculate. But as you can see, my line graph has dropped to zero for these months. Is there a way I can tell the graph to ignore blank cells?
Or can you think of another solution?

The main issue is, next year I want to create 2018 in it's entirety and I really only want the graph to plot the months with data in them - I don't want a massive blank space at the end where it's waiting for data.

I hope this makes sense!

Jemma

[benishiryo]

hi there. for the charts to ignore the value, it has to be NA. you could use this in cell L57 for eg:
=IFERROR(L54/L$53,NA())

apply this logic to the rest too

[heytherejem]

Hi Beni,

Thanks that's worked! So does:

Please Login or Register  to view this content.

which I found out by accident!

But... my graph still has the horizontal axis range showing to the end of the year (although now there's nothing plotted there). Which is ok for 2017 as there's only 2 months left so it looks ok, but next year I will have 12 months of blank space.

Is there a way of having the graph know when to change it's own range? Or could a macro be used to update the ranges of all the graphs at once each month?

Thanks

[MrShorty]

Is there a way of having the graph know when to change it's own range? Or could a macro be used to update the ranges of all the graphs at once each month?

What you are looking for is a "dynamic named range", and there are many tutorials across the internet describing their use.
https://trumpexcel.com/dynamic-chart-range/
https://www.techrepublic.com/blog/mi...arts-in-excel/
https://chandoo.org/wp/2009/10/15/dy...t-data-series/

They all use the OFFSET() function to build the dynamic range: https://support.office.com/en-us/art...e-b4d906d11b66

[xlnitwit]

Using your existing IFERROR formula, the attached shows dynamic named ranges for each year (you can review them in Formulas- Name Manager). Hopefully it is fairly self explanatory, but let me know if not.

[heytherejem]

@xlnitwit - I can see that your document works exactly how I want it to (thanks!!) but I cannot see what you've done to make it this way.
If you would be so kind as to explain how you did it I can apply this across the rest of the datasets

Thanks so much for helping me with this!

[xlnitwit]

If you click the Formulas tab and then Name Manager, you will see the named ranges for the 4 series- Series_2014, Series_2015 and so on- and the formulas that they use to only include the cells with numbers in.

I cleared the existing data from your chart and then added those series one by one (via the Select Data dialog).

[heytherejem]

Hi xlnitwit - so if I want to do this across the other datasets, do I go to Name Manager and create four new series and apply the formulas you have used?

And then when I re-create the graphs, how do I select the range as you have? I can see when I use the Select Data dialog the 2014 range says this:
='550830d1512570103-line-graph-to-ignore-plot-area-with-zero-values-triangles.xlsx'!Series_2014

- forgive my ignorance I've never done this before!!

[xlnitwit]

Hi xlnitwit - so if I want to do this across the other datasets, do I go to Name Manager and create four new series and apply the formulas you have used?

Yes, assuming the other sheets have the same layout.

And then when I re-create the graphs, how do I select the range as you have? I can see when I use the Select Data dialog the 2014 range says this:
='550830d1512570103-line-graph-to-ignore-plot-area-with-zero-values-triangles.xlsx'!Series_2014

Create a blank chart, then right-click and choose Select Data. Then use the Add button to add new series, entering the relevant name and then the formula for the Values. Repeat for each series. It is a little bit fiddly and time-consuming initially but should save you time in the long run.

[heytherejem]

One more question about the graph.

The formula for the Series values... I am not selecting the range, right? How did you get it to pick up the range - do I just type the same info you have done '=filename!seriesname' ?

[xlnitwit]

You have to type it in. Two tips to speed that up-
1. Use the sheet name rather than the workbook name- i.e. enter ='Sheet name'!Series_2014 (Excel will convert it to the workbook name for you later)
2. When you do the first one, copy the formula so that you can paste it in for the others and just change the number at the end.

[heytherejem]

You, are a legend. I will work on this tomorrow and hopefully it will be plain sailing, if I get stuck, you may hear from me again!

You've been incredibly helpful xlnitwit thank you SOOOOO MUCH!!!! Not such a nitwit.

[heytherejem]

@xlnitwit.... I'm stuck.

I've gone through and created all the name manager ranges, and then when I've come to enter the range name in the chart series value, it comes up with an error saying it's not valid.

I've attached a copy of my working document, I've got all the calculations on one tab, and the charts which point to the calculations on a second tab.

I've taken a screenshot of what I entered in the chart series box that excel didn't like (on the screenshot tab) - any ideas where I've gone wrong???

Jemma

[xlnitwit]

Your 2014 series formula is wrong as it starts with B15 but then indexes into row 36.

[heytherejem]

Quite right, that I did. I was just testing your attention to detail!
Seems to be working now, only 99 more to go

[xlnitwit]

You might want to consider creating a separate table of formulas linking to the original data to create your charts from. I suspect it would greatly speed things up. Or perhaps use some code.