That's a much improved visual presentation. Well done! And I did not know about the mmmmm format for months. I have a tip: at 6:05 in MAXIFS, or any of those IFS functions, you can just use "" and skip the &""
Great tip! I would simplify it a bit. In the Forecast Labels and Actual Labels columns you could simply write the texts "Forecast" and "Actual" instead of the max values. And then you wouldn't need to add additional series to the graph, you just need to add the data labels to the current series and point them to those column. Also you can edit the formating of the data labels so that they won't show 0 values (and not needing to add NA()). I enjoyed your video very much, keep it up! Thank you!
Thanks Ricardo - Let me work through an example with your suggestions, hopefully I can pick up a few tips too. If we just used the Forecast & Actual columns, wouldn't we get data labels for every data point along the line (apart from the blank cells)? And, in terms of the formatting the label, what if the value is 0? I still want the label to display. So I'm not sure that works in all cases.
@@ExcelOffTheGridi would just use a ghost space after the names actual and forecast for the label column names so that they are different, but not visible when in the chart.
@@JenMayB That would work, but I still wouldn’t do. It’s solving a presentation layer issue by breaking the semantic nature of column headers in the data layer. I would favour hard coded labels over changing the column names.
Interesting technique - thanks for sharing. As always with XL there are many ways to solve a problem. As an alternative, rather than using extra series, I would calculate the label similarly (using helper columns) but use the "Labels from cells" method of attaching these custom data labels to the existing series. Instead of using your IF() function to calculate a value, it would simply return the name of the series. Cheers!
@@ExcelOffTheGrid However - wouldn't that just show the value at every value point instead of just showing Actual (just once) at the end of the line? I tried to re-do your excellent example as I'm not a chart person (yet 😉) and I tried both combos and I thought your solution with a helper column was as great way to keep the chart clean. 💪👍
@@annemettevejlegardkristens9263 their suggestion is to calculate the Label Text (i.e the words “Actual” or “Forecast” in the helper columns, with the others showing blank). Then use the labels From Cells. So the label only shows against the points we want.
Thank you for your very useful instruction which I'm going to apply in my charts to make them easier to understand.
Glad it was helpful!
Certainly the presentation of the graph is much more attractive that way. Thanks for sharing Mark. Excellent!!
My pleasure! Thanks Ivan.
This is an excellent tutorial. Many thanks for sharing!👏
Very creative idea👍👍. Thanks Mark
Thanks Kebin 😁
Awesome as alway Mark. This is fantastic!
Thanks Jim, I'm glad you like it! 😁
That's a much improved visual presentation. Well done! And I did not know about the mmmmm format for months. I have a tip: at 6:05 in MAXIFS, or any of those IFS functions, you can just use "" and skip the &""
Thanks for the “” tip.
The syntax of all those functions feels wrong, so it doesn’t surprise me that there is another method. 👍
@@ExcelOffTheGrid It's a mystery that those functions require the quotes and the ampersand as opposed to what should be simple operands. Painful.
Great tip! I would simplify it a bit. In the Forecast Labels and Actual Labels columns you could simply write the texts "Forecast" and "Actual" instead of the max values. And then you wouldn't need to add additional series to the graph, you just need to add the data labels to the current series and point them to those column. Also you can edit the formating of the data labels so that they won't show 0 values (and not needing to add NA()). I enjoyed your video very much, keep it up! Thank you!
Thanks Ricardo - Let me work through an example with your suggestions, hopefully I can pick up a few tips too.
If we just used the Forecast & Actual columns, wouldn't we get data labels for every data point along the line (apart from the blank cells)? And, in terms of the formatting the label, what if the value is 0? I still want the label to display. So I'm not sure that works in all cases.
@@ExcelOffTheGridi would just use a ghost space after the names actual and forecast for the label column names so that they are different, but not visible when in the chart.
@@JenMayB That would work, but I still wouldn’t do.
It’s solving a presentation layer issue by breaking the semantic nature of column headers in the data layer.
I would favour hard coded labels over changing the column names.
Thanks for the tip. But still i wud prefer to add labels to my original line series. That way its more natural. As per my view.
Provided you have a method to do that dynamically so that you don’t have to keep updating it manually. Then, Yes, I agree.
Interesting technique - thanks for sharing. As always with XL there are many ways to solve a problem. As an alternative, rather than using extra series, I would calculate the label similarly (using helper columns) but use the "Labels from cells" method of attaching these custom data labels to the existing series. Instead of using your IF() function to calculate a value, it would simply return the name of the series. Cheers!
Yes, I can see that working too. That’s a good method. 👍
@@ExcelOffTheGrid However - wouldn't that just show the value at every value point instead of just showing Actual (just once) at the end of the line? I tried to re-do your excellent example as I'm not a chart person (yet 😉) and I tried both combos and I thought your solution with a helper column was as great way to keep the chart clean. 💪👍
@@annemettevejlegardkristens9263 their suggestion is to calculate the Label Text (i.e the words “Actual” or “Forecast” in the helper columns, with the others showing blank). Then use the labels From Cells. So the label only shows against the points we want.
Nice! but now I’ve got to go back and change all my charts😀
🤣 - yes, you do!!!!!
Great tip. If one created a chart starting in PowerPoint, does the chart function in PowerPoint support the formulas you outlined?
I should, but I've never tried it. Give it a go. Please let us know either way.