Thank you for your very useful instruction which I'm going to apply in my charts to make them easier to understand.

@ExcelOffTheGrid

Күн бұрын

Glad it was helpful!

Certainly the presentation of the graph is much more attractive that way. Thanks for sharing Mark. Excellent!!

@ExcelOffTheGrid

Жыл бұрын

My pleasure! Thanks Ivan.

This is an excellent tutorial. Many thanks for sharing!👏

Very creative idea👍👍. Thanks Mark

@ExcelOffTheGrid

Жыл бұрын

Thanks Kebin 😁

Awesome as alway Mark. This is fantastic!

@ExcelOffTheGrid

Жыл бұрын

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 &""

@ExcelOffTheGrid

Жыл бұрын

Thanks for the “” tip. The syntax of all those functions feels wrong, so it doesn’t surprise me that there is another method. 👍

@drsteele4749

Жыл бұрын

@@ExcelOffTheGrid It's a mystery that those functions require the quotes and the ampersand as opposed to what should be simple operands. Painful.

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.

@ExcelOffTheGrid

Жыл бұрын

Provided you have a method to do that dynamically so that you don’t have to keep updating it manually. Then, Yes, I agree.

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!

@ExcelOffTheGrid

Жыл бұрын

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.

@JenMayB

Жыл бұрын

@@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.

@ExcelOffTheGrid

Жыл бұрын

@@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.

Nice! but now I’ve got to go back and change all my charts😀

@ExcelOffTheGrid

Жыл бұрын

🤣 - yes, you do!!!!!

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

Жыл бұрын

Yes, I can see that working too. That’s a good method. 👍

@annemettevejlegardkristens9263

Жыл бұрын

@@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. 💪👍

@ExcelOffTheGrid

Жыл бұрын

@@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.

Great tip. If one created a chart starting in PowerPoint, does the chart function in PowerPoint support the formulas you outlined?

@ExcelOffTheGrid

Жыл бұрын

I should, but I've never tried it. Give it a go. Please let us know either way.